January 30, 2014 at 4:23 am
I was looking for a method of querying my SQL Server 2000/2005/2008 databases to gather a list of Databases users and their permissions.
I found this script on an old blog and it works very well.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
-- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]
( [DBNAME] [SYSNAME] ,
[USERNAME] [SYSNAME] , [DB_OWNER] [VARCHAR](3) ,
[DB_ACCESSADMIN] [VARCHAR](3) ,
[DB_SECURITYADMIN] [VARCHAR](3) ,
[DB_DDLADMIN] [VARCHAR](3) ,
[DB_DATAREADER] [VARCHAR](3) ,
[DB_DATAWRITER] [VARCHAR](3) ,
[DB_DENYDATAREADER] [VARCHAR](3) ,
[DB_DENYDATAWRITER] [VARCHAR](3) ,
[DT_CREATE] [DATETIME] NOT NULL,
[DT_UPDATE] [DATETIME] NOT NULL,
[DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3] DEFAULT (GETDATE()) ) ON [PRIMARY];
INSERT INTO [TEMPDB].[DBO].[DB_ROLES] EXEC SP_MSFOREACHDB ' SELECT ''?'' AS DBNAME,
USERNAME, MAX(CASE ROLENAME WHEN ''DB_OWNER'' THEN ''YES'' ELSE ''NO'' END) AS DB_OWNER,
MAX(CASE ROLENAME WHEN ''DB_ACCESSADMIN '' THEN ''YES'' ELSE ''NO'' END) AS DB_ACCESSADMIN ,
MAX(CASE ROLENAME WHEN ''DB_SECURITYADMIN'' THEN ''YES'' ELSE ''NO'' END) AS DB_SECURITYADMIN,
MAX(CASE ROLENAME WHEN ''DB_DDLADMIN'' THEN ''YES'' ELSE ''NO'' END) AS DB_DDLADMIN,
MAX(CASE ROLENAME WHEN ''DB_DATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DATAREADER,
MAX(CASE ROLENAME WHEN ''DB_DATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DATAWRITER,
MAX(CASE ROLENAME WHEN ''DB_DENYDATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAREADER,
MAX(CASE ROLENAME WHEN ''DB_DENYDATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAWRITER,
CREATEDATE,
UPDATEDATE,
GETDATE()
FROM (
SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE
FROM
[?].DBO.SYSMEMBERS A JOIN [?].DBO.SYSUSERS B ON A.MEMBERUID = B.UID
JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID )S
GROUP BY USERNAME, CREATEDATE, UPDATEDATE
ORDER BY USERNAME'
SELECT SERVERPROPERTY('SERVERNAME') AS [SERVERNAME],
B.NAME AS [LOGINNAME],
CASE B.SYSADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SYSADMIN,
CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SECURITYADMIN,
CASE B.SETUPADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SETUPADMIN,
CASE B.PROCESSADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS PROCESSADMIN,
CASE B.DISKADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS DISKADMIN,
CASE B.DBCREATOR WHEN '1' THEN 'YES' ELSE 'NO' END AS DBCREATOR,
CASE B.BULKADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS BULKADMIN, B.DBNAME AS [DEFAULT_DBNAME],
A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES]
A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME
--WHERE B.ISNTUSER=1 --INCLUDE TO EXCLUDE THE SQL LOGINS
SELECT * FROM #LOGINS ORDER BY [LOGINNAME]
DROP TABLE [TEMPDB].[DBO].[DB_ROLES]
DROP TABLE #LOGINS
But it ony gives me AD users and I need AD Users, AD Groups and SQL users.
Can anyone help? Or does anyone know of a dfferent method of gathering this information?
February 6, 2014 at 5:34 am
But it ony gives me AD users and I need AD Users, AD Groups and SQL users.
...
Do you Active Directory groups and user? If so you need to query Active Directory itself.
It better to be done not via SQL. Use scripting or .NET
February 6, 2014 at 5:46 am
Eugene Elutin (2/6/2014)
But it ony gives me AD users and I need AD Users, AD Groups and SQL users.
...
Do you Active Directory groups and user? If so you need to query Active Directory itself.
It better to be done not via SQL. Use scripting or .NET
I am not sure that you can query Active Directory to list SQL Server permissions :unsure:
I am looking to query each Database in our environment and list AD, AD Group and SQL Authenticated permissions to the Database.
February 6, 2014 at 6:14 am
ok this might get you started: if you enumerate the permissions for your windows group, and then join this query of active directory for gorup members, you cna get the individual windows logins, and know what their permissions are, since they are in the group.
IF OBJECT_ID('[tempdb].[dbo].[#TMP]') IS NOT NULL
DROP TABLE [dbo].[#TMP]
CREATE TABLE [dbo].[#TMP] (
[ACCOUNT NAME] NVARCHAR(256) NULL ,
[TYPE] VARCHAR(8) NULL ,
[PRIVILEGE] VARCHAR(8) NULL ,
[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,
[PERMISSION PATH] NVARCHAR(256) NULL )
DECLARE @cmd VARCHAR(MAX);
SELECT @cmd = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ';' + 'INSERT INTO #TMP EXEC master..xp_logininfo @acctname = ''' + name +''',@option = ''members'' '
FROM master.sys.server_principals
WHERE type_desc = 'WINDOWS_GROUP'
AND name NOT LIKE '%$%' --avoid errors like Could not obtain information about Windows NT group/user 'NT SERVICE\MSSQL$MSSQLSERVER1', error code 0x8ac.
FOR XML PATH('')
),1,1,'')
) s
SET @cmd = REPLACE(@cmd,';',';' + CHAR(13) + CHAR(10))
print @cmd
exec(@cmd)
SELECT * FROM #tmp
Lowell
February 6, 2014 at 6:15 am
I am not sure that you can query Active Directory to list SQL Server permissions :unsure:
You cannot...
I am looking to query each Database in our environment and list AD, AD Group and SQL Authenticated permissions to the Database.
If AD in your text stays for Active Directory, then the answer is the same:
You cannot list AD groups and/or its members in SQL Server as it has no way to differentiate between NT group or AD group and has no direct way querying AD.
February 6, 2014 at 7:14 am
Maybe handy ?
Exec sp_MSForEachDB 'SELECT
CONVERT(varchar(100), SERVERPROPERTY(''Servername''))
AS Server,
''?'' AS DB_Name,usu.name u_name,
CASE WHEN (usg.uid is null) THEN ''public''
ELSE usg.name
END as Group_Name,
CASE WHEN usu.isntuser=1 THEN ''Windows Domain
Account''
WHEN usu.isntgroup = 1 THEN ''Windows Group''
WHEN usu.issqluser = 1 THEN''SQL Account''
WHEN usu.issqlrole = 1 THEN ''SQL Role''
END as Account_Type,
lo.loginname,
lo.dbname AS Def_DB
FROM
[?]..sysusers usu LEFT OUTER JOIN
([?]..sysmembers mem INNER JOIN
[?]..sysusers usg ON mem.groupuid = usg.uid)
ON usu.uid = mem.memberuid LEFT OUTER JOIN
master.dbo.syslogins lo ON usu.sid = lo.sid
WHERE( usu.islogin = 1 AND
usu.isaliased = 0 AND
usu.hasdbaccess = 1) AND
(usg.issqlrole = 1 OR
usg.uid is null)'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply