Security9: Querying the permissions granted by login

Source: Internet
Author: User

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

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.