August 26, 2016 at 11:03 am
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!
August 26, 2016 at 11:19 am
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
August 26, 2016 at 11:38 am
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
August 26, 2016 at 12:43 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply