Methods for querying the user's object permissions and roles in SQL Server

How to query the user's object permissions and roles in--sql server

--Querying the user's object permissions

exec sp_helprotect NULL, ' sa '

--Query user-owned role

EXEC sp_helpuser ' public '

--Query which users have the specified system role

exec sp_helpsrvrolemember ' sysadmin '

--Can query nested role

With Tree_roles as

(

SELECT role_principal_id, member_principal_id

From Sys.database_role_members

WHERE member_principal_id = user_id (' UserName ')

UNION All

SELECT c.role_principal_id,c.member_principal_id

From Sys.database_role_members as C

INNER JOIN Tree_roles

On tree_roles.member_principal_id = c.role_principal_id

)

SELECT distinct user_name (role_principal_id) RoleName

From Tree_roles

--Other Permissions related basic table

SELECT * FROM sysusers

SELECT * FROM Syspermissions

-who have access to my SQL Server instance?

SELECT

Name as UserName, Type_desc as usertype, is_disabled as isdisabled

From Sys.server_principals

where Type_desc in (' Windows_login ', ' Sql_login ')

Order by usertype, name, isdisabled

-who have access to my Databases?

SELECT

Dp.name as UserName, Dp.type_desc as usertype, sp.name as LoginName, Sp.type_desc as Logintype

From Sys.database_principals DP

JOIN sys.server_principals sp on dp.principal_id = sp.principal_id

ORDER BY Usertype

SELECT * FROM Sys.database_principals

--Server Roles

Select

P.name as UserName, P.type_desc as usertype, pp.name as Serverrolename, Pp.type_desc as Serverroletype

From sys.server_role_members roles

Join Sys.server_principals p on roles.member_principal_id = p.principal_id

Join Sys.server_principals pp on roles.role_principal_id = pp.principal_id

where Pp.name in (' sysadmin ')

Order by Serverrolename, UserName

--Database Roles

SELECT

P.name as UserName, P.type_desc as usertype, pp.name as Dbrolename, Pp.type_desc as Dbroletype, pp.is_fixed_role as IfFixe Drole

From sys.database_role_members roles

JOIN sys.database_principals p on roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp on roles.role_principal_id = pp.principal_id

where Pp.name in (' db_owner ', ' db_datawriter ')

-What can these users do?

SELECT

Grantor.name as Grantorname, Dp.state_desc as Statedesc, Dp.class_desc as Classdesc, dp.permission_name as PermissionName ,

object_name (major_id) as ObjectName, Granteename = Grantee.name

From Sys.database_permissions DP

JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id

JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

where permission_name like '%update% '

http://blog.itpub.net/66009/viewspace-1060533/

Methods for querying the user's object permissions and roles in SQL Server