list of users and roles that have permissions to an object (table) in SQL

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 TS开发者_如何学编程QL 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'

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

Try this,

sp_helprotect "table name" go

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