Setting permissions (Updated: UDF's now set)
Here is a script I wrote to automate updating of permission on a database. Often running as sa I forget to set the rights for objects when distributing db objects. It provides the capability to process only certain object types, and whether or not to print and execute the persmissions change or just print out the sql to run it later. It also eliminates itself from the permissions list as it would not be good to grant execute on this to [Public].
spSetPermissionsGlobally(@name nvarchar(128) = 'public',
@printonly bit = 1,
@revokeOldRights as bit = 1,
@processViews bit = 1,
@processProcs bit = 1,
@processTables bit = 0)
@name - name of the role / user to grant permissions to
@printonly - to print out the sql or print and execute it
@revokeOldRights - whether to revoke all the previous rights for this role
@processViews - Process views?
@processProcs - Process Stored Procs?
@processTables - Process Tables
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSetPermissionsGlobally]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSetPermissionsGlobally]
GO
CREATE PROCEDURE spSetPermissionsGlobally(@name nvarchar(128) = 'public',
@printonly bit = 0,
@revokeOldRights bit = 1,
@grantNewRights bit = 1,
@processViews bit = 1,
@processProcs bit = 1,
@processTables bit = 0, --normally tables are not exposed
@processFunctions bit = 1) AS
SET NOCOUNT ON
DECLARE @objname nvarchar(128),
@type char(2),
@sql varchar(200),
@sqlrevoke varchar(200),
@errors bit
SET @errors = 0
DECLARE permissions_cursor CURSOR FAST_FORWARD FOR --read only, fast forward tsql cursor
SELECT [name],
xtype
FROM SYSOBJECTS
WHERE OBJECTPROPERTY(id, N'IsMSShipped') = 0 -- <--ask for any object that did not come with SQL Server
AND [name] <> 'spSetPermissionsGlobally'
AND (
(@processViews = 1 AND OBJECTPROPERTY(id, N'IsView') = 1) -- <--Stored Procs, Tables, and Views, OH MY!
OR (@processProcs = 1 AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
OR (@processTables = 1 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
OR (@processFunctions = 1
AND (
OBJECTPROPERTY(id, N'IsScalarFunction') = 1
OR OBJECTPROPERTY(id, N'IsTableFunction') = 1
OR OBJECTPROPERTY(id, N'IsInlineFunction') = 1
)
)
)
ORDER BY xtype, -- <--makes it run slower, but easier to find items in the QA output window(of course there is CTRL+F :P )
[name]
OPEN permissions_cursor
FETCH NEXT FROM permissions_cursor
INTO @objname, @type
IF @printonly = 1
PRINT '--PRINTING ONLY!!'
PRINT ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @printonly = 0
PRINT '*****Setting permissions for : ' + @objname + '*****'
ELSE
PRINT 'PRINT ''*****Setting permissions for : ' + @objname + '*****'''
IF(@revokeOldRights = 1) --revoke the old rights?
BEGIN
SET @sqlrevoke = 'REVOKE ALL ON ' + @objname + ' TO ' + @name
IF @printonly = 1
BEGIN
PRINT @sqlrevoke
PRINT 'GO'
END
ELSE
BEGIN
PRINT @sqlrevoke
EXEC (@sqlrevoke)
END
END
IF @grantNewRights = 1 --grant the new rights?
BEGIN
SET @sql = NULL
IF(@type IN(N'V', N'TF')) -- VIEW, Table UDF
SET @sql = 'GRANT SELECT ON ' + @objname + ' TO ' + @name
IF(@type IN (N'P', N'FN')) -- STORED PROC, Scalar UDF
SET @sql = 'GRANT EXECUTE ON ' + @objname + ' TO ' + @name
IF(@type IN(N'U', N'IF')) -- TABLE, Inline UDF
SET @sql = 'GRANT SELECT, UPDATE, INSERT, DELETE ON ' + @objname + ' TO ' + @name
IF @printonly = 1
BEGIN
PRINT @sql
PRINT 'GO'
END
ELSE
BEGIN
PRINT @sql
EXEC (@sql)
END
END
IF @@ERROR <> 0
BEGIN
SET @errors = 1
BREAK --break outta loop if any errors
END
PRINT ''
FETCH NEXT FROM permissions_cursor
INTO @objname, @type
END
PRINT ''
IF @errors = 0
BEGIN
IF @printonly = 1
PRINT 'PRINT DONE'
ELSE
PRINT 'DONE'
END
ELSE
PRINT 'ERRORS OCCURRED.'
CLOSE permissions_cursor
DEALLOCATE permissions_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
