A frequent request I receive is to pull a list of logins/users with certain accesses, role memberships, etc.
I had a query to use xp_logininfo to pull group membership chains - that is, DOMAIN\Andy has access, but not directly - DOMAIN\Andy has access because he is a member of DOMAIN\DBAGroup. The query is this:
/*
Domain Login Group Security Info
*/
DECLARE @name sysname
CREATE TABLE ##logininfo
(
[account name] sysname,
[type] nvarchar(50),
[privilege] nvarchar(50),
[mapped login name] sysname,
[permission path] sysname
)
DECLARE namecursor cursor fast_forward
for
select name from master.sys.server_principals
where type='G' and name not like 'NT SERVICE%'
open namecursor
fetch next from namecursor into @name
WHILE @@fetch_status=0
BEGIN
insert into ##logininfo EXEC ('xp_logininfo '''+ @name+''',''members''')
fetch next from namecursor into @name
END
CLOSE namecursor
DEALLOCATE namecursor
select @@SERVERNAME as InstanceName, *
from ##logininfo
/*
where [mapped login name] like'%agalb%'
*/
DROP TABLE ##logininfo
I needed the other half (or two-thirds) - I needed the ability to pull server and database role memberships. Rather than script something from scratch I went looking and sure enough found the raw material for what I wanted on SQLServerCentral in the forums in the post "Query to get the lisst of logins having sysadmin and serveradmin."
The query is pulled from Jagan Kondapalli's (@JVKondapalli) reply to the original poster's question. I modified it in a couple places and am posting my modification here:
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##LOGINS' AND type in (N'U'))
DROP TABLE ##LOGINS
GO
USE tempdb
GO
/*CREATE TABLE ##LOGINS
(
[Login Name] varchar(50),
[Default Database] varchar(60),
[Login Type] varchar(40),
[AD Login Type] varchar(40),
[sysadmin] char(5),
[securityadmin] char(5),
[serveradmin] char(5),
[setupadmin] char(5),
[processadmin] char(5),
[diskadmin] char(5),
[dbcreator] char(5),
[bulkadmin] char(5)
)*/
CREATE TABLE ##Users
(
[Database] VARCHAR(64),
[Database User ID] VARCHAR(64),
[Server Login] VARCHAR(64),
[Database Role] VARCHAR(64)
)
use master
go
SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname
WHEN 1 THEN 'AD Login'
ELSE 'SQL Login'
END AS [Login Type],
CASE
WHEN isntgroup = 1 THEN 'AD Group'
WHEN isntuser = 1 THEN 'AD User'
ELSE ''
END AS [AD Login Type],
CASE sysadmin
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [sysadmin],
CASE [securityadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [securityadmin],
CASE [serveradmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [serveradmin],
CASE [setupadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [setupadmin],
CASE [processadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [processadmin],
CASE [diskadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [diskadmin],
CASE [dbcreator]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [dbcreator],
CASE [bulkadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [bulkadmin]
INTO ##LOGINS
FROM dbo.syslogins /*IN ORDER TO GET THE ACCESS INFORMATION A LOGIN ADD THE LOGIN NAME TO THE WHERE CLAUSE BELOW*/
--WHERE [LOGINNAME] = 'PUNCH IN THE LOGIN NAME HERE'
SELECT @@SERVERNAME as InstanceName, [Login Name], [Default Database],
[Login Type], [AD Login Type], [sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin], [diskadmin], [dbcreator], [bulkadmin]
FROM tempdb..##LOGINS
--where [mapped login name] like'%agalb%'
ORDER BY [Login Type], [AD Login Type], [Login Name]
--
USE master
GO
DECLARE @DBName VARCHAR(60)
DECLARE @SQLCmd VARCHAR(1024)
Declare @DBID varchar(3)
set @DBID = (select MAX(database_id) from sys.databases)
--print @DBID
WHILE @DBID != 0
BEGIN
set @DBName = (select DB_NAME (''+@DBID+''))
SELECT @SQLCmd = 'INSERT ##Users ' +
' SELECT ''' + @DBName + ''' AS [Database],' +
' su.[name] AS [Database User ID], ' +
' COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
' FROM [' + @DBName + '].[dbo].[sysusers] su' +
' LEFT OUTER JOIN ##LOGINS u' +
' ON su.sid = u.sid' +
' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' +
' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' +
' ON sm.groupuid = sug.uid)' +
' ON su.uid = sm.memberuid ' +
' WHERE su.hasdbaccess = 1' +
' AND su.[name] != ''dbo'' '
IF DATABASEPROPERTYEX(@DBName, 'Status')='ONLINE'
EXEC (@SQLCmd)
print @DBID
set @DBID = @DBID - 1
END
SELECT @@SERVERNAME as InstanceName,*
FROM ##Users
/*IN ORDER TO GET THE ACCESS INFORMATION A USER ADD THE USER TO THE WEHRE CLUASE BELOW*/
--WHERE [Database User ID] = 'PUNCH IN THE USER HERE'
/*IN ORDER TO GET THE ACCESS INFORMATION OF ALL USERS TO A PARTICULAR DATABASE, ADD THE DATABASE NAME TO THE WHERE CLUASE BELOW*/
--WHERE [DATABASE] = 'PUNCH IN YOUR DATABASE NAME HERE'
--where [server login] like '%AGALBRAI%'
ORDER BY [Database], [Database User ID]
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##LOGINS' AND type in (N'U'))
DROP TABLE ##LOGINS
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users
Again - this is *not* my underlying query but it is exactly what I needed to pull role memberships. I usually pull this info and dump it into Excel for client consumption.
I could not find a blog or other presence for Jagan besides his SQLServerCentral profile and the inactive Twitter account mentioned above, but thanks!
--
Hope this helps!
No comments:
Post a Comment