December 14, 2011 at 12:19 pm
I have a database role(Dev_Group) to my database.
I need to give grant only Stored Procedure permissions to that group
ie Create, Drop and Alter only stored procedures not ant other objects
I know to we can grant create procedure
But how can I grant Drop and Alter only to all the Stored Procedures in Database
December 14, 2011 at 12:28 pm
You can generate the statements to grant permissions on existing objects by querying off the sys.objects table where object type is P (proc) like this:
print 'go'
print '--object level perms'
select 'GRANT ALTER
ON [' + s.name +'].['+ o.name collate Latin1_general_CI_AS+ '] TO [GROUPNAME]'
from sys.database_permissions
p inner join sys.objects o on p.major_id = o.object_id inner join sys.schemas s
on s.schema_id = o.schema_id inner join sys.database_principals u
on p.grantee_principal_id = u.principal_id
select 'GRANT DROP
ON [' + s.name +'].['+ o.name collate Latin1_general_CI_AS+ '] TO [GROUPNAME]'
from sys.database_permissions
p inner join sys.objects o on p.major_id = o.object_id inner join sys.schemas s
on s.schema_id = o.schema_id inner join sys.database_principals u
on p.grantee_principal_id = u.principal_id
as new procs are added, you would then need to grant those permissions.
December 14, 2011 at 2:58 pm
Is there anyway I can give the users DDL permissiopns and create a trigger to allow only Admins and dbo's to alter, create & Drop tables any tables ?
If yes can any one help me with trigger
December 14, 2011 at 3:08 pm
Maybe grant ddl admin, then DENY perms on the tables... It think that would work. In a car, so cant test presently. I guess you could use triggers, but that could get uglier.
December 15, 2011 at 11:44 am
YOu could definitely do a DDL trigger to control who can ALTER what, but I think you'd be better off explicitly managing permissions on the SP's. I'd even be more likely to put a DDL trigger for CREATE PROCEDURE that automatically GRANT's ALTER permissions on the newly created SP to the group.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply