May 11, 2021 at 9:55 am
Hi
Could anyone please help to figure out which roles/rights needed in SQL server so that user can write/Run procedures/functions.
I gave db_datareader + db_datawriter + ddladmin roles to user but they can't execute procedures/functions.
See the screenshots of rights/ roles given to user on the database where he needs to execute procedure/functions.
Regards
May 11, 2021 at 10:00 am
There is no built in role for execute permissions.
You will need to create a custom role to do this.
Lookup the syntax for CREATE ROLE and GRANT EXECUTE and post back if you have any difficulties
May 11, 2021 at 10:24 am
Hi
Thanks for your reply.
Does that mean that I need to run below grant execute SP SQL Script for each SP for user?
GRANT create procedure ON DATABASE::[db name] TO [user_name]
GRANT execute ON SP_Name TO [User_Name]
Regards
May 11, 2021 at 10:58 am
Well it all depends what you want to do.
If you want to do it grant at the whole database level like you have already granted db_datareader etc, then you create a role and grant execute on the database, then assign the user to the role.
CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
ALTER ROLE db_executor ADD MEMBER <myuser>
Or if you want to be doing it correctly and only granting the permission when you need it instead of a generic "ALL EXECUTE".
Then you create a role and grant execute to the specific procedures at creation time.
CREATE ROLE ProcExecute
GRANT EXECUTE ON <ProcName> TO ProcExecute
ALTER ROLE ProcExecute ADD MEMBER <myuser>
May 11, 2021 at 11:34 am
Generally, I use a combination of Active Directory groups (assuming Windows), and roles to define security.
As shown above, you can grant specific actions to a role. So, do that. Least privilege principal in mind, of course. Then, you can create AD groups. Add the group to the role (as defined above). Then, never worry about managing database access ever again. All you have to do is work with the AD group, adding/removing users there does everything else you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2021 at 2:53 pm
Hi
Thanks for your reply.
Does that mean that I need to run below grant execute SP SQL Script for each SP for user?
GRANT create procedure ON DATABASE::[db name] TO [user_name]
GRANT execute ON SP_Name TO [User_Name]
Regards
No. If you want to let them exec any proc or function, even new ones that get created in the future, give them exec rights on the schema, like so:
GRANT EXECUTE ON SCHEMA::dbo TO [user_name]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply