Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant Exec Rights on all Functions Expand / Collapse
Author
Message
Posted Saturday, October 3, 2009 1:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
Dear All,

How to provide exec rights to a specific user on all User & System functions?



Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #797370
Posted Saturday, October 3, 2009 5:36 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, September 15, 2014 8:57 AM
Points: 6,624, Visits: 1,872
You should always try to grant permissions to a role you've created and then put the appropriate users in that role. If you also want the role to have execute rights on all stored procedures as well, you can simply:

GRANT EXECUTE ON SCHEMA::dbo

Assuming all are in the dbo schema. If you only want functions, the best thing to do is use a query to build the permissions T-SQL for you:

SELECT 'GRANT EXECUTE ON [' + SCHEMA_NAME(schema_id) + '].[' + [name] + '] TO MyRole;'
FROM sys.objects
WHERE type = 'FN';

Then take the code you've generated and execute it.



K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #797466
Posted Sunday, October 4, 2009 11:32 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
K. Brian Kelley (10/3/2009)
You should always try to grant permissions to a role you've created and then put the appropriate users in that role. If you also want the role to have execute rights on all stored procedures as well, you can simply:

I know its a best practice, thanks. i'll do the same from now onwards.

and thanks for the help Sir [:)]


Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #797656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse