Grant Rights To All Routines (STORED PROCEDURES AND FUNCTIONS)

It is seldom a good idea to grant dbo rights to your users so here is an easy way to control who can run stored procedures and functions and who can view them.

Essentially the steps are:

1. Create new database roles called sp_runner and sp_viewer.. these are essentially hollow roles as they cannot do anything at this point.
2. Grant the role sp_runner the right to run, and sp_view the right to view (but not change) the stored procedures on the current database
3. Through SSMS or a script you can then assign those roles to database users.

The stored procedure below handles steps 1 and 2 and all you need to do is:
exec spGrantRightsToAllRoutines

We use this to allow:

• Our web app to run stored procedures. We like to grant the account that runs the web app the minimal rights to do its job.
• Our developers to view stored procedures on non-development databases. We like to have tight control over the change process but it is helpful for them to see the routines.

Hope that helps
Jon Bosker
www.dbgurus.com.au
-----------------------------------------------------------------------------------------
-- Refresh or create the SP spGrantRightsToAllRoutines:
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spGrantRightsToAllRoutines')
DROP PROCEDURE spGrantRightsToAllRoutines
GO

CREATE PROCEDURE [dbo].[spGrantRightsToAllRoutines]
AS
/*
Stored procedure to create 2 new database roles sp_viewer and sp_runner
and grant rights rights to those roles. After that all you need to do is
give those roles to the database users who need them

exec spGrantRightsToAllRoutines
*/

BEGIN
IF DATABASE_PRINCIPAL_ID('sp_runner') IS NULL
CREATE ROLE [sp_runner]
IF DATABASE_PRINCIPAL_ID('sp_viewer') IS NULL
CREATE ROLE [sp_viewer]

DECLARE @t TABLE (Name varchar(8000), id int identity(1,1) )

INSERT INTO @t
SELECT SPECIFIC_SCHEMA  + '.[' + ROUTINE_NAME + ']' FROM INFORMATION_SCHEMA.ROUTINES
WHERE NOT (ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE = 'TABLE') -- this one causes an error

DECLARE @sSQL varchar(8000), @counter int
SET @counter = 1

WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL='
GRANT EXEC ON ' + [Name] + ' TO sp_runner;
GRANT VIEW DEFINITION ON ' + [Name] + ' TO sp_viewer;'
 FROM @t WHERE id = @counter
PRINT @sSQL
  EXEC (@sSQL)  -- comment out this line to test it

SET @counter = @counter + 1
END
END

GO

exec spGrantRightsToAllRoutines