usp_SecurCreation - SQL 2k

, 2008-06-26 (first published: )

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)

Related content

Worst Practice - Bad Connection Strings and Bad Info in Sysprocesses

Andy returns to the Worst Practice series this week with a short article looking at how connection strings in applications affect what you see in sysprocesses. Perhaps less controversial (in our opinion) that some of the other worst practices, this is something easy to fix and definitely worth fixing! Read the article and post a comment - explore other points of view! Readers posting a comment will be entered in a drawing for a copy of the SQL Server 2000 Resource Kit.

2003-08-01

15,125 reads

Logins, Users, and Roles - Getting Started

Do you know the difference between a login and a user? What's the best way to add them; Enterprise Manager, T-SQL, or SQL-DMO? In this beginner level article Andy demonstrates how to use all three methods to add logins and users and offers his view of which is the best technique.

2005-09-30 (first published: )

35,340 reads

The Case for SQL Logins - Part 1

Andy says Windows Authentication "is bad". What? That's not what Microsoft says! Heck, that's not even what we say! Everyone knows NT authentication is the way to go. Then again, when was the last time Andy wrote an article that wasn't worth reading?! Read the article, rate it and add a comment - and automatically be entered in a drawing for a copy of SQL Server 2000 Performance Tuning donated by Microsoft Press.

2 (1)

2002-06-25

12,610 reads