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

Rate

Share

Share

Rate