对 SQL 中的对象（表）拥有权限的用户和角色列表

发布于 2024-11-06 14:41:54 字数 213 浏览 4 评论 0原文

你可能认为我可以用谷歌搜索这样一个简单的问题。但无论我做什么，我都会碰壁。

用于查找对表具有权限的角色列表的 TSQL 语句是什么？

伪代码如下所示：

SELECT role_name 
FROM permissions 
where object_name = 'the_table_i_need_to_know_about'

原文

You'd think I'd be able to Google such a simple question. But no matter what I try, I hit a brick wall.

What is the TSQL statement to find a list of roles that have permissions to a table?

The pseudo-code looks like this:

SELECT role_name 
FROM permissions 
where object_name = 'the_table_i_need_to_know_about'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

往日情怀 2024-11-13 14:41:54

这有点棘手。首先，请记住内置角色具有预定义的访问权限；这些不会出现在下面的查询中。建议的查询列出了自定义数据库角色以及特别授予或拒绝的访问权限。这是您要找的吗？

select permission_name, state_desc, type_desc, U.name, OBJECT_NAME(major_id) 
from sys.database_permissions P 
JOIN sys.tables T ON P.major_id = T.object_id 
JOIN sysusers U ON U.uid = P.grantee_principal_id

It's a bit tricky. First, remember that the built-in roles have pre-defined access; these won't show up in the query below. The proposed query lists custom database roles and which access they were specifically granted or denied. Is this what you were looking for?

select permission_name, state_desc, type_desc, U.name, OBJECT_NAME(major_id) 
from sys.database_permissions P 
JOIN sys.tables T ON P.major_id = T.object_id 
JOIN sysusers U ON U.uid = P.grantee_principal_id

回复收藏 0 原文

幸福不弃 2024-11-13 14:41:54

试试这个，

sp_helprotect“表名”
去

回复收藏 0 原文

等待我真够勒 2024-11-13 14:41:54

为了获取数据库中分配给特定用户的各个角色，您需要执行 sp_helpusers 过程。以下过程将为服务器上的每个数据库执行 sp_helpuser，将每个数据库的结果累积在表变量中，然后提供每个数据库、用户及其有权访问的角色的结果集：

Create Procedure dba_HelpUserRoles
AS
Declare @SQL Varchar(2000)
Declare @DBname Sysname

Declare @HelpUserResults Table
(
UserName Sysname,
RoleName Sysname,
LoginName Sysname NULL,
DefDBName Sysname NULL,
DefSchemaName Sysname NULL,
UserID Smallint,
SID Smallint
)

Declare @DbUserResults Table
(
DBname Sysname,
UserName Sysname,
RoleName Sysname,
LoginName Sysname NULL,
DefDBName Sysname NULL,
DefSchemaName Sysname NULL,
UserID Smallint,
SID Smallint
)

Declare @DBcursor
Cursor For
Select Name
From sys.sysdatabases
Order by Name;

Fetch Next
From DBcursor
Into @DBname;

While @@Fetch_Status = 0
Begin

Set @SQL = 'Use [' + @DBname + ']; Exec sp_helpuser;';

Print @SQL

Insert @HelpUserResults
Exec(@SQL);

Insert @DBUserReults
Select @DBname, *
From @HelpUserResults
Where LoginName IS NOT NULL;

Delete
From HelpUserResults;

Fetch Next
From DBcursor
Into @DBname;

End

Close DBcursor;
Deallocate DBcursor;

Select *
From @DBUser_Results;

-------------------------------- Procedure End

In order to get the individual roles assigned to a particular user with in a database, you need to execute the sp_helpusers procedure. The following procedure will execute sp_helpuser for each database on the server, accumulate the results for each database in a table variable, and then provide a result set of each database, user, and the role they have permission to:

Create Procedure dba_HelpUserRoles
AS
Declare @SQL Varchar(2000)
Declare @DBname Sysname

Declare @HelpUserResults Table
(
UserName Sysname,
RoleName Sysname,
LoginName Sysname NULL,
DefDBName Sysname NULL,
DefSchemaName Sysname NULL,
UserID Smallint,
SID Smallint
)

Declare @DbUserResults Table
(
DBname Sysname,
UserName Sysname,
RoleName Sysname,
LoginName Sysname NULL,
DefDBName Sysname NULL,
DefSchemaName Sysname NULL,
UserID Smallint,
SID Smallint
)

Declare @DBcursor
Cursor For
Select Name
From sys.sysdatabases
Order by Name;

Fetch Next
From DBcursor
Into @DBname;

While @@Fetch_Status = 0
Begin

Set @SQL = 'Use [' + @DBname + ']; Exec sp_helpuser;';

Print @SQL

Insert @HelpUserResults
Exec(@SQL);

Insert @DBUserReults
Select @DBname, *
From @HelpUserResults
Where LoginName IS NOT NULL;

Delete
From HelpUserResults;

Fetch Next
From DBcursor
Into @DBname;

End

Close DBcursor;
Deallocate DBcursor;

Select *
From @DBUser_Results;

-------------------------------- Procedure End

回复收藏 0 原文

~没有更多了~