Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Capture User Auths For Database

By Donald Patterson,

This script is for capturing user authorizations on a SQL instance.  This script is by database versus user.  Thank you to everyone for their feedback on the first script I submitted.

This procedure takes in a valid database name and will return all   authorities for the logins with the database as their default database.  The script captures any server roles, database roles, and any database permissions and authorites granted on the default database along with any other database the login has access to on the SQL instance.
If the database name is not provided, it will generate the access and authorities for all logins which could take some time.
This procedure uses the stroed procedure sp_hexidecimal and Aaron Bertrand's sp_foreachdb stored procedure

This has been tested on SQL Server 2005 and SQL Server 2008 R2.  Please note that some facets on each of these SQL versions have not been tested due to lack of availability.
EX - sp_help_revlogin_db 'DWProd' 
EX - sp_help_revlogin_db

Total article views: 1518 | Views in the last 30 days: 6
Related Articles

Create Login Script

Error with Create Login Script



This procedure takes a login name and will generate the access and authorities for the login across ...


Login Failure for NT Authority\System user

Login Failure for NT Authority\System user


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.


moving databases,logins

moving database,logins


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones