验证动态指定的表存在 [英] Verify dynamically specified table exists

问题描述

我需要编写一个存储过程来验证表是否存在，并且

还表示执行存储过程的用户可以访问

指定的表。 />

任何用户都可以调用此公开程序并传递数据库

名称，所有者名称和表名作为参数。如果表存在并且用户可以访问它，则程序

返回成功，或者如果他没有，则b $ b失败。这是我所拥有的简化版本，但

我想知道是否有更好的方法。谢谢。


创建程序愚蠢如下

开始

声明@myError int，

@mytable varchar（128），

@myquery varchar（128）

选择@mytable =''[Northwind]。[dbo]。[sysobjects2]''

选择@myquery =''DECLARE @x int SELECT @x = count（1）from''+

@mytable +''其中1 = 2''

exec（@myquery）

选择@myError = @@ ERROR

if @myError！= 0

BEGIN

RAISERROR（''错误：指定的表％s无法访问。''，10,1，

@mytable）

RETURN 1

结束


结束

go

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

推荐答案

您可以使用PERMISSIONS（）函数（参见联机丛书）查看

用户是否拥有某个对象的权限。


虽然你不说你的目标是什么，但我会对你的b
方法保持警惕 - 如果你不知道哪个表用户需要访问
运行时，那么你可能不得不大量使用动态SQL，这通常是一个坏主意：

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


根据您要做的事情，可能有更好的选择

可用，例如使用存储过程，报告工具等。如果您

可以提供你需要完成的更多细节，有人可能会有建议。


Simon

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

嗨西蒙，


感谢您提供有关动态SQL文章的信息和链接。

的文章很棒。我将尝试解释

程序的目标是什么。我们有一个应用程序存储关于

我们的应用程序已处理的某些表的元数据。用户希望能够为具有与我们已存储元数据的结构相同的结构的表复制此元数据。例如，如果

已经存储了一个名为BILLING_CURRENT的表的元数据，

并且有一个月度进程重命名BILLING_CURRENT表

到BILLING_MMYY（月和年），并创建一个名为

BILLING_CURRENT的新空表，我希望能够复制表BILLING_CURRENT的所有

相关元数据表格为

BILLING_MMYY表。这是一个非常通用的例子，表格可以

也可以在另一个数据库中复制以进行报告等。我为此编写的存储的
过程需要6个参数，

数据库，原始表的所有者和表名（形成元数据表的

唯一键），以及数据库，所有者和

新表的表名。在我实际复制元数据之前，

我想确保请求用户实际上有足够的权限访问他们请求拥有元数据的表
复制。如果他们无法从表中选择，那么他们

应该无法复制该表的元数据。


您的建议使用persissions（）函数是一个好主意，但

不会只适用于当前数据库吗？似乎我仍然会需要一些动态的sql才能得到我想要的东西。仅供参考这个存储过程

不是经常运行的东西。每天一次将是b $ b可能是非常繁重的使用。


我希望这有点清楚，再次感谢你的帮助。

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.

（bs ****** @ gmail.com）写道：

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

我们有一个应用程序存储关于某些表的元数据我们的申请已经处理完毕。用户希望能够为具有与我们已存储元数据的结构具有相同结构的表复制此元数据。例如，如果已经为名为BILLING_CURRENT的表存储了元数据，那么每月进程会将BILLING_CURRENT表重命名为BILLING_MMYY（月和年），并创建一个新的空表叫做BILLING_CURRENT，我希望能够将表BILLING_CURRENT表的所有相关元数据复制到
BILLING_MMYY表中。这是一个非常通用的例子，表格也可以在另一个数据库中复制以进行报告等。我编写的存储过程需要6个参数，
数据库，原始表的所有者和表名（形成元数据表的唯一键），以及新表的数据库，所有者和
表名。在我实际复制元数据之前，我想确保请求用户实际上有足够的权限访问他们要求复制元数据的表。如果他们无法从表格中进行选择，那么他们就无法复制该表格的元数据。


这个想法是关系数据库中的模式应该是静态的。它可以通过安装新版本的应用程序进行更改，但在运行时添加新表会违反

精神。


不知道为什么所有这些复制都发生了，建议替代方案有点困难

。但是在一个例子中你给出了一个账单

表，正常的做法就是在表中添加MMYY作为列

，并且有一个表对于所有账单。

你建议使用persissions（）函数是一个好主意，但
不是它只适用于当前数据库吗？好像我还是需要一些动态的sql才能得到我想要的东西。

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.

是的，似乎你会有一些动态SQL在源数据库上执行
a USE，然后将权限结果（）

分配给输出参数。你可以使用sp_executesql。


-

Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se


SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/books.asp

这篇关于验证动态指定的表存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！