Do not SELECT if in Table B

  • 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!

  • How about this?

    WHERE not exists (SELECT 1 from sys.server_principals where

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • I appreciate your response but I'm not really sure how you intended for me to use that. Can you make it a little clearer for me? I appreciate

  • with JustInD as (select name

    from sys.server_principals db

    where not exists (select 1 from master.sys.server_principals mp

    where db.name = mp.name)

    )

    This cte gives you the names that exist in sys.server_principals in your current database but not in the master. However it looks like you are wanting to walk through all the databases doing this. Add this to your code and plug in @DBName in your from clause.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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