SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Audit All Permissions


Audit All Permissions

Author
Message
Jim Sebastiano
Jim Sebastiano
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 171
Comments posted to this topic are about the item Audit All Permissions
Ian T
Ian T
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1581 Visits: 269
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.).



Jim Sebastiano
Jim Sebastiano
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 171
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!
ken.trock
ken.trock
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2166 Visits: 1735
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
arj
arj
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 240
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


Kindest Regards,

arj
ken.trock
ken.trock
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2166 Visits: 1735
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 :-D

Ken
jswong05
jswong05
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 476
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
:-P
Gatekeeper
Gatekeeper
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 889
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? */
Giraffe DBA
Giraffe DBA
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 566
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! :-D
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32054 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search