Have you ever wanted to grant a user account the ability to execute permissions in a database without having to worry about maintaining object level permissions? Maybe we should back up a bit… When you have an account that needs to read data only, it is easy enough to add them to the db_datareader database role. If you want to allow that same account to update the tables directly then db_datawriter is the role you need to add the account to. But what do you do if you need to grant execute permissions to an account and you don’t want to maintain the permissions long term? You have a couple options… You can always create a role and manage the execute permissions to each proc allowing the execute role to have execute permissions.
You can take advantage of the schema’s in the database. A simple call to a couple system stored procs is going to allow you to grant execute rights to anyone who has permissions to that schema. For Example:
‘YourLogin’, ‘YourUser’, ‘YourRole’
:: DBO TO YourRole
According to Books On Line the stored procedure sp_AddUser is being phased out to be replaced by a Create User Statement. The Create User Statement looks a lot more like this:
CREATE USER your_database_user FOR LOGIN sqlserver_Login_Account
My only complaint so for with the CREATE USER statement is that I can assign it to a database role as I create it. There may be a way and I just have not found it yet.