Many DBAs need to perform SQL Server security audit and save the permissions for each account at server level and database level. This will be done time to time and verified so that no elevated permissions are provided to any user than required. But to collect data of database roles and server roles of all the logins and users is a challenge. So I have written the below script by spending some time rather than manually doing it every quarter for dozens of servers. The below script will run on SQL Server versions above SQL 2005 and the script commented out will help for running on SQL 2000 servers.

For SQL Server 2005 and above

For SQL Server 2000

The above script returns redundant data at server level but I can compromise with that given the filter options in the excel sheets.

Happy Troubleshooting,



Manohar Punna

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook