Verify dynamically specified table exists

[bsandell]

I need to write a stored procedure to verify that a table exists and
also that the user executing the stored procedure has access to the
specified table.

Any user can call this publicly available procedure and pass a database
name, an owner name and a table name as parameters. The procedure
returns success if the table exists and the user has access to it, or
fails if he doesn't. Here's a simplified version of what I have, but
I'm wondering if there's a better way. Thanks.

create procedure dumb as
begin
declare @myError int,
@mytable varchar(128),
@myquery varchar(128)

select @mytable = '[Northwind].[dbo].[sysobjects2]'
select @myquery = 'DECLARE @x int SELECT @x = count(1) from ' +
@mytable + ' where 1 = 2'
exec (@myquery)
select @myError = @@ERROR
if @myError != 0
BEGIN
RAISERROR ('ERROR: The specified table %s cannot be accessed.', 10, 1,
@mytable)
RETURN 1
end

end
go

[Simon Hayes]

You can use the PERMISSIONS() function (see Books Online) to see if a
user has permissions on a certain object.

Although you don't say what your goal is, I would be wary of your
approach - if you don't know which table a user needs to access
runtime, then you will probably have to use dynamic SQL heavily, which
is usually a bad idea:

http://www.sommarskog.se/dynamic_sql.html

Depending on what you're trying to do, there may be better options
available, such as using stored procs, a reporting tool, etc. If you
can give more details of what you need to accomplish, someone may have
a suggestion.

Simon

[bsandell]

Hi Simon,

Thanks for the info and the link to the dynamic SQL article. The
article was excellent. I will attempt to explain what the goal of the
procedure is. We have an application that stores metadata about
certain tables that our application has processed. Users would like to
be able to duplicate this metadata for tables that have the same
structure as one for which we already store metadata. For example, if
there is already metadata stored for a table called BILLING_CURRENT,
and there is a monthly process that renames the BILLING_CURRENT table
to BILLING_MMYY (month and year), and creates a new empty table called
BILLING_CURRENT, I would like to be able to replicate all of the
relevant metadata for the table BILLING_CURRENT table to the
BILLING_MMYY table. This is a pretty generic example, the table could
also get replicated in another database for reporting, etc. The stored
procedure which I have written to do this takes 6 parameters, the
database, owner and tablename of the original table ( which form a
unique key to the metadata table), and the database, owner and
tablename of the new table. Before I actually replicate the metadata,
I would like to be sure that the requesting user actually has
sufficient access to the table they are requesting to have the metadata
copied for. If they aren't able to select from the table, then they
shouldn't be able to replicate metadata for that table.

Your suggestion to use the persissions() function is a great idea, but
won't it only apply to the current database? It seems like I'll still
need some dynamic sql to get what I'm after. FYI this stored procedure
is not something that will be run frequently. Once a day would
probably be pretty heavy usage.

I hope this is somewhat clear, and thanks again for the help.

[Erland Sommarskog]

(bs******@gmail.com) writes:

We have an application that stores metadata about
certain tables that our application has processed. Users would like to
be able to duplicate this metadata for tables that have the same
structure as one for which we already store metadata. For example, if
there is already metadata stored for a table called BILLING_CURRENT,
and there is a monthly process that renames the BILLING_CURRENT table
to BILLING_MMYY (month and year), and creates a new empty table called
BILLING_CURRENT, I would like to be able to replicate all of the
relevant metadata for the table BILLING_CURRENT table to the
BILLING_MMYY table. This is a pretty generic example, the table could
also get replicated in another database for reporting, etc. The stored
procedure which I have written to do this takes 6 parameters, the
database, owner and tablename of the original table ( which form a
unique key to the metadata table), and the database, owner and
tablename of the new table. Before I actually replicate the metadata,
I would like to be sure that the requesting user actually has
sufficient access to the table they are requesting to have the metadata
copied for. If they aren't able to select from the table, then they
shouldn't be able to replicate metadata for that table.
The idea is that the schema in a relational database is supposed to be
static. It could be changed with new versions of the application being
installed, but adding new tables during run-time goes against the
spirit.

Not knowing why all this copying take place, it's a little difficult
to suggest alternatives. But in the one example you give with a billing
table, the normal thing to do would simply be to add MMYY as a column
in the table, and have one table for all billings.
Your suggestion to use the persissions() function is a great idea, but
won't it only apply to the current database? It seems like I'll still
need some dynamic sql to get what I'm after.

Yes, it seems that you would have some dynamic SQL which performs
a USE on the source database, and then assigns the result of permissions()
into an output parameter. You would use sp_executesql for this.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> We have an application that stores metadata about certain tables
that our application has processed. <<

Mixing data and meta-data in a schema is always a fundamental design
mistake.

there is already metadata stored for a table called BILLING_CURRENT,

and there is a monthly process that renames the BILLING_CURRENT table
to BILLING_MMYY (month and year), .. <<

You have re-discovered a version of the old IBM magnetic tape label
convention (yyddd) and put it into an RDMS over 50 years later. "Those
who cannot remember the past are condemned to repeat it." --George
Santayana.

You have missed the whole point of relational models. This is a
version of attribute splitting; you have taken a temporal attribute and
made it's values into tables. You need to start over after you have
gotten some data modeling training.

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

You have missed the whole point of relational models. This is a
version of attribute splitting; you have taken a temporal attribute and
made it's values into tables. You need to start over after you have
gotten some data modeling training.
That's the thoery. Real life is apparently somewhat different. Or else
SQL Server would not have partitioned views, and in SQL 2005 also add
partitioned tables, so actually permit you to have billing_0501,
billing_0502 (or whatever), although you can still view it as one big
table as well.
You have re-discovered a version of the old IBM magnetic tape label
convention (yyddd) and put it into an RDMS over 50 years later. "Those
who cannot remember the past are condemned to repeat it." --George
Santayana.

The underlying problem is really the same, it is just that relational
databases and most of all the hardware development that have pushed
the limits where you need to resort to this sort of thing.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> .. partitioned views, and in SQL 2005 also add partitioned tables,
so actually permit you to have billing_0501, billing_0502 (or
whatever), although you can still view it as one big table as well. <<

Red Brick went even further with kind of STORAGE design, since it was
one of the first products built for OLAP. I don't care how the data is
PHYSICALLY stored as long as I see it has a normalized schema with
correct data. I do not want to have to manage it myself -- I am not as
smart as a good storage optimizer (see Teradata and its hashing
algorithm).

The underlying problem is really the same, it is just that

relational databases and most of all the hardware development that have
pushed the limits where you need to resort to this sort of thing. <<

We have the hardware for VLDB apps and it is cheap. The real problem
is the choice of software and programming. People start off with a
small app in ACCESS, then find that it is a pain to port to SQL Server
because the languages are so different. Then they find that SQL
Server also hits a limit.

Rather than make the step up to a new platform, they kludge for awhile
with tricks like this, and try to get speed from highly proprietary
code,. This makes the code even harder than before to move to a larger
RDBMS. So they have to start over when it gets critical.

[Greg D. Moore \(Strider\)]

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

We have the hardware for VLDB apps and it is cheap. The real problem
is the choice of software and programming. People start off with a
small app in ACCESS, then find that it is a pain to port to SQL Server
because the languages are so different. Then they find that SQL
Server also hits a limit.
One thing that I'm hoping will help is that more people will use SQL Express
instead of Access. Makes a lot of these porting/upgrading questions kind of
moot.


Rather than make the step up to a new platform, they kludge for awhile
with tricks like this, and try to get speed from highly proprietary
code,. This makes the code even harder than before to move to a larger
RDBMS. So they have to start over when it gets critical.

[--CELKO--]

>> I'm hoping will help is that more people will use SQL Express
instead of Access. <<

I hope ACCESS dies. I was COMDEX when they presented it to the world
and the Trade Press for the first time. It sorted dates alphabetically
by month and gave a Blue Screen of Death when Gates demoed it. On the
other hand, Foxpro and "Dr. Dave" had a flawless demo on the same
stageand equipment. This piece of crap has never been close to
Standard SQL and the engine is so bad that it probably can not ever be
made to perform correctly. I was also on retainer to the ACCESS group
for a year.

[bsandell]

Thanks everyone for all your input on this topic. Just to clarify a
few points -

1. The metadata is in a separate schema from the user's data.
2. I have nothing to do with the existing application design or
implementation. I just have to provide a tool that's flexible enough
to work in this existing environment, among others, regardless of what
I think of any exisitng implementation decisions.

Again, thanks for all the input. I think that the sp_executesql
suggestion looks like what I'm after.

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

We have the hardware for VLDB apps and it is cheap. The real problem
is the choice of software and programming. People start off with a
small app in ACCESS, then find that it is a pain to port to SQL Server
because the languages are so different. Then they find that SQL
Server also hits a limit.
If you first hit the limits in Access, and believe you have hit limits
in SQL Server, then I can only congratulate to a very expanding business.
I don't know where Access bites the dust, but would not consider it for
a 1GB database. Neither do I know when SQL Server caves in, but since
people run terabyte databases in SQL Server, so the margin to Access to
SQL Server.

Of course, with poor indexing you can make any engine go awfully slow.

The major reason for partitioning is probably so much query speed,
bu manageability. If the billings for last months never changes,
you don't really want to back them up each time. And if you routinely
purge them after, say, 12 months, then you want to do that quickly.
Rather than make the step up to a new platform, they kludge for awhile
with tricks like this, and try to get speed from highly proprietary
code,.

Of course. Most database products on the market are propritary. The
exception are the open-source products, but my impression is that
MySQL are not really ripe for the terabyte market yet.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp