Shulou(Shulou.com)06/01 Report--

-- query the permissions of the database:

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

-- related permission table

Select * from sysusers

Select * from syspermissions

Some specific queries

-- see who can access the 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

-- see who can access the database

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

-- role permission query

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 role

SELECT

P.name as UserName, p.type_desc as UserType, pp.name as DBRoleName, pp.type_desc as DBRoleType, pp.is_fixed_role as IfFixedRole

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')

-- View the user's permissions

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%'

-- other instructions

Query through stored procedures

EXEC sp_helprotect NULL, NULL, null,'golden_ro'

Parameter 1:Owner sysname Name of the object owner

Parameter 2:Object sysname Name of the object.

Parameter 3:Grantee sysname Name of the principal to which permissions were granted

Parameter 4:Grantor sysname Name of the principal that granted permissions to the specified grantee.

Temporarily list the four commonly used parameters! The usage is as above!

Such as querying the authorization of the table

EXEC sp_helpuser

Parameter 1:UserName sysname Users in the current database.

Parameter 2:RoleName sysname Roles to which UserName belongs.

Parameter 3:LoginName sysname Login of UserName.

Parameter 4:DefDBName sysname Default database of UserName.

Recently, when I authorized with the user, I found that I had to grant the execution permission to all stored procedures for a long time, and finally figured it out.

Grant execute to username

It is relatively easy to grant a single table, view, or stored procedure!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.