Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rohaandba
Starting Member
33 Posts |
 Posted - 2011-12-14 : 14:18:27
|
| I have a database role(Dev_Group) to my database.I need to give grant only Stored Procedure permissions to that groupie Create, Drop and Alter only stored procedures not ant other objects I know to we can grant create procedureBut how can I grant Drop and Alter only to all the Stored Procedures in Database |
|
|
pnash
Starting Member
26 Posts |
Posted - 2011-12-14 : 15:32:08
|
| you can use below query to grant alter permission on all the sps to a particular user SELECT 'Grant alter on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) + ' TO User_name' FROM INFORMATION_SCHEMA.ROUTINES WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0 |
 |
|
|
rohaandba
Starting Member
33 Posts |
Posted - 2011-12-14 : 16:58:33
|
| 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 |
|
|
|
|
|
|
Stored Procedure Permissions