March 11, 2014 at 10:16 pm
i need to prepare audit report which list all the server roels for all the users in the sql server .
any query to get this info in sql2005\sql2008
March 11, 2014 at 10:52 pm
Use Master
GO
select su.name,p.name from sys.server_principals P
INNER JOIN sys.server_role_members sr
On P.principal_id = sr.role_principal_id
INNER JOIN (Select sr.Name,p.principal_id from sys.syslogins sr INNER JOIN sys.server_principals p
On P.sid = sr.sid)su
On sr.member_principal_id = su.principal_id
Regards,
Mitesh OSwal
+918698619998
March 12, 2014 at 8:04 am
USE master
GO
SET NOCOUNT ON
-- Get all roles
CREATE TABLE #temp_srvrole(
ServerRole VARCHAR(128),
Description VARCHAR(128)
)
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole
-- sp_help syslogins
CREATE TABLE #temp_memberrole(
ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300)
)
DECLARE @ServerRole VARCHAR(128)
DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole
FROM
#temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END
CLOSE srv_role
DEALLOCATE srv_role
SELECT ServerRole
, MemberName
FROM
#temp_memberrole
-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS (SELECT *
FROM
#temp_memberrole
WHERE
MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin')
BEGIN
CREATE TABLE #temp_localadmin(
output VARCHAR(8000)
)
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'
SELECT output AS local_administrator
FROM
#temp_localadmin
WHERE
output LIKE '%\%'
DROP TABLE #temp_localadmin
END
DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole
-- Get individual Logins
SELECT name
, 'Individual NT Login' LoginType
FROM
syslogins
WHERE
isntgroup = 0
AND isntname = 1
UNION
SELECT name
, 'Individual SQL Login' LoginType
FROM
syslogins
WHERE
isntgroup = 0
AND isntname = 0
UNION ALL
-- Get Group logins
SELECT name
, 'NT Group Login' LoginType
FROM
syslogins
WHERE
isntgroup = 1
-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin(
output VARCHAR(8000)
)
CREATE TABLE #temp_groupadmin2(
groupName VARCHAR(256),
groupMember VARCHAR(1000)
)
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)
DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT replace(name, 'US\', '')
FROM
syslogins
WHERE
isntgroup = 1
AND name LIKE 'US\%'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin
PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd
SET ROWCOUNT 8
DELETE
FROM
#temp_groupadmin
SET ROWCOUNT 0
INSERT INTO #temp_groupadmin2
SELECT @grpname
, output
FROM
#temp_groupadmin
WHERE
output NOT LIKE ('%The command completed successfully%')
FETCH NEXT FROM grp_role INTO @grpname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT *
FROM
#temp_groupadmin2
DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2
PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''
-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember(
DbRole VARCHAR(128),
MemberName VARCHAR(128),
MemberSID VARCHAR(1000)
)
CREATE TABLE #temp_rolemember_final(
DbName VARCHAR(100),
DbRole VARCHAR(128),
MemberName VARCHAR(128)
)
DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)
DECLARE grp_role CURSOR FOR
SELECT name
FROM
sysdatabases
WHERE
name NOT IN ('tempdb')
AND databasepropertyex(name, 'Status') = 'ONLINE'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'
PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE (@sqlcmd2)
INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName
, DbRole
, MemberName
FROM
#temp_rolemember
FETCH NEXT FROM grp_role INTO @dbname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT *
FROM
#temp_rolemember_final
DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply