对 SQL 中的对象（表）具有权限的用户和角色列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5981162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
list of users and roles that have permissions to an object (table) in SQL
提问by Rabbi
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?
查找对表具有权限的角色列表的 TSQL 语句是什么？
The pseudo-code looks like this:
伪代码如下所示：
SELECT role_name
FROM permissions
where object_name = 'the_table_i_need_to_know_about'
回答by Herve Roggero
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
回答by sudmong
Try this,
尝试这个，
sp_helprotect "table name" go
sp_helprotect "表名" 去
回答by Steve Kirchner
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:
为了在数据库中获得分配给特定用户的各个角色，您需要执行 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
