Security9: Querying the permissions granted by login

When granting permission to a login, it is found that the login already exists and its corresponding user exists in the specified db to see the permissions that the login has been granted in the specified db.

1. View login's server Principalid

Select *  from  where sp.name='domain_name\llogin_name'

2, see if login is a server role member

Select *  from  where srm.member_principal_id=287

3. View the database Principalid of login for the corresponding user in the specified db

-- view the corresponding user in db and get the user's principalid Select *  from sys.database_principals DP Inner Join sys.server_principals SP       on Dp.sid=sp.sidwhere sp.principal_id=287

4, see if User is the member of database role

-- See if the user is a member of database role Select *  from  where drm.member_principal_id=5

5, view user's permission

-- View the permissions that the user has been given Select *  from  where dp.grantee_principal_id=5

Appendix

Sys.database_permissions

Returns a row for every permission or column-exception permission in the database. For columns, there are a row for every permission that's different from the corresponding Object-level permission. If The column permission is the same as the corresponding object permission, there'll be no row for it and the actual PE Rmission used would be is that of the object.

major_id: ID of thing on which permission exists, interpreted according to class. For more, this is simply the kind of the ID, which applies to what the class represents. Interpretation for nonstandard is as follows:

0 = always 0

1 = Object-id

Negative IDs is assigned to system objects.

minor_id: Secondary-id of thing on which permission exists, interpreted according to class. For more, this is zero. Otherwise, it is the following:

1 = Column-id If a Column. Otherwise, it's 0 if an object.

Reference doc:

SYS.DATABASE_PERMISSIONS (Transact-SQL)

Security9: Querying the permissions granted by login