One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Thursday, March 24, 2016

Pulling Security Info

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