SQL Server - DBO without Backup

Question

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.

Answer 1

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.

Answer 2

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.