Our very Own CoreMember Marc Mertens put out a great script on how to calculate the effective rights of a certain login in SQL SERVER. Attached to this post you will find the script + the readme instructions on how to use it.

here's an excerpt on the instructions:

Installing

1.       Use the Sql Server Surface Configuration to enable CLR for your sql server instance

2.       Execute the EffectiveRights\DefinitionOfHelpStoredProcedures.sql script as a login who is member of sysadmin, this will define the following stored procedures:

a.       sp_ServerRightsFor, displays the effective rights that a login has on the server and all securables in the server scope

b.      sp_DatabaseRightsForLogin, display the effective rights that a login  has on a database and all securables in this database

3.       Use Visual Studio 2005 to open the EffectiveRightsFunctions\EffectiveRightsFunctions.sln solution file and build the project.  Note the absolute path of the produced assembly (EffectiveRightsFunctions.dll)

4.       Load the EffectiveRights\DeployFunctions.sql and modify the CREATE ASSEMBLY statement so that it refers to EffectiveRightsFunctions.dll produced in the previous step. Execute then the script as a login who is member of sysadmin. This script will then define the following tablevalued functions:

a.       fn_effective_server_rights, displays the effective rights that a login has on the server and all securables in the server scope

b.      fn_effective_database_rights, display the effective rights that a login  has on a database and all securables in this database

c.       fn_effective_database_rights_all, display the effective rights that a login has on all the databases and all their securables

d.      fn_effective_server_rights_for_all_logins, displays the effective rights that all logins have on the server and all securables in the server scope

e.      fn_effective_database_rights_for_all_logins, displays the effective rights that all logins have on all the databases and their securables

How to use

1.       You must be in sysadmin to be able to use the stored procedures and functions

2.       If you want to see what the effective rights are for a login on the server scope execute either of the following:

a.       exec  sp_ServerRightsFor @Principal=’the name of the login’

b.      select * from fn_effective_server_rights(‘the name of the login’)

3.       If you want to see what the effective rights are for a login on a database and its securables, execute either of the following:

a.       exec  sp_DatabaseRightsForLogin @Principal=’the name of the login’,@Database=’The name of the database’

b.       select * from fn_effective_database_rights (‘the name of the login’,’the name of the database’)

4.       If you want to see the effective rights that a login has on all the databases and their content, execute:

a.       Select * from fn_effective_database_rights_all(‘the name of the login’)

5.       If you want to see the effective rights of all logins on the server level execute:

a.       Select * from fn_effective_server_rights_for_all_logins(), this can take a long time to complete

6.       If you want to see the effective rights of all logins on all databases and their securables execute:

a.       Select * from fn_effective_database_rights_for_all_logins(), this can take a long time to complete

If you have any further questions you can e-mail me on the address below

Marc Mertens

marc.lisp [at] gmail.com