SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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


I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.


Leave a comment on the original post [www.practicalsqldba.com, opens in a new window]

Loading comments...