Hi friends,
Sorry for this long inactivity but I was a bit busy with one of my clients from Mexico Republic working in Europe.
This guy is an IT Auditor and he has a lot of queries regarding security and auditing in SQL Server. I will keep writing about the queries posed by him. So here is the first and a simple one.
He wanted to find out the way to get a list of all logins, well categorized by different criteria. eg. He wanted a list of all windows logins mapped as a SQL Server Login. All windows users mapped as SQL Login and all these logins mapped to which database as which user along with their default database.
So, for this query we need a few system catalog views. First is the sys.server_principals. This is really a good place to look for when you want to see all the server logins along with which server level role they are registered in the server (eg. sysadmin, serveradmin etc.). Another is the sys.sysusers. This is the catalog view which will give you the information about users mapped in different databases. Remember that this catalog view can give different results for different databases.
So here is that simple query, use it or modify it according to your own needs. DECLARE @i int, @ctr int, @dbname nchar(50)
SELECT @i = COUNT(name) FROM sys.databases
SET @ctr = 1
WHILE (@ctr <= @i) BEGIN SELECT @dbname = name from sys.databases WHERE database_id = @ctr
SELECT @dbname [Database Name]
EXECUTE('USE ' + @dbname + '; Select P.name [Server Login Name], S.name [Database User Name] , default_database_name [Default Database]' +
'FROM sys.server_principals P INNER JOIN sys.sysusers S ' +
'ON P.sid = S.sid WHERE S.name != ''dbo''')
SET @ctr = @ctr + 1
END
