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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.