Search This Blog

How to GRANT execute permission to all SPs in the database

For those , who are finding it difficult to GRANT permissions to the sps/fns, run the following, take the output from the resultpane and run it...

declare @sqlServerLogin varchar(100)
SET @sqlServerLogin = 'sqllogin'


SELECT N'GRANT EXEC ON ' +QUOTENAME(ROUTINE_SCHEMA) + N'.' +QUOTENAME(ROUTINE_NAME) +' TO ' +@sqlServerLogin +' '
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY( OBJECT_ID( QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0

1 comment:

Robby Jones said...

Many thanks for the script. As I know there are many other alternative ways for permissions granting.
You can use a tool like security explorer.
We have implemented this solution some time ago. It has a lot of intereseting and useful abilities for sql server security permissions administration that can replace standard scripting ways.
Permissions searching, modifying, granting and even cloning can be done in several clicks.