SQL Server 2008 R2 – How to Find All Logins and Database Users

sql-server-2008-r2

I'm trying to join, every login and permissions tables. Looking over the internet but I just can't find something to list like:

LOGIN  |  USER  | DATABASE_THE_USER_HAS_ACCESS

This is because I would like to find all users with not a single permission on any database ( Login with no users ).

sys.server_principals AS log
LEFT OUTER JOIN sys.syslanguages AS l ON l.name = log.default_language_name
LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = log.principal_id and sp.type = N'COSQ'
LEFT OUTER JOIN sys.credentials AS c ON c.credential_id = log.credential_id
LEFT OUTER JOIN master.sys.certificates AS cert ON cert.sid = log.sid
LEFT OUTER JOIN master.sys.asymmetric_keys AS ak ON ak.sid = log.sid
LEFT OUTER JOIN sys.sql_logins AS sqllog ON sqllog.principal_id = log.principal_id
WHERE

I'm looking at these tables but I can't find a pattern to join them,.

Best Answer

First, go grab sp_foreachdb from the First Responder Kit (background here):

Then you can collect all the database users here:

CREATE TABLE #DBUsers
(
  DBName sysname,
  DBUserName sysname,
  DBPrincipalID int,
  SID varbinary(85),
  LoginName nvarchar(128),
  LoginPrincipalID int
);

EXEC master.dbo.sp_foreachdb N'INSERT #DBUsers 
SELECT ''?'',d.name, d.principal_id, d.sid, s.name, s.principal_id
FROM ?.sys.database_principals AS d
LEFT OUTER JOIN sys.server_principals AS s 
ON d.sid = s.sid
WHERE d.principal_id > 4 
  AND d.type <> ''R''
  AND d.name NOT LIKE N''##%##'';';

Now for example to find any server-level login that doesn't have an associated user in any database (note this will currently include those in elevated roles, like sysadmin):

SELECT * FROM sys.server_principals AS p
WHERE type NOT IN ('S','R','C')
  AND name NOT LIKE N'##%##' 
  AND name NOT LIKE N'NT %\%'
  AND NOT EXISTS
  (
    SELECT 1 FROM #DBUsers
    WHERE SID = p.sid
  );

Note that you might have to dig a bit deeper, because a user might have been added to a database to explicitly deny access. But that should be a good start for the normal case.

Related Question