1

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,.

2

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.

  • Yeeeah Thanks Aaron. ...I already have this SP_FOREACHDB for a loooong time. It worked . haha thanks again. – Racer SQL Oct 1 '18 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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