November 15, 2011 at 9:51 pm
Comments posted to this topic are about the item Audit All Permissions
November 16, 2011 at 12:24 pm
Great script, and it did work on 2008 SP2.
I will use this and probably add to it. I want to identify the logins with instance level permissions outside the roles (view server state, alter trace, etc.) and the users with database permissions outside the database roles ("execute" any stored procedure/function, view definition, etc.).
November 16, 2011 at 12:31 pm
Ian T (11/16/2011)
Great script, and it did work on 2008 SP2.I will use this and probably add to it. I want to identify the logins with instance level permissions outside the roles (view server state, alter trace, etc.) and the users with database permissions outside the database roles ("execute" any stored procedure/function, view definition, etc.).
I'm glad you like it! I'm just now getting access to 2008, so I'm not sure if this misses anything "new" in 2008. I'll eventually test everything and repost any revisions necessary for 2008.
The fourth part of the script is meant to return permission granted directly to a user, outside of a role (at least in 2005).
Thanks for the feedback!
November 16, 2011 at 1:15 pm
The fourth part of the script is meant to return permission granted directly to a user, outside of a role (at least in 2005).
That's one of the beauties of this script, to catch these rights given to users outside a role. The IDE can get this but you have to look around for it.
Full disclosure, Jim is a colleague of mine. But this is better than the ones I've written or borrowed here. Part 3, auditing users and roles is really helpful. I use it all the time.
Ken
November 28, 2011 at 5:01 am
Well no doubt this will a good one.
But when i'm trying to execute this script all the time it's giving the following error. Have rectified few of them. Is there any spaces problem in syntax.
Msg 102, Level 15, State 1, Line 40
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 45
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 63
Incorrect syntax near '?'.
Waiting for the reply.
Thank you.
Mallikarjun
arj
November 28, 2011 at 11:01 am
I see it too, and I've seen it before when posting code on this site. It must be that special characters get in there instead of CRLF combinations. Try deleting the empty space on the line above the error. I haven't found a way to get these out of here except do each error separately.
It's worth it though 😀
Ken
November 30, 2011 at 10:09 am
Paste it into a good text editor, like PFE, where you are going to see unidentified character as '?', Replace them all with replace command.
Jason
http://dbace.us
😛
November 30, 2011 at 1:54 pm
Very handy! Here's the code with the characters removed and the database name safely quoted.
/*************************************************************************************************
*** Server Permissions Audit ***
**************************************************************************************************
This script is used for auditing the permissions that exist on a SQL Server. It will scan every
database on the server (separate scripts to run only one database are commented at the bottom)
and return four record sets:
1. Audit who is in server-level roles
2. Audit roles on each database, defining what they are and what they can do
3. Audit the roles that users are in
4. Audit any users that have access to specific objects outside of a role
NOTE: This script was written for MS SQL Server 2005 and uses undocumented system tables, rather
than the standard MS procedures. It is likely that this script will not work in future versions
of SQL Server.
Created: 2010-05-07
Jim Sebastiano
*/
DECLARE @ShowOnlyThisLogin VARCHAR(50)
DECLARE @SQLCmd VARCHAR(max)
SET @ShowOnlyThisLogin = NULL -- leave null for all IDs, otherwise 'SomeLogin'
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SET NOCOUNT ON
DECLARE @currDB VARCHAR(100), @sql varchar(2000)
DECLARE @databases TABLE (dbname VARCHAR(100))
INSERT INTO @databases (dbname)
SELECT [Name]
FROM master.sys.databases
WHERE state_desc <> 'OFFLINE'
IF OBJECT_ID('tempdb..#AuditServerRoles') IS NOT NULL
DROP TABLE #AuditServerRoles
IF OBJECT_ID('tempdb..#AuditDatabaseRoles') IS NOT NULL
DROP TABLE #AuditDatabaseRoles
IF OBJECT_ID('tempdb..#AuditDatabaseRoleAssignments') IS NOT NULL
DROP TABLE #AuditDatabaseRoleAssignments
IF OBJECT_ID('tempdb..#AuditUserLevelAssignments') IS NOT NULL
DROP TABLE #AuditUserLevelAssignments
CREATE TABLE #AuditServerRoles
(ServerName VARCHAR(100), DatabaseName VARCHAR(100), ServerRole VARCHAR(100), MemberName VARCHAR(100))
CREATE TABLE #AuditDatabaseRoles
(ServerName VARCHAR(100), DatabaseName VARCHAR(100), RoleName VARCHAR(100), SchemaName VARCHAR(100),
ObjectName VARCHAR(100), PermissionType VARCHAR(100), StateDesc VARCHAR(100), Grantor VARCHAR(100))
CREATE TABLE #AuditDatabaseRoleAssignments
(ServerName VARCHAR(100), DatabaseName VARCHAR(100), RoleName VARCHAR(100), UserName VARCHAR(100))
CREATE TABLE #AuditUserLevelAssignments
(ServerName VARCHAR(100), DatabaseName VARCHAR(100), SchemaName VARCHAR(100), ObjectName VARCHAR(100),
ObjectType VARCHAR(100), Grantee VARCHAR(100), Grantor VARCHAR(100),
UserType VARCHAR(100), PermissionType VARCHAR(100), PermissionState VARCHAR(100))
-- Step 1: Audit who is in server-level roles
INSERT INTO #AuditServerRoles
SELECT
@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
SUSER_NAME(rm.role_principal_id) AS ServerRole, lgn.name AS MemberName
FROM
sys.server_role_members rm
INNER JOIN sys.server_principals lgn
ON rm.role_principal_id >=3 AND rm.role_principal_id <=10
AND rm.member_principal_id = lgn.principal_id
ORDER BY 1, 2, 3, 4
-- loop through all databases
while exists (select * from @databases)
begin
set @currDB = (select top 1 dbname from @databases order by dbname)
PRINT @currdb
-- Step 2: Audit roles on each database, defining what they are, what they can do, and who belongs in them
SELECT @SQLCmd = '' +
'use ' + QUOTENAME(@currdb) + ';
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, dprin.name AS RoleName,
ISNULL(sch.name, osch.name) AS SchemaName, ISNULL(o.name, ''.'') AS ObjectName,
dperm.permission_name, dperm.state_desc, grantor.name AS Grantor
FROM
sys.database_permissions dperm
INNER JOIN sys.database_principals dprin
ON dperm.grantee_principal_id = dprin.principal_id
INNER JOIN sys.database_principals grantor
ON dperm.grantor_principal_id = grantor.principal_id
LEFT OUTER JOIN sys.schemas sch
ON dperm.major_id = sch.schema_id AND dperm.class = 3
LEFT OUTER JOIN sys.all_objects o
ON dperm.major_id = o.OBJECT_ID AND dperm.class = 1
LEFT OUTER JOIN sys.schemas osch
ON o.schema_id = osch.schema_id
WHERE dprin.name <> ''public'' AND dperm.type <> ''CO'' AND dprin.type = ''R''
ORDER BY 1, 2, 3, 4, 5, 6'
INSERT INTO #AuditDatabaseRoles
exec (@SQLCmd)
-- Step 3: Audit the roles that users are in
SELECT @SQLCmd = '' +
'use ' + QUOTENAME(@currdb) + ';
SELECT
@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
CASE WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END AS RoleName,
u.name AS UserName
FROM
sys.database_principals u
LEFT JOIN (sys.database_role_members m JOIN sys.database_principals r ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
ORDER BY 1, 2, 3, 4'
INSERT INTO #AuditDatabaseRoleAssignments
exec (@SQLCmd)
-- Step 4: Audit any users that have access to specific objects outside of a role
SELECT @SQLCmd = '' +
'use ' + QUOTENAME(@currdb) + ';
SELECT
@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
ISNULL(sch.name, osch.name) AS SchemaName, ISNULL(o.name, ''.'') AS ObjectName,
o.type_desc,
dprin.NAME AS Grantee,
grantor.name AS Grantor,
dprin.type_desc AS principal_type_desc,
dperm.permission_name,
dperm.state_desc AS permission_state_desc
FROM
sys.database_permissions dperm
INNER JOIN sys.database_principals dprin
ON dperm.grantee_principal_id = dprin.principal_id
INNER JOIN sys.database_principals grantor
ON dperm.grantor_principal_id = grantor.principal_id
LEFT OUTER JOIN sys.schemas sch
ON dperm.major_id = sch.schema_id AND dperm.class = 3
LEFT OUTER JOIN sys.all_objects o
ON dperm.major_id = o.OBJECT_ID AND dperm.class = 1
LEFT OUTER JOIN sys.schemas osch
ON o.schema_id = osch.schema_id
WHERE dprin.name <> ''public'' AND dperm.type <> ''CO'' AND dprin.type <> ''R''
ORDER BY 1, 2, 3, 4, 5'
INSERT INTO #AuditUserLevelAssignments
exec (@SQLCmd)
delete from @databases where dbname = @currDB
END
IF @ShowOnlyThisLogin IS NULL
BEGIN
SELECT 'Server Roles', * FROM #AuditServerRoles ORDER BY 1,2,3,4,5
SELECT 'Database Roles', * FROM #AuditDatabaseRoles ORDER BY 1,2,3,4,5,6,7
SELECT 'DB Role Assignments', * FROM #AuditDatabaseRoleAssignments ORDER BY 1,2,3,4,5
SELECT 'User Level Assignments', * FROM #AuditUserLevelAssignments ORDER BY 1,2,3,4,5,6
END ELSE BEGIN
SELECT 'Server Roles', * FROM #AuditServerRoles WHERE MemberName = @ShowOnlyThisLogin ORDER BY 1,2,3,4,5
SELECT 'DB Role Assignments', * FROM #AuditDatabaseRoleAssignments WHERE UserName = @ShowOnlyThisLogin ORDER BY 1,2,3,4,5
SELECT 'User Level Assignments', * FROM #AuditUserLevelAssignments WHERE Grantee = @ShowOnlyThisLogin ORDER BY 1,2,3,4,5,6
END
DROP TABLE #AuditServerRoles, #AuditDatabaseRoles, #AuditDatabaseRoleAssignments, #AuditUserLevelAssignments
/* originals
-- Step 1: Audit who is in server-level roles
SELECT
@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
SUSER_NAME(rm.role_principal_id) AS ServerRole, lgn.name AS MemberName
FROM
sys.server_role_members rm
INNER JOIN sys.server_principals lgn
ON rm.role_principal_id >=3 AND rm.role_principal_id <=10
AND rm.member_principal_id = lgn.principal_id
ORDER BY 1, 2, 3, 4
-- Step 2: Audit roles on each database, defining what they are, what they can do, and who belongs in them
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, dprin.name AS RoleName,
ISNULL(sch.name, osch.name) AS SchemaName, ISNULL(o.name, '.') AS ObjectName,
dperm.permission_name, dperm.state_desc, grantor.name AS Grantor
FROM
sys.database_permissions dperm
INNER JOIN sys.database_principals dprin
ON dperm.grantee_principal_id = dprin.principal_id
INNER JOIN sys.database_principals grantor
ON dperm.grantor_principal_id = grantor.principal_id
LEFT OUTER JOIN sys.schemas sch
ON dperm.major_id = sch.schema_id AND dperm.class = 3
LEFT OUTER JOIN sys.all_objects o
ON dperm.major_id = o.OBJECT_ID AND dperm.class = 1
LEFT OUTER JOIN sys.schemas osch
ON o.schema_id = osch.schema_id
WHERE dprin.name <> 'public' AND dperm.type <> 'CO' AND dprin.type = 'R'
ORDER BY 1, 2, 3, 4, 5, 6
-- Step 3: Audit the roles that users are in
SELECT
@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
CASE WHEN (r.principal_id IS NULL) THEN 'public'
ELSE r.name
END AS RoleName,
u.name AS UserName
FROM
sys.database_principals u
LEFT JOIN (sys.database_role_members m JOIN sys.database_principals r ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
--WHERE u.type <> 'R'
ORDER BY 1, 2, 3, 4
-- Step 4: Audit any users that have access to specific objects outside of a role
SELECT
@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
ISNULL(sch.name, osch.name) AS SchemaName, ISNULL(o.name, '.') AS ObjectName,
o.type_desc,
dprin.NAME AS Grantee,
grantor.name AS Grantor,
dprin.type_desc AS principal_type_desc,
dperm.permission_name,
dperm.state_desc AS permission_state_desc
FROM
sys.database_permissions dperm
INNER JOIN sys.database_principals dprin
ON dperm.grantee_principal_id = dprin.principal_id
INNER JOIN sys.database_principals grantor
ON dperm.grantor_principal_id = grantor.principal_id
LEFT OUTER JOIN sys.schemas sch
ON dperm.major_id = sch.schema_id AND dperm.class = 3
LEFT OUTER JOIN sys.all_objects o
ON dperm.major_id = o.OBJECT_ID AND dperm.class = 1
LEFT OUTER JOIN sys.schemas osch
ON o.schema_id = osch.schema_id
WHERE dprin.name <> 'public' AND dperm.type <> 'CO' AND dprin.type <> 'R'
ORDER BY 1, 2, 3, 4, 5
*/
/* Anything is possible but is it worth it? */
March 29, 2012 at 2:01 pm
Thanks for posting this!
I ended up using this to create a stored procedure that I can put onto all of my servers. I then use a package to connect to all of my servers and put this information into a central location. Works amazingly! 😀
May 12, 2016 at 6:35 am
Thanks for the script.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply