0

I am looking for tsql query to get all the associated users in all dbs for a login. Tried below SQL, but this is not giving right result set.

If I execute this query under the Database context where the user is present, it gives output as if user is present in all DBs.

Same query executed under the Database context where the user is absent, gives 0 rows as if user is not present in any of the DBs.

exec sp_msforeachdb 'select  ''?'' AS DBName,dp.name as username from sys.database_principals dp join sys.server_principals 
srp on dp.sid=srp.sid where srp.name=''domain\account'''

Thanks

4
  • can you please make a minimal reproducible example s0o that we can see what you get and what you expect – nbk Dec 22 '20 at 12:28
  • Add USE [?]; to the beginning of the script: exec sp_msforeachdb 'USE [?];select.... – Dan Guzman Dec 22 '20 at 13:18
  • @nbk That's a duplicate of what Learning_DBAdmin posted. – J.D. Dec 22 '20 at 16:50
  • @J.D. i noticed that and voted accordingly – nbk Dec 22 '20 at 17:00
0

You can use the following Dynamic SQL to build the query you need to hit all databases and get a list of Users:

DECLARE @LoginName AS NVARCHAR(100) = N'Your Login Name Here'
DECLARE @DynamicSQL AS NVARCHAR(MAX) = N'';

SELECT @DynamicSQL = @DynamicSQL +
    'SELECT ''' + [name] + ''' AS DatabaseName, [name] AS UserName FROM [' + [name] + '].sys.database_principals WHERE [type] IN (''S'', ''U'', ''E'', ''G'') AND [name] = ''' + @LoginName + ''' UNION ALL ' 
FROM sys.databases
WHERE [state] = 0; -- Online databases only

SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10); -- Cleans up unneeded trailing "UNION ALL"

-- PRINT @DynamicSQL -- Prints the dynamic SQL string (For Testing)
EXEC master.sys.sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL string

I based it off the query Aaron Bertrand is using in the answer that @Learning_DBAdmin linked, but I figured re-writing my own Dynamic SQL could make it a little more readable. (And as he mentions sp_msforeachdb is undocumented and unreliable, so better to query dynamically.)

This can be used to get SQL Users, Windows Users, Azure Active Directory External Users, and Windows Groups based on the Type column in sys.database_principals (you can adjust this as needed).

7
  • Note that the behavior of aggregate string concatenation (e.g. SELECT @DynamicSQL = @DynamicSQL +) is undefined. Use STRING_AGG or FOR XML for deterministic results. – Dan Guzman Dec 22 '20 at 13:48
  • @DanGuzman Thanks for the info, but I don't fully understand the point. Are there any issues with it being nondeterministic I might be unaware of? – J.D. Dec 22 '20 at 14:00
  • I should have said "undefined". The actual behavior depends on the execution plan and can yield unexpected results. – Dan Guzman Dec 22 '20 at 15:42
  • @DanGuzman Hmm interesting, I've never seen any example of that before. Have any references or an example that reflects unexpected results? (I've been doing string concatenation like this for my dynamic SQL queries for a few years and have yet to encounter that, but it would certainly be good for me to be aware of.) – J.D. Dec 22 '20 at 16:40
  • 1
    See this answer. Unfortunately, some of the KB article and Connect feedback links are now dead but the "undefined behavior" is a quote from the SQL Server product team. – Dan Guzman Dec 22 '20 at 16:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.