January 9, 2006 at 9:18 am
I have created a User Role for our developers. I can't seem to find where you can specify that a User Role can Alter Procedures. I don't want to add our developers to the ddl_admin role cause it has more permissions than I want them to have. However I would like for them to alter existing SPs. I have given the UserRole specific permissions in the permissions tab of the properties menu of the database but I don't see an "alter procedure" option (just create) and the Update Procedure check box in the Database Role Properties window isn't selectable (like the tables and views). Does anyone have a clue as to where this might be? Thanks.
January 9, 2006 at 9:43 am
You can grant the right to CREATE PROCEDURE. This also gives the right to alter. The two are inseparable in SQL Server 7.0/2000. However, the stored procedure must also be owned by them. Otherwise you will have to give them db_ddladmin. It and db_owner are the only two roles which can modify an object that the user doesn't own.
K. Brian Kelley
@kbriankelley
January 9, 2006 at 12:58 pm
Thanks Brian. Is it true that if I explicitly deny this user any permission it will take precedence over any allowed "role" permission?
January 9, 2006 at 2:26 pm
Not always. For instance, I believe db_datareader and db_datawriter ignore DENYs for the appropriate operations on tables and views. Not sure about db_ddladmin. You'll definitely want to test to see if db_ddladmin gets implicit permissions and bypasses any DENYs.
K. Brian Kelley
@kbriankelley
January 9, 2006 at 2:43 pm
Will do. Thanks for your time as always it's much appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply