Do not SELECT if in Table B

Question

Do not SELECT if in Table B

Michael_O

Ten Centuries

Points: 1244
More actions
August 26, 2016 at 11:08 am

#329599

I have the following query for auditing contained sql logins in my sql server instance:
SET NOCOUNT ON;
CREATE TABLE #ContainedDBUser (
DBName sysname,
UserName sysname
);
DECLARE cursContainedDBs CURSOR FAST_FORWARD
FOR SELECT name FROM sys.databases WHERE containment > 0;
DECLARE @DBName sysname;
DECLARE @SQL NVARCHAR(MAX);
OPEN cursContainedDBs;
FETCH NEXT FROM cursContainedDBs INTO @DBName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQL = 'INSERT INTO #ContainedDBUser (DBName, UserName)
SELECT ''' + @DBName + ''', name
FROM [' + @DBName + '].sys.database_principals
WHERE type IN (''U'', ''S'', ''G'')
AND name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')
AND name NOT LIKE (''__\%'')'; -- comment this out if you want to audit contained domain users also
EXEC(@SQL);
FETCH NEXT FROM cursContainedDBs INTO @DBName;
END;
CLOSE cursContainedDBs;
DEALLOCATE cursContainedDBs;
SELECT DBName, UserName
FROM #ContainedDBUser;
DROP TABLE #ContainedDBUser;
After executing this script I also have about 5 out of 19 logins that have instance level access and appear in the master.sys.server_principals table and I could find them by executing this:
SELECT Username, name
from #ContainedDBUser a
inner join master.sys.server_principals b on a.Username = b.name
Now Let’s say I don’t DROP TABLE #ContainedDBUser so that I can still query it within the existing session, I’m a little confused at this point how to join #ContainedDBUser and master.sys.server_principals so that logins which exist in master.sys.server_principals are filtered out of my result set. Any ideas on how I can do this?
Thanks in advance!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Duplicate post. No replies here. Replies to http://www.sqlservercentral.com/Forums/Topic1812925-392-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass