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)
Thank this author by sharing:
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
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 failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.