usp_SecurCreation - SQL 2k

,

Creates statements for Procedures/Functions, Table and Column Level Privileges creation to a specific user. Useful when migrating a server, for example.

Usage:

To script all users securables: EXEC usp_SecurCreation
To script one user securables: EXEC usp_SecurCreation '<User>'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_SecurCreation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_SecurCreation]
GO

CREATE PROCEDURE usp_SecurCreation @User NVARCHAR(128) = NULL
AS
--
--  2008-02-25 Pedro Lopes (NovaBase) pedro.lopes@novabase.pt
--
--  All users: EXEC usp_SecurCreation
--
--  One user: EXEC usp_SecurCreation '<User>'
--

SET NOCOUNT ON

DECLARE @SC VARCHAR(4000), @SCUser VARCHAR(4000)	
CREATE TABLE #TempSecurables2 ([DBName] sysname,
					[State] VARCHAR(1000)
					)	

IF @User IS NULL
BEGIN
	--Server level Privileges to User or User Group
	INSERT INTO #TempSecurables2
	EXEC master.dbo.sp_MSforeachdb @command1='USE [?] 
	--Privileges for Procedures/Functions to the User
	SELECT ''[?]'', CASE c.protecttype WHEN 206 THEN ''DENY'' ELSE ''GRANT'' END + '' EXECUTE ON ['' + d.name 
	+ ''].['' + a.name + ''] TO ['' + e.name + CASE c.protecttype WHEN 204 THEN ''] WITH GRANT OPTION'' ELSE '']'' END 
	FROM dbo.sysobjects a INNER JOIN dbo.sysprotects c ON a.id = c.id INNER JOIN dbo.syspermissions b ON a.id = b.id
	LEFT OUTER JOIN dbo.sysusers d ON a.uid = d.uid LEFT OUTER JOIN dbo.sysusers e ON c.uid = e.uid
	WHERE a.type IN (''X'',''P'',''FN'') AND b.id <> 0 AND b.grantee <>0 AND b.grantee <>2
	ORDER BY a.name

	--Table Level Privileges to the User
	SELECT ''[?]'', ''GRANT '' + PRIVILEGE_TYPE + '' ON ['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + ''] TO ['' + GRANTEE + '']'' +
	CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' ELSE '''' END
	FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
	WHERE GRANTEE <> ''public''

	--Column Level Privileges to the User 
	SELECT ''[?]'', ''GRANT '' + PRIVILEGE_TYPE + '' ON ['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + ''] ('' + COLUMN_NAME + '') TO ['' + GRANTEE + '']'' +
	CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
	ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
	WHERE GRANTEE <> ''public'''
END
ELSE
BEGIN
	--Server level Privileges to User or User Group
	SET @SCUser = 'USE [?] 
	--Privileges for Procedures/Functions to the User
	SELECT ''[?]'', CASE c.protecttype WHEN 206 THEN ''DENY'' ELSE ''GRANT'' END + '' EXECUTE ON ['' + d.name 
	+ ''].['' + a.name + ''] TO ['' + e.name + CASE c.protecttype WHEN 204 THEN ''] WITH GRANT OPTION'' ELSE '']'' END 
	FROM dbo.sysobjects a INNER JOIN dbo.sysprotects c ON a.id = c.id INNER JOIN dbo.syspermissions b ON a.id = b.id
	LEFT OUTER JOIN dbo.sysusers d ON a.uid = d.uid LEFT OUTER JOIN dbo.sysusers e ON c.uid = e.uid
	WHERE a.type IN (''X'',''P'',''FN'') AND b.id <> 0 AND b.grantee <>0 AND b.grantee <>2
	AND QUOTENAME(e.name) = ''[' + @User + ']''
	ORDER BY a.name

	--Table Level Privileges to the User
	SELECT ''[?]'', ''GRANT '' + PRIVILEGE_TYPE + '' ON ['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + ''] TO ['' + GRANTEE + '']'' +
	CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' ELSE '''' END
	FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
	WHERE GRANTEE <> ''public''
	AND GRANTEE = ''[' + @User + ']''

	--Column Level Privileges to the User 
	SELECT ''[?]'', ''GRANT '' + PRIVILEGE_TYPE + '' ON ['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + ''] ('' + COLUMN_NAME + '') TO ['' + GRANTEE + '']'' +
	CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
	ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
	WHERE GRANTEE <> ''public''
	AND GRANTEE = ''[' + @User + ']'''

	INSERT INTO #TempSecurables2
	EXEC master.dbo.sp_MSforeachdb @command1=@SCUser
END

DECLARE @tmpstr NVARCHAR(128)
SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr

PRINT '--##### Procedures/Functions, Table and Column Level Privileges to the User #####'

DECLARE cSC CURSOR FOR SELECT 'USE ' + ts2.DBName +';' + CHAR(10) + RTRIM(ts2.[State]) + ';' + CHAR(10) + 'GO' FROM #TempSecurables2 ts2
OPEN cSC  
FETCH NEXT FROM cSC INTO @SC
WHILE @@FETCH_STATUS = 0 
	BEGIN 
		PRINT @SC
		FETCH NEXT FROM cSC INTO @SC
	END
CLOSE cSC 
DEALLOCATE cSC

DROP TABLE #TempSecurables2
GO

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)