Explicit Permissions
-
I am trying to create a script that will deny permission at Schema level and I am struggling to find a way to list the available permissions. If access/deny access is already given I can use the following to create my script:-
SELECT DISTINCT 'DENY SELECT ON [sys].' + permission_name + ' myuser
from sys.database_permissions
WHERE permission_name NOT IN ('CONNECT','EXECUTE','SELECT')
However if no permissions are set I cannot list the permission_name, is there a system procedure/view to list them, I've not been able to find anything. (I know I need not set permissions here but its not my requirement)
-
Not entirely sure what you're attempting here ... but this may or may not help point you in the right direction (this will show you a listing of all permissions per role/user):
WITH perms_cte(principal_name,principal_id, principal_type_desc,class_desc, [object_name], permission_name, permission_state_desc, login ) as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS [object_name],
p.permission_name,
p.state_desc AS permission_state_desc,
sp.name as login
from sys.database_permissions p
left JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id
left Join sys.server_principals sp on dp.sid = sp.sid
)
-- users
SELECT p.principal_name, p.principal_type_desc, login, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(' ' as sysname) as role_name
FROM perms_cte p
UNION
-- role members
SELECT rm.member_principal_name, rm.principal_type_desc, rm.login, p.class_desc, p.[object_name], p.permission_name, coalesce(p.permission_state_desc, ' '), rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name, sp.name as login
from sys.database_role_members rm
INNER JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id
left Join sys.server_principals sp on dp.sid = sp.sid
) rm
ON rm.role_principal_id = p.principal_id
order by 1, 2, 4, 5, 6, 8
-
So you want to find what schema's do not yet have any permissions to them, so you can then deny access to them? I am pretty familiar with most permissions related activities, so hopefully I can help you. Just not clearly following exactly what you're looking for.
-
I want to be able to explicitly deny access to the full range of schema roles ie:-
ALTER ANY APPLICATION ROLE
ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION etc...
Except for say SELECT and EXECUTE.
-
My apologies, perhaps I'm still not following ... but If I understand correctly, you want to be able to more or grant deny's on all objects that do not yet have permissions granted?
-
Yeah that's kind of a weird one ...
Without spending to much time on this, this will at least give you the objects that do not have explicitly granted individual permissions on them. Yet if the object is contained with in a GRANT ALL statement (say someone did a GRANT EXECUTE TO ), than this won't help ...
SELECT
[object_id]AS [ObjectID]
,OBJECT_NAME(object_id) AS [ObjectName]
,type_descAS [ObjectType]
FROM sys.objects
WHERE [object_id] NOT IN (SELECT major_id FROM sys.database_permissions)
AND [type] <> 'S'
Sorry, I know it isn't much and you probably already know how to do the above, but what you're asking would require a bit more time to work on ... I haven't dug that far into permissions from that perspective. Still though, I'd question why this needs to be done in the first place and propose different security measures as this ... I would not agree with unless there was good reasoning behind it.
I'll see what I can do later If I get some free time to work on a better query.
-
Carolyn Richardson (3/11/2008)
I want to be able to explicitly deny access to the full range of schema roles ie:-ALTER ANY APPLICATION ROLE
ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION etc...
Except for say SELECT and EXECUTE.
The problem with your question is that these are not Schema Roles. In fact there is no such thing as Schema Roles (just Server Roles and Database Roles, OK, technically application Roles also). What you are listing here are Database Permissions, so it is a little bit confusing.
Now assuming that what you want is a list of all POSSIBLE permissions, then you can get that from: [font="Courier New"]SELECT * FROM sys.fn_builtin_permissions('')[/font].
If you want to limit to just the permissions that could directly or indirectly affect a user's access to an Object, except for SELECT and EXECUTE, then try something like:
Select Class_desc, permission_name from fn_builtin_permissions('')Where Class_desc IN ('SERVER','DATABASE','SCHEMA','OBJECT')
And permission_name NOT IN ('SELECT', 'EXECUTE')
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply