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!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 26, 2016 at 11:47 am

  #1897884

  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