DBUser Roles Script

--select * from master.DBO.TEMP_TABLE_FOR_USERS
DECLARE @DBNAME VARCHAR(50),
		@STORAGEDBNAME VARCHAR(50)
SET @DBNAME='ReportingDatabase'
SET @STORAGEDBNAME='master'

SET NOCOUNT ON
DECLARE @CMD VARCHAR(350)
SET @CMD=	'IF  EXISTS (SELECT * FROM '+@STORAGEDBNAME+'.sys.objects WHERE object_id = OBJECT_ID('''+@STORAGEDBNAME+'.[dbo].[TEMP_TABLE_FOR_USERS]'')
			AND type in (''U''))
			DROP TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
			CREATE TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
			([COMMAND] TEXT)'
			
EXEC (@CMD)
CREATE TABLE #TEMP
	(   NUMBER INT IDENTITY(1,1) NOT NULL, 
		USERNAME VARCHAR(100),
		ROLENAME VARCHAR(100)
	)
CREATE TABLE #TEMP1
	(   NUMBER INT IDENTITY(1,1) NOT NULL, 
		OBJECTNAME VARCHAR(100),
		TYPE_DESC VARCHAR(100),
		PERMISSION_NAME VARCHAR(100),
		STATE_DESC VARCHAR(100),
		USERNAME VARCHAR(100)
	)
CREATE TABLE #TEMP2
	(   NUMBER INT IDENTITY(1,1) NOT NULL, 
		USERNAME VARCHAR(100)
		)

DECLARE @CMD1 VARCHAR(500),
@USER INT,
@USERNAME VARCHAR(50)

SET @CMD1=	'SELECT U.NAME , G.NAME 
            FROM '+ @DBNAME+'.DBO.SYSUSERS U,' +@DBNAME+'.DBO.SYSUSERS G,' +@DBNAME+'.DBO.SYSMEMBERS M
            WHERE   G.UID = M.GROUPUID
            AND G.ISSQLROLE = 1 AND U.UID = M.MEMBERUID AND U.NAME<>''dbo'''
INSERT INTO #TEMP
EXEC (@CMD1)

INSERT INTO #TEMP2
SELECT DISTINCT USERNAME FROM #TEMP
DECLARE @CMD2 VARCHAR(5000)
SET @CMD2=		'DECLARE @COUNT INT,@USER INT
					SET @COUNT=1 SELECT @USER=COUNT(*) FROM #TEMP2 WHILE @USER>=@COUNT
					BEGIN
						INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
						SELECT ''IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ''''''+USERNAME+'''''')
						CREATE USER [''+USERNAME+''] FOR LOGIN ['' +USERNAME +''] WITH DEFAULT_SCHEMA=[DBO]'' FROM #TEMP2
						WHERE NUMBER=@COUNT SET @COUNT=@COUNT+1
					END'
EXEC (@CMD2)
-------------------- Schema Starts Here ------------------

CREATE TABLE #TEMP3
	(   NUMBER INT IDENTITY(1,1) NOT NULL, 
		SCHEMANAME VARCHAR(100)
	)
INSERT INTO #TEMP3 SELECT NAME FROM SYS.SCHEMAS WHERE [SCHEMA_ID] BETWEEN 5 AND 16383


DECLARE @CMD5 VARCHAR(5000)
SET @CMD5=	'DECLARE @SCHEMA INT,
					@COUNT3 INT
			SET @COUNT3=1
			SELECT @SCHEMA=COUNT(SCHEMANAME) FROM #TEMP3
			WHILE @SCHEMA>=@COUNT3
				BEGIN
				INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
						SELECT ''IF NOT EXISTS (SELECT * FROM SYS.SCHEMAS WHERE NAME = ''''''+SCHEMANAME+'''''')
						EXEC SYS.SP_EXECUTESQL N''''CREATE SCHEMA [''+SCHEMANAME+''] AUTHORIZATION ['' +SCHEMANAME +'']'''''' FROM #TEMP3
						WHERE NUMBER=@COUNT3 SET @COUNT3=@COUNT3+1
				END'
					
					
EXEC (@CMD5)

-------------------- User Role Starts Here ---------------
DECLARE @CMD3 VARCHAR(5000)
SET @CMD3=	'DECLARE @ROLE INT,
					@COUNT2 INT
			SET @COUNT2=1
			SELECT @ROLE=COUNT(ROLENAME) FROM #TEMP
			WHILE @ROLE>=@COUNT2
				BEGIN
					INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''EXEC sp_addrolemember ''''''+ROLENAME+ '''''' ,''''''+ USERNAME+'''''''' FROM #TEMP 
					WHERE NUMBER=@COUNT2 SET @COUNT2=@COUNT2+1 
				END'
	
EXEC (@CMD3)

--------------------- Secure Permission Starts Here ---------------
INSERT INTO #TEMP1
SELECT O.NAME COLLATE LATIN1_GENERAL_CI_AS_KS_WS AS OBJECTNAME ,TYPE_DESC,
PERMISSION_NAME,STATE_DESC,U.NAME AS USERNAME
FROM SYS.DATABASE_PERMISSIONS P
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID=MAJOR_ID
INNER JOIN SYSUSERS U ON U.UID=P.GRANTEE_PRINCIPAL_ID

DECLARE @CMD4 VARCHAR(5000)
SET @CMD4=	'DECLARE @SECUR INT,
					@COUNT1 INT
			SET @COUNT1=1
			SELECT @SECUR=COUNT(*) FROM #TEMP1
			WHILE @SECUR>=@COUNT1
				BEGIN
					INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''''+STATE_DESC+'' ''+PERMISSION_NAME+'' ON ''+OBJECTNAME+''  TO [''+USERNAME +'']''FROM #TEMP1 
					WHERE NUMBER=@COUNT1 SET @COUNT1=@COUNT1+1
				END'
EXEC (@CMD4)

-------------------- Final Output ---------------
DROP TABLE #TEMP
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #TEMP3
SET NOCOUNT OFF