SQL Stuff

Sensible Public Role Permissions

Creating a temp table to hold the Public Role permissions…
Here’s some quick sql logic to help make more sense of the public role permissions. This might be helpful when trying to figure out what revokes you might need to write in order to better secure your database environment. Using a small temporary table (populated with public role permissions), I’m able to write a much more simplified query to find the permissions I’m looking for.

Get a basic set of Public Role permissions.

Get a quick report to add to your query-to-email using SMTP (if this is something you already have setup).

Finally; a couple short queries to get the dirt on the Public Role, and the extended stored procedures or some the system objects.

use master;
set nocount on

-- build temp table to hold public role permissions
if object_id('tempdb..#role_permissions') is not null
drop table		[#role_permissions]
create table	[#role_permissions]
	[role]		varchar(255)
,	[permissions]	varchar(255)
,	[schema]	varchar(255)
,	[object]	varchar(255)
insert into [#role_permissions]
	'role'		= upper(sdprin.name)
,	'permission'	= sdperm.permission_name
,	'schema'	= object_schema_name(so.object_id)
,	'object'	= so.name 
	sys.all_objects so
	inner join sys.database_permissions	sdperm on so.object_id			= sdperm.major_id
	inner join sys.database_principals	sdprin on sdprin.principal_id	= sdperm.grantee_principal_id

select 'number of role permissions'	= count(*) from [#role_permissions]
select  top 20 * from [#role_permissions]

-- get existing permissions for PUBLIC role.
,	[permissions]
,	'object' = [schema] + '.' + [object]
	[role] = 'public'
order by
	[object] asc

-- looking at it a more sensible way when reading it you can do this...
	'Quick Permissions Report'		= ' The role  ' + [role] + '  has  ' + [permissions] + '  permissions on object:  [' + [schema] + '].[' + [object] + ']  under the  [' + upper(db_name()) + ']  database.'
	[role] = 'public'
order by
	[object] asc

-- couple short queries to get public role permissions to xp's and some system objects
select [role], [permissions], 'object' = [schema] + '.' + [object] from [#role_permissions] where [object] like 'xp%'
select [role], [permissions], 'object' = [schema] + '.' + [object] from [#role_permissions] where [object] like 'sys%'

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s