question

amit.cs03 avatar image
amit.cs03 asked

Permissions assigned to Public role

I need a script to find out all the permissions assigned to Public role in a SQL Server.
sql serversql query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
A quick Google search revealed several hits. This one borrowed from here: http://dba.stackexchange.com/questions/36618/list-all-permissions-for-a-given-role I think would be sufficient for what you are looking for. SELECT DISTINCT rp.name, ObjectType = rp.type_desc, PermissionType = pm.class_desc, pm.permission_name, pm.state_desc, ObjectType = CASE WHEN obj.type_desc IS NULL OR obj.type_desc = 'SYSTEM_TABLE' THEN pm.class_desc ELSE obj.type_desc END, [ObjectName] = Isnull(ss.name, Object_name(pm.major_id)) FROM sys.database_principals rp INNER JOIN sys.database_permissions pm ON pm.grantee_principal_id = rp.principal_id LEFT JOIN sys.schemas ss ON pm.major_id = ss.schema_id LEFT JOIN sys.objects obj ON pm.[major_id] = obj.[object_id] WHERE rp.type_desc = 'DATABASE_ROLE' AND pm.class_desc <> 'DATABASE' ORDER BY rp.name, rp.type_desc, pm.class_desc
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks John but this scripts gives all the default permissions as well on system objects which needs to be filtered out.
0 Likes 0 ·
Forgive me, isn't that what you were looking for? All of the permissions for a given role?
0 Likes 0 ·
I need permissions granted to public role for all databases barring system objects. The script in my previous reply perfectly suits the requirements but I'm not able to roll it up in sp_msforeachdb.
0 Likes 0 ·
amit.cs03 avatar image
amit.cs03 answered
I also consolidated below script for this but it runs per database. I need to execute this against all databases on the server. I tried wrapping it up in sp_msforeachdb but it fails because I'm not able to handle single quotes properly. SELECT [class_desc], p.[state_desc] AS [PermissionType] ,p.[permission_name] AS [PermissionName] ,USER_NAME(p.[grantee_principal_id]) AS [DatabaseRole] ,CASE p.[class] WHEN 0 THEN 'Database::' + DB_NAME() WHEN 1 THEN OBJECT_NAME(major_id) WHEN 3 THEN 'Schema::' + SCHEMA_NAME(p.[major_id]) END AS [ObjectName] FROM [sys].[database_permissions] p INNER JOIN sys.objects AS o ON p.major_id = o.object_id WHERE p.[class] IN (0, 1, 3) AND p.[minor_id] = 0 AND USER_NAME(p.[grantee_principal_id])='public' AND OBJECTPROPERTY(o.object_id, 'IsMSShipped') = 0
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.