February 7, 2012 at 3:06 pm
I would like to grant a user permission to excute all the stored procedures in the database.
Is there a way to that?
February 7, 2012 at 3:10 pm
use [DBName]
GO
GRANT EXECUTE TO [Domain\user]
GO
This would grant execute access to functions as well as stored prcoedures
February 7, 2012 at 3:12 pm
Great, thank you.
If I do this, is there a way later I can check from management studio, this user has been granted execute ,
Thanks
February 7, 2012 at 3:19 pm
Yes. Navigate to Securables (right click user properties under security node) for the user (not login) and select database.
You will see all the database level securables you can set for the user.
February 7, 2012 at 3:22 pm
Another way to do this would be to create a database role and give it execute access. That way you can assign users to this database role
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
February 7, 2012 at 3:29 pm
This will let you know what the perms are. And as an added bonus, I am leaving in the script portion that can help recreate the perms. I don't recall where I found it, but it is of somebody else's making 😉
SELECTCASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';' AS '--Object Level Permissions'
FROMsys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 3:32 pm
I know how I can set up this in SSMS. But my question is when I use SQL grant execute to that user, how can I check in SSMS if the user is granted to execute functions or sprocs?
I went to check at the plaace you told me, at the user - securables, then I pull up a function, but I ddin't see she is granted execute permission.
February 7, 2012 at 3:44 pm
If you grant execute at the database level you will not see it being set under securables for each object (sp or function), you will only see it set under securables for the database.
You probably know this but this is how you check it for the database..
1)Right Click the User and Select Properties
2)Select Securables
3)Click Search button
4)Select the default radio button option which is Specific objects and click OK
5)Click Object Types
6)Select Databases Check box and click OK
7)Click Browse and Select the Check box of the database you gave the user permission
8)Click Ok
9)Under Permisisions for the Database you will see a bunch of securables you can set for this user. If you granted execute permissions for the user you will the Execute checkbox selected.
February 7, 2012 at 3:50 pm
sqlfriends (2/7/2012)
I know how I can set up this in SSMS. But my question is when I use SQL grant execute to that user, how can I check in SSMS if the user is granted to execute functions or sprocs?I went to check at the plaace you told me, at the user - securables, then I pull up a function, but I ddin't see she is granted execute permission.
Functions have a different permission set. Run the code I provided and it will show the permissions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 3:50 pm
Thanks, I found it. You are correct, I should look under securables for databases. I see excute is granted.
Thanks Jason for the script, I will check into it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply