SQL Server - DBO without Backup

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

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.

StackExchange Database Administrators Q#13279, answer score: 2