Pages

Tuesday 7 August 2012

SQL Server: List Explicit Permission on Database for Login and Database Role

As part of security audit of login's, we have discussed earlier about the orphan users , listing server level and database level role membership.To keep the the SQL server secure, it is very important to review the permission/authorization  level of users in database. A user can have access on database in two ways, either through explicit permission or through database role membership.

It is not very easy or there is no system stored procedure to list the explicit permission granted for a user or a database role. The List Login explicit permission on database.sql help us to list the explicit permission granted/denied for user either in object level or in database level. This script will return six columns.Database Name,user name mapped to this login,object(table/function/store procedure etc) name, object type, permission state (grant/deny) and the permission granted /denied. On providing the login name , this script will list permission details of that login in  all databases.

In the same way, List Database Role explicit permission on database.sql help us to list the explicit permission granted/denied for a database role.On providing the database role name , this script will list permission details of that role in  all databases.This will help us if you have same role name across multiple databases.

To summarize the point that we discussed on reviewing the security of database sever:


Hope these scripts will help to review the access level of your environment. 

If you liked this post, do like my page on FaceBook


8 comments:

  1. Your link to List Database Role explicit permission on database.sql is not working.

    ReplyDelete

  2. Thanks for posting this useful content, Good to know about new things here, Let me share this, . LINUX training in pune

    ReplyDelete
  3. Nice Blog.Thank you for Sharing. We are leading erp software software solution providers in chennai. For more details call +91 9677025199.
    erp software in chennai | erp providers in chennai | online events registration

    ReplyDelete
  4. Interesting post! This is really helpful for me. I like it! Thanks for sharing!

    Webseiten Gestaltung Lüdenscheid

    ReplyDelete


  5. you can find some special partner for you. Just call us and ask for a Call Girls in Vasant KunjIf you are all alone in the big city with nothing to do or no friends to accompany you can call us any time for more details. Check our other services...
    Call Girls in Vasant Kunj
    Call Girls in Vasant Kunj
    Call Girls in Vasant Kunj
    Call Girls in Vasant Kunj

    ReplyDelete