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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply