If you are in a high-security environment, this may not be the best tip for you, but if you’re in a situation like mine where you have a SQL user that is connecting to a database in order to EXECUTE stored procs, and you know that user needs to be able to EXECUTE every proc in that database, you can save a little time by creating a role for that:
CREATE ROLE usp_execute
GO
GRANT EXECUTE ON SCHEMA::dbo TO usp_execute
GO
This means that the user will be able to execute EVERY stored proc belonging to the schema dbo from this point forward; again, be cautious when using this. Security models should not be taken lightly.
The only issue with doing this I that it also implicitly grants the ability to alter the schema objects as well.
Tim, I must be missing something because my test doesn’t show that; can you elaborate? I’m running SQL Server 2008, logged in as a user which only belongs to the new role I created (and public, of course). I can execute procs, but I can’t alter anything (unless the proc does the alteration for me).
What am I missing?
Stu
Hi Stuart,
I agree, using roles is much simpler than cherrypicking objects and manipulating rights on them. Even in highly secure environments, you can design security model that will use roles and schemas to provide optimal safety without tons of code.
In general, roles can be used also to group other rights, like built in role memberships. I wrote post about it some time ago:
http://usepubs.blogspot.com/2010/06/save-time-use-roles.html
Stuart, my apologies. When I tested this out I must have had my sessions switched. I went in to test it out again all prepared to yell Foo on you and instead realized the error of my ways. I was wrong. Even better: you were right.