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


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


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


  3. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune

  4. 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



  7. شركة نقل اثاث بالدمام التفاؤل شركة نقل اثاث بالخبر كما انها افضل شركة نقل اثاث بالجبيل نقل عفش واثاث بالجبيل والخبر والقطيف والدمام
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالجبيل

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

    Webseiten Gestaltung Lüdenscheid