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)

List all Usernames, Roles for all the databases.

By Shiva Challa,

You can directly download the script from here: http://www.sqlservercentral.com/Forums/Attachment14561.aspx

Provided the link so that folks who are having Misc' characters issue can get this file and use it.


Description: This script generates a list of Usernames and the database Roles that the user belongs to in all the databases; with a couple of other useful information detailed below.

Other benefit: you can use this script to get the roles of ONE specific user in all the databases.

Directions of Use:

For All Users list: You can directly run this script in SQL Server Management studio

For a specific user:

1. Find this code /*and u.name like ''tester''*/

2. Uncomment the code

3. Replace the Name ''tester'' with the username you want to search on.

Resultset Explanation:

ServerName: Name of the server this script was executed on.
DBName: Database name that the user exists in.
Name: user name.
GroupName: Group/Database Role that the user is a part of.
LoginName: Actual login name, if this is null, Name is used to connect.
default_database_name
default_schema_name
principal_id
sid

Tested: This script is tested on SQL Server 2005 standard edition with sp2 and the forum users were generous enough to test the script on SQL server 2000 and SQL Server 2008 and deemed the script working.

Change history:

08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/2008 Cathy Greenselder - Convert to SQL2000
- (default_database_name not in SQL2K)
- (default_schema_name not in SQL2K)
- (principal_id not in SQL2K)
- (uid is in SQL2K
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
- Added code to use SysObjects in 2000 instead of sys.objects
10/09/2008 Cathy Greenselder - for 2000, switched the SysUsers join to use "m.groupuid" instead of "m.memberuid"
02/09/2009 Shiva Challa - Per Matt Karp's(forum member) suggestion, added Square brackets around dbname,
- changed dbname datatype to sysname instead of varchar(50)
- Added ServerName to the select

10/29/2013 Shiva Challa - Added support for sql server 2008 and 2012. 

                                    - Changed the logic to check version number, so that this will work for all future versions (that is until there is a paradigm shift, again).

Total article views: 20682 | Views in the last 30 days: 127
 
Related Articles
FORUM

Database Default file Locations

Database Default Locations in Database Setting Under Server Properties

FORUM

Default Database

Finding Default Database

FORUM

Default Database Drive Almost Full

Change Default Database Location

FORUM
BLOG

SQL Server – Login Failed, cannot open user default database

Each login in SQL Server has a default database associated with it. When you login to SQL Server con...

Tags
administration    
auditing    
role    
security    
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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