GeekZilla
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.
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
B
said:
Thanks guys. Very Useful.
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
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
Laurasaur
said:
Cheers very usefull 
who
said:
Very nice! How do I grant execute permission on all types of functions?
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;
