query to see all databases to which a user has access

Question

I need a simple script that will list all of the databases to which a particular user has access. We have over 100 databases on our server, and around 300 users. Yes, I know I can get this info by right-clicking the login in SQL Management Studio. But I'm not looking for a GUI solution. I need a SQL statement (or script) that will produce a list of databases.

Thank you.

Answer 1

Hi,

The simple one is to use:

 

sp_helplogins 'loginname'

 

---

Regards, Vishal Srivastava

Answer 2

 

Run this scrip this will provide you the user information for all the Databases.

 

DECLARE @DBuser_sql VARCHAR(4000) 
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200)) 
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a 
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id 
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table 
EXEC sp_MSforeachdb @command1=@dbuser_sql 
SELECT * FROM @DBuser_table ORDER BY DBName 

 

---

Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

Answer 3

One difficult problem. A Windows user might have access to a database through a login based on a Windows group. So you will need to look for all groups that a user is a member of. Or (the other way around) find all the groups that are users in the databases, and then figure out who is a member of each group. Some Windows users might have access through a Windows group, and never have logged into SQL Server and SQL Server has no idea that they even exits.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

I wrote a script to do exactally that, you can get it from my blog at Link.  It will list each user blonging to each database.  You can create the stored procedure in tempdb and run it to collect the info.

---

Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://sqllearnings.blogspot.com/

Answer 5

This is  perfect, exactly what I was looking for. I signed up just to upvote. 

Answer 6

Hi Mohit,

I am having this same issue!  Where is your script?  We need to find where a group of users is gaining access.  They have DBO level rights, but no login in SQL SERVER.  We know they are part of a Windows group, but there are no groups on the SQL SERVER that either of them are members of.  Can you please repost the script?  Thank you.

Answer 7

Hi Mohit,

I am having this same issue!  Where is your script?  We need to find where a group of users is gaining access.  They have DBO level rights, but no login in SQL SERVER.  We know they are part of a Windows group, but there are no groups on the SQL SERVER that either of them are members of.  Can you please repost the script?  Thank you.

Rather than piggybacking on an old thread, it is better to start a new thread explaining your problem from start to end. I happen to know of a solution for your problem, but I prefer to post that to a new thread.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Hello,

Is it possible for you to provide the thread where you continued this discussion?

I am very interested in your answer.

---

Marcia Henry

Answer 9

Is it possible for you to provide the thread where you continued this discussion?

I am very interested in your answer.

Click on Studyhawk2's name to get his Profile page, and the select the Activity tab. There you can see which threads Studyhawk2 has started. (Don't click on my name. I'm involved in way too many threads for it to be practical to find things that way.

If you don't find anything - follow the instructions in my post from December 2018.

---

Erland Sommarskog, SQL Server MVP, [e-mail]