--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
Ads
Showing posts with label script out logins and permissions. Show all posts
Showing posts with label script out logins and permissions. Show all posts
Subscribe to:
Posts (Atom)
-
ERROR: report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the o...
-
Error messages: · The process could not execute 'sp_repldone/sp_replcounters' on ' SCKNLSSI '. (Source:...
-
Error Message: Replication-Replication Distribution Subsystem: agent DBA\DBA-EPDW-EPDW-DB6C\DB6C-5 failed. The subscription(S) have been...