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:

• Finding the Orphaned Users
• Fixing the Orphaned Users
• List Login Server and Database Level Role Membership

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

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