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.
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.