Similar to my previous question : Prevent Non Copy Only Backups, how do I create a user that can do everything to my database <EXCEPT> 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 (stored procedures, 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]
throws an error
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself
Also, if I just try to execute the stored procedure, I get an
EXECUTE permission denied
I'm open for ideas.
Thanks.
Best Answer
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.