Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating