SET STATISTICS IO OFF SET STATISTICS TIME OFF SET NOCOUNT ON
DECLARE @UserString VARCHAR(8000) ,@DatabaseString VARCHAR(8000) ,@DefaultDatabase VARCHAR(255) ,@password VARCHAR(20) ,@RolesString VARCHAR(8000) ,@delimiter CHAR(1) ,@Environment VARCHAR(20) ,@Instance TINYINT ,@DatabaseRole VARCHAR(20) ,@intFlag INT ,@recordcount INT SET @intFlag = 1 SET @recordcount = 1 WHILE (@intFlag <=1)[email protected]) BEGIN
-- Change this section per user SET @UserString = (SELECT [User] FROM tmpdata.dbo.Security WHERE @intFlag = s_ID) SET @Environment = (SELECT @@SERVERNAME) SET @Instance = 1 SET @DatabaseRole = (SELECT DatabaseRole FROM tmpdata.dbo.Security WHERE @intFlag = s_ID)
IF @Environment = 'DEVELOPMENT' AND @Instance = 1 BEGIN SET @DatabaseString = 'ArchiveData,DevTrack,ESRD,HealthSQL,MedLex,MedScribeSQL,PatientData,POCForms,RobotSQL,TmpData,Treatment,TrigLogs,zCol,zHealthSQL' END
IF @Environment = 'DEVELOPMENT' AND @Instance = 2 BEGIN SET @DatabaseString = 'CKDWarehouse,CustAcct,DZMGmnt,DevTrack,EDIData_ghn7,edidata_old,edidata_sql3,GHNDictionary,GHNKBase,GHNLogs,GHNSchedule,ICD9,interfaces_sql3,LabData,LabDataMU,MGCPGSQL,RealmDB,RobotBM,RobotSQL,Rx,Tmpdata,TrigLogs' END
SET @DefaultDatabase = 'master'
/*Grant View Definition to user*/ DECLARE @SQL2 varchar(max) SET @SQL2 = 'USE ' + @DefaultDatabase + '; GRANT VIEW ANY DEFINITION TO ' + @UserString PRINT (@SQL2) EXECUTE (@SQL2)
IF @DatabaseRole IN ('QA','BA') BEGIN SET @RolesString = 'db_datareader' END
IF @DatabaseRole = 'Superuser' BEGIN SET @RolesString = 'db_datareader,db_datawriter,db_superuser' END IF @DatabaseRole = 'WebDev' BEGIN SET @RolesString = 'db_datareader,db_developer' END IF @DatabaseRole = 'TechSupport' BEGIN SET @RolesString = 'db_datareader,db_techsupport' END
SET @delimiter = ',' BEGIN TRY DROP TABLE #Users DROP TABLE #Databases DROP TABLE #Roles END TRY BEGIN CATCH
END CATCH
;WITH Substr(num, firstchar, lastchar) AS ( SELECT 1, 1, CHARINDEX(@delimiter, @UserString) UNION ALL SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @UserString, lastchar + 1) FROM Substr WHERE lastchar > 0 ) SELECT num, UserName = SUBSTRING(@UserString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END) INTO #Users FROM Substr ;WITH Substr(num, firstchar, lastchar) AS ( SELECT 1, 1, CHARINDEX(@delimiter, @DatabaseString) UNION ALL SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @DatabaseString, lastchar + 1) FROM Substr WHERE lastchar > 0 ) SELECT num, DatabaseName = SUBSTRING(@DatabaseString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END) INTO #Databases FROM Substr
;WITH Substr(num, firstchar, lastchar) AS ( SELECT 1, 1, CHARINDEX(@delimiter, @RolesString) UNION ALL SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @RolesString, lastchar + 1) FROM Substr WHERE lastchar > 0 ) SELECT num, RoleName = SUBSTRING(@RolesString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END) INTO #Roles FROM Substr
DECLARE @NumUsers int DECLARE @NumDBs int DECLARE @NumRoles int DECLARE @UserIter int DECLARE @DBIter int DECLARE @RoleIter int DECLARE @UserName varchar(255) DECLARE @RoleUserName varchar(255) DECLARE @DBName varchar(255) DECLARE @RoleName varchar(255) DECLARE @SQL varchar(max)
SET @NumUsers = (SELECT MAX(num) FROM #Users) SET @NumDBs = (SELECT MAX(num) FROM #Databases) SET @NumRoles = (SELECT MAX(num) FROM #Roles) SET @UserIter = 1 SET @SQL = ''
WHILE @UserIter <= @NumUsers BEGIN SET @DBIter = 1 SET @RoleIter = 1 SET @UserName = (SELECT UserName FROM #Users WHERE num = @UserIter)
SET @UserIter = @UserIter + 1 -- Add user to the databases WHILE @DBIter <= @NumDBs BEGIN SET @DBName = (SELECT DatabaseName FROM #Databases WHERE num = @DBIter) SET @SQL = 'USE ' + @DBName + '; DROP USER ' + @UserName + ' ;' PRINT (@SQL) EXECUTE (@SQL) SET @SQL = 'USE ' + @DBName + '; CREATE USER ' + @UserName + ' FOR LOGIN ' + @UserName
PRINT (@SQL) EXECUTE (@SQL) /* Grant Show Plan to WebDev, TechSupport and SuperUser*/ IF @DatabaseRole IN ('Superuser','WebDev','TechSupport') BEGIN SET @SQL = 'USE ' + @DBName + '; GRANT SHOWPLAN TO ' + @UserName + ' ;' PRINT (@SQL) EXECUTE (@SQL) END SET @DBIter = @DBIter + 1 SET @RoleIter = 1
WHILE @RoleIter <= @NumRoles BEGIN
SET @RoleName = (SELECT RoleName FROM #Roles WHERE num = @RoleIter)
-- Must remove brackets for addrolemember procedure
SET @RoleUserName = REPLACE(REPLACE(@UserName, '[', ''), ']', '') SET @SQL = 'USE ' + @DBName + '; EXEC sp_addrolemember ''' + @RoleName + ''', ''' + @RoleUserName + '''' EXECUTE (@SQL) IF @DBName = 'RobotSQL' AND @UserName IN ('CFAdmin','DrFirst','HL7Link') AND @RoleIter = 1 BEGIN SET @SQL = 'USE ' + @DBName + '; REVOKE EXECUTE ON SCHEMA ::TechSupp TO ' + @UserName EXECUTE (@SQL) END IF @DBName ='MGCPGSQL' AND @RoleName = 'db_developer' BEGIN PRINT '--No role for db_developer in MGCPGSQL.' END ELSE BEGIN PRINT (@SQL) END
SET @RoleIter = @RoleIter + 1 END END END
SET @intFlag = @intFlag + 1
END
|