GRANT Permissions on all of your Stored Procedures using a query

Permissions are an essential security feature, and you'll often create Roles and Users when you copy your Database live. Rather than using the permissions dialog boxes in Enterprise Manager you can write some T-SQL to perform the GRANTS in Query Analyzer. This is relatively simple process.

Use dbo.sysobjects to get a list of your Stored Procedures

SELECT name
FROM dbo.sysobjects
WHERE type = 'P' AND category = 0

In the WHERE clause setting type to 'P' will only return the Stored Procedures from dbo.sysobjects. Of these records those with a category value of 0 are user stored procedures, you'll notice that I have added this to the WHERE clause.

Don't be tempted to use a CURSOR to get to all your stored procs

CURSORs utilise loads of resource, they're slow, and this only increases as table size increases. Try and use a simple loop to iterate rows in a table, I normally drive this using a MIN() on Unique field, selecting the next lowest value in the table, this is much faster than a cursor. See below.

DECLARE @ID    integer,
       @LAST_ID integer,

--Initialise the variable
SET @LAST_ID = 0

--Loop until we've returned all the records
WHILE @LAST_ID IS NOT NULL
BEGIN

    -- Get next lowest value
    SELECT @ID = MIN(id)
    FROM dbo.sysobjects
    WHERE id > @LAST_ID  AND type = 'P' AND category = 0
    
    SET @LAST_ID = @ID
END

Once you've got your loop working, you'll want to grab the details of the stored procedure and perform the GRANT. On each iteration of the loop you can build a SQL string then execute it using xp_execresultset

DECLARE @DB  sysname ; set @DB = DB_NAME()
DECLARE @U  sysname ; set @U = QUOTENAME('<username>')

DECLARE @ID           integer,
        @LAST_ID     integer,
        @NAME        varchar(1000),
        @SQL         varchar(4000)

SET @LAST_ID = 0

WHILE @LAST_ID IS NOT NULL
BEGIN
    SELECT @ID = MIN(id)
    FROM dbo.sysobjects
    WHERE id > @LAST_ID  AND type = 'P' AND category = 0
    
    SET @LAST_ID = @ID
    
    -- We have a record so go get the name
    IF @ID IS NOT NULL
    BEGIN
        SELECT @NAME = name
        FROM dbo.sysobjects
        WHERE id = @ID
    
        -- Build the DCL to do the GRANT
        SET @SQL = 'GRANT EXECUTE ON ' + @NAME + ' TO ' + @U
        
        -- Run the SQL Statement you just generated
        EXEC master.dbo.xp_execresultset @SQL, @DB

    END
    

END

And that's it. Loop round the dbo.sysobjects table, get each stored procedure in turn, build a SQL string and execute it.

Using a standard naming convention for your stored procs will allow you to extend this further by picking up specific groups of procedures in the initial query so you can GRANT functionality to the correct user or role.

Author Paul Marshall

A self confessed Microsoft bigot, Paul loves all Microsoft products with a particular fondness for SQL Server. Paul is currently focusing on Web 2.0 patterns and practices and is always looking for better ways of doing things.

I love the .net platform, and I find it to be the most productive toolset I have used to date.

Comments

Anonymous said:

I usually do something like:

DECLARE @User varchar(50)

SELECT @User = '<a user or role>'

SELECT

        'GRANT EXEC ON ' + name + ' TO ' + @User

FROM

        sysobjects

WHERE

        type = 'P'

        AND category = 0

and i get a script that i can reuse

14/Jul/2006 15:14 PM

B said:

Thanks guys. Very Useful.

02/Mar/2007 16:04 PM

Anonymous said:

This sp do the same with a cursor and automatically adds the permissions for the user if someone is interested.

set ANSI_NULLS OFF

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE dbo.usp_ActualizarPermisos_USP

AS

declare @consulta as varchar(250)

declare @nombreSP as varchar(100)

declare @user as varchar(50)

SET @user = '<input your user here>'

DECLARE cursorSP CURSOR FOR

select name from sysobjects where xtype='P' AND category!=2

OPEN cursorSP

FETCH NEXT FROM CursorSP

INTO @nombreSP

WHILE @@FETCH_STATUS = 0

  BEGIN
     set @consulta = 'GRANT EXECUTE ON [dbo].[' + @nombreSP + '] TO [' + @user + ']'
     exec (@consulta)


  FETCH NEXT FROM CursorSP
  INTO @nombreSP

END

close CursorSP

Deallocate CursorSP

Hope this helps,

Me

07/Jun/2007 15:20 PM

krishna@sqlserver.in said:

Declare @sql nvarchar(1000)

Declare @dbname varchar(100)

Set @dbname = db_name()

Set @sql = 'select Grant Execute on name to [kris]

            from sysobjects where xtype in (''P'')'

Exec master.dbo.xp_execresultset sql,dbname

10/Jun/2008 21:31 PM

Laurasaur said:

Cheers very usefull

15/Oct/2008 23:34 PM

who said:

Very nice! How do I grant execute permission on all types of functions?

17/Mar/2009 07:46 AM

JB said:

Nice, thanks. Below, an extended version of the script, using evil cursors

USE XXX;

SET NOCOUNT ON;

-- _______________________________________________________________________________________________________________________________________

-- ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

-- JB 2011 : SQL Server helper script to grants access role grants to objects

-- 0=exclude object having all permission already, 1=execute grant statements whether the permission is already set or not

DECLARE force_grant bit; SELECT force_grant = 0;

-- 1 : Preview mode - print SQL commands w/o executing them

-- 0 : execute SQL commands

DECLARE preview bit; SELECT preview = 1;

-- Preview mode settings :

DECLARE pvw_script_ready bit; SELECT pvw_script_ready = 1; -- 1:the output is a valid SQL statement, 0:prints out more informations to debug

DECLARE pvw_print_skipped bit; SELECT pvw_print_skipped = 0;

-- ---------------------------------------------------------------------------------------------------------------------------------------

DECLARE @tmp_role TABLE (role nvarchar(50), uspGrant nvarchar(50), viewGrant nvarchar(50), tableGrant nvarchar(50), funcGrant nvarchar(50) )

-- ---------------------------------------------------------------------------------------------------------------------------------------

-- | role | proc | views | tables | function

-- ---------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO @tmp_role select 'MY_ADMIN_ROLE' ,'EXECUTE' ,'SELECT' ,'INSERT,UPDATE,DELETE,SELECT' ,'EXECUTE'

INSERT INTO @tmp_role select 'MY_SELECT_ROLE' ,'EXECUTE' ,'SELECT' ,'SELECT' ,'EXECUTE'

-- _______________________________________________________________________________________________________________________________________

-- ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

SET NOCOUNT OFF;

DECLARE

        @LB nvarchar(2) -- Line Break
        , @prevRole nvarchar(50)
        , @role nvarchar(50)
        , @permTarget varchar(10)
        , @objXType char(2)
        , @objName nvarchar(128) -- declared sysname(128) IN dbo.sysobjects
        , @permName nvarchar(50)
        , @hasAllPerms bit
        , @count int
        , @skippedCount int
        , @sql nvarchar(256)

;

SELECT

        @LB = CHAR(13) + CHAR(10)
        , @prevRole = ''

;

IF (preview = 1) PRINT '-- -----------------------------------------------------------------------------' LB '-- Preview ENABLED : no statement is executed.' @LB ' ';

DECLARE curObj CURSOR FOR

        SELECT
                perm.role
                , perm.target
                , perm.name
                , so.xtype
                , so.name
                , CASE WHEN EXISTS(
                        SELECT 1 FROM dbo.syspermissions
                        WHERE grantee = user_id(perm.role) AND id = so.id
                                AND (
                                        (CHARINDEX('SELECT', perm.name) = 0 OR (actadd & 1) <> 0)
                                        AND (CHARINDEX('UPDATE', perm.name) = 0 OR (actadd & 2) <> 0)
                                        AND (CHARINDEX('INSERT', perm.name) = 0 OR (actadd & 8) <> 0)
                                        AND (CHARINDEX('DELETE', perm.name) = 0 OR (actadd & 16) <> 0)
                                        AND (CHARINDEX('EXECUTE', perm.name) = 0 OR (actadd & 32) <> 0) -- TODO: add special case for Table Function if EXECUTE is specified for function
                                )
                ) THEN 1 ELSE 0 END -- 1 if it has ALL the specified premission already, else 0
        FROM
                dbo.sysobjects AS so
                INNER JOIN
                (
                        SELECT role, 'proc' AS target, uspGrant AS name FROM @tmp_role
                        UNION SELECT role, 'view', viewGrant FROM @tmp_role
                        UNION SELECT role, 'table', tableGrant FROM @tmp_role
                        UNION SELECT role, 'func', funcGrant FROM @tmp_role
                ) perm
                        ON 1=1
                        AND perm.role IS NOT NULL
                        AND perm.name IS NOT NULL
        WHERE
                (
                        perm.target = 'proc' AND so.xtype = 'P'
                        AND NOT so.name LIKE 'dt_%' -- excludes system procedures
                        AND NOT so.name LIKE 'sp_MS%'
                )
                OR (
                        perm.target = 'view' AND so.xtype = 'V'
                        AND NOT so.name LIKE 'sys%' -- excludes system views
                )
                OR (
                        perm.target = 'table' AND so.xtype = 'U'
                        AND so.name NOT LIKE 'dt%' -- excludes dbo.dtproperties
                        AND so.name NOT LIKE 'MS%' -- skip dbo.MS*
                )
                OR (
                        perm.target = 'func' AND so.xtype IN (N'FN', N'IF', N'TF')
                )
        ORDER BY
                perm.role
                , perm.target
                , so.xtype
                , so.name

;

OPEN curObj;

WHILE(1=1)

BEGIN

        FETCH NEXT FROM curObj INTO @role, @permTarget, @permName, @objXType, @objName, @hasAllPerms;
        IF (@@fetch_status <> 0)
        BEGIN
                IF @count IS NOT NULL PRINT '-- ' + CONVERT(varchar, ISNULL(@skippedCount,0)) + '/' + CONVERT(varchar, @count) + ' skipped';
                BREAK;
        END


        IF @prevRole <> @role
        BEGIN
                IF @count IS NOT NULL PRINT '-- ' + CONVERT(varchar, ISNULL(@skippedCount,0)) + '/' + CONVERT(varchar, @count) + ' skipped';
                PRINT @LB + '-- -------------------------------------------------------------------------------' + @LB + '-- Grants to ' + @role + @LB + ' ';
                SELECT @skippedCount = 0, @count = 0;
        END


        SET @count = @count + 1;


        IF (@hasAllPerms = 0 OR @force_grant = 1)
        BEGIN
                IF @permTarget = 'func' -- User Defined Function, Table Function, ...
                BEGIN
                        IF (@objXType = 'TF' AND @permName = 'EXECUTE') SET @permName = 'SELECT'; -- uses 'SELECT' for Table-Function
                        SET @permTarget = @permTarget + ' ' + ISNULL(@objXType,'');
                END


                SET @sql = 'GRANT ' + @permName + ' ON dbo.' + @objName + '        TO ' + @role;
                IF @preview = 0
                BEGIN
                        PRINT @permTarget + '        ' + @sql + ' :';
                        EXEC(@sql);
                END
                ELSE IF @pvw_script_ready = 0
                        PRINT @permTarget + '        ' + @sql ;
                ELSE -- preview, script ready 
                        PRINT @sql + ';';
        END
        ELSE
        BEGIN
                SET @skippedCount = @skippedCount + 1;
                IF (@preview = 1 AND @pvw_print_skipped = 1)
                        PRINT '-- ' + @permTarget + '        already granted to ' + @permName + ' on ' + @objName + ' : skipped';
        END


        SET @prevRole = @role;

END

CLOSE curObj;

DEALLOCATE curObj;

27/Oct/2011 14:53 PM

Add Comment

Name
Comment
 

Your comment has been received and will be shown once it passes moderation.