November 19, 2012 at 7:06 am
How do I grant (in the quickest simplest way) execute on all stored proc's in a database? I have a few to do this to, but not all on a particular server.
November 19, 2012 at 7:09 am
Create a role if not already then assign the execute permission in the database context then add the users needed to the role is the way I would recommend doing it.
CREATE ROLE ExecuteAllDB
GO
GRANT EXECUTE TO ExecuteAllDB
GO
sp_addrolemember 'ExecuteAllDB','Username'
GO
November 19, 2012 at 7:10 am
Any way to do it without creating roles?
November 19, 2012 at 7:11 am
Just grant execute to the user.
Although the prefered way of doing permissions is by roles as it makes life easier to manage.
November 19, 2012 at 3:29 pm
ahthomas (11/19/2012)
How do I grant (in the quickest simplest way) execute on all stored proc's in a database? I have a few to do this to, but not all on a particular server.
It depends.
Do you just want to grant it on existing procs only?
Or do you want them to be able to exec new procs as well?
Easiest is indeed to use a role, although I would limit most users to only "dbo." procedures (or whatever schema you have your custom stored procs in), rather than potentially opening up sys.* procs in the db as well:
GRANT EXECUTE ON SCHEMA::dbo TO <role_name>|<user_name>
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply