Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Similar to my previous Question : Prevent Non Copy Only Backups, how do I create a user that can do everything to my database take backups at all. I've granted them every role except DB_backupOperator and DB_Owner. The issue is that the user will need to create objects (sprocs, functions, tables, whatever) in the database.

With these roles, that is possible, however, they are not able to use the objects that they create.

I.E.

create proc test as select 1
go
grant exec on test to [OtherUser]

this completes successfully

grant exec on test to [TheCurrentUser]

gives me a "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself" Also, if i just try to execute the sproc, I get an EXECUTE permission denied.

I'm open for ideas.

thanks.

share|improve this question

migrated from serverfault.com Feb 17 '12 at 4:38

This question came from our site for professional system and network administrators.

2 Answers 2

Grant the user SELECT, INSERT, UPDATE, DELETE, EXECUTE and VIEW DEFINITION rights to the database (or the schema). This will grant the user the rights to all the objects within the database (or the schema).

You'll probably also need to give them rights like ALTER ANY OBJECT within the database as well so that they can modify the objects.

share|improve this answer

Script out the stored procedure, and examine the very last line. I will bet you 1 large coffee that you have a "grant exec" statement at the end of your proc. This happened because whoever compiled the proc did not put a GO statement before the grant, so it became included with proc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.