RE: Server Login/Role And DB User/Role Script

sunshine-587009

SSCrazy Eights

Points: 8981

March 21, 2011 at 12:23 pm

#1301476

I replaced all the cursors named error with one called login but still not sure how this is supposed to work"

/****************************************

Script Made by Lester A. Policarpio

For questions and clarifications feel free to email me at

lpolicarpio2005@yahooo.com

SET NOCOUNT ON

print '--##############################################################'

print '--Generate Script for Server Log-ins'

print '--Supply with your Server log-in password'

print '--##############################################################'

print ''

IF EXISTS (SELECT name,dbname FROM master..syslogins WHERE name not LIKE '%BUILTIN%' AND name <> 'sa' AND name not like '%administrator%')

BEGIN

DECLARE @name varchar(200)

DECLARE @default varchar(200)

print 'DECLARE @login varchar(1024)'

print 'DECLARE @q varchar(1024)'

print ''

print 'CREATE TABLE login'

print '('

print 'names varchar(124),'

print 'pass varchar(124),'

print 'db varchar(124),'

print ')'

--GENERATE SERVER LOG-IN

DECLARE login CURSOR FOR

SELECT name,dbname FROM master..syslogins WHERE name not LIKE '%BUILTIN%' AND name <> 'sa' AND name not like '%administrator%'

OPEN login

FETCH NEXT FROM login INTO @name,@default

WHILE (@@FETCH_STATUS = 0)

BEGIN

print 'INSERT INTO login VALUES ('+''''+@name+''''+','+'''[password_here]'''+','+''''+@default+''''+')'

FETCH NEXT FROM login INTO @name,@default

END

CLOSE login

DEALLOCATE login

print 'DECLARE logins CURSOR FOR'

print 'select '+''''''''''+'+names+'+''''''''''+'+'','''+'+'+''''''''''+'+pass+'+''''''''''+'+'','''+'+'+''''''''''+'+db+'+''''''''''+ ' AS '+'''LOG'''+ ' FROM login'

print 'OPEN logins'

print 'FETCH NEXT FROM logins INTO @login'

print 'WHILE (@@FETCH_STATUS = 0)'

print 'BEGIN'

print 'SET @q = ''sp_addlogin ''+@login'

print 'EXEC (@q)'

print 'FETCH NEXT FROM logins INTO @login'

print 'END'

print 'CLOSE logins'

print 'DEALLOCATE logins'

print 'DROP TABLE login'

END

print '--#################################################################'

print '--Generate Script for Server Roles'

print '--#################################################################'

print ''

IF EXISTS (select name,dbname,sysadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator,bulkadmin

FROM master..syslogins WHERE name not LIKE '%BUILTIN%' AND name <> 'sa' AND name not like '%administrator%')

BEGIN

DECLARE @logins varchar(200)

DECLARE @Default1 varchar(200)

DECLARE @sysadmin int

DECLARE @securityadmin int

DECLARE @serveradmin int

DECLARE @setupadmin int

DECLARE @processadmin int

DECLARE @diskadmin int

DECLARE @dbcreator int

DECLARE @bulkadmin int

DECLARE @master int

print 'DECLARE @login2 varchar(1024)'

print 'DECLARE @w varchar(1024)'

print ''

print 'CREATE TABLE login2'

print '('

print 'names varchar(1024),'

print 'role varchar(3000)'

print ')'

print ''

DECLARE login CURSOR FOR

select name,dbname,sysadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator,bulkadmin

FROM master..syslogins WHERE name not LIKE '%BUILTIN%' AND name <> 'sa' AND name not like '%administrator%'

OPEN login

FETCH NEXT FROM login INTO @logins,@default1,@sysadmin,@securityadmin,@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin

WHILE (@@FETCH_STATUS = 0)

BEGIN

--@@@@@@ sysadmin

IF (@sysadmin = 1)

BEGIN

print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''sysadmin'''+')'

print ''

END

--@@@@@ securityadmin

IF (@securityadmin = 1)

BEGIN

print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''securityadmin'''+')'

print ''

END

--@@@@@ serveradmin

IF (@serveradmin = 1)

BEGIN

print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''serveradmin'''+')'

print ''

END

--@@@@@ setupadmin

IF (@setupadmin = 1)

BEGIN

print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''setupadmin'''+')'

print ''

END

--@@@@@ processadmin

IF (@processadmin = 1)

BEGIN

print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''processadmin'''+')'

print ''

END

--@@@@@ diskadmin

IF (@diskadmin = 1)

BEGIN

print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''diskadmin'''+')'

print ''

END

--@@@@@ dbcreator

IF (@dbcreator = 1)

BEGIN

print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''dbcreator'''+')'

print ''

END

--@@@@@ bulkadmin

IF (@bulkadmin = 1)

BEGIN

print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''bulkadmin'''+')'

print ''

END

FETCH NEXT FROM login INTO @logins,@default1,@sysadmin,@securityadmin,@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin

END

CLOSE login

DEALLOCATE login

print 'DECLARE logins CURSOR FOR'

print 'select names + '+''','''+'+ role FROM login2'

print 'OPEN logins'

print 'FETCH NEXT FROM logins INTO @login2'

print 'WHILE (@@FETCH_STATUS = 0)'

print 'BEGIN'

print 'SET @w = ''sp_addsrvrolemember ''+@login2'

print 'EXEC (@w)'

print 'FETCH NEXT FROM logins INTO @login2'

print 'END'

print 'CLOSE logins'

print 'DEALLOCATE logins'

print 'DROP TABLE login2'

END

print '--#################################################################'

print '--Generate Script for Database Users/ROles'

print '--#################################################################'

print ''

/****************************************

Script Made by Lester A. Policarpio

For questions and clarifications feel free to email me at

lpolicarpio2005@yahooo.com

DECLARE @dbcomp varchar(1024)

DECLARE @pass varchar(5000)

DECLARE @counter varchar(500)

DECLARE @dbid varchar(100)

CREATE TABLE DBROLES

( DBName sysname not null,

UserName sysname not null,

db_owner varchar(3) not null,

db_accessadmin varchar(3) not null,

db_securityadmin varchar(3) not null,

db_ddladmin varchar(3) not null,

db_datareader varchar(3) not null,

db_datawriter varchar(3) not null,

db_denydatareader varchar(3) not null,

db_denydatawriter varchar(3) not null,

db_backupoperator varchar(3) not null

)

declare @dbname varchar(200)

declare @mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR

select name

from master.dbo.sysdatabases

where name not in ('master','tempdb','model','pubs','northwind','DBA','msdb')

Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

Set @mSQL1 = ' Insert into DBROLES ( DBName, UserName, db_owner, db_accessadmin,

db_securityadmin, db_ddladmin, db_datareader, db_datawriter,

db_denydatareader, db_denydatawriter,db_backupoperator )

SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '

Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,

Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,

Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,

Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,

Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,

Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,

Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,

Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter,

Max(CASE RoleName WHEN ''db_backupoperator'' THEN ''Yes'' ELSE ''No'' END) AS db_backupoperator

from (

select b.name as USERName, c.name as RoleName

from ' + @dbName+'.dbo.sysmembers a '+char(13)+

' join '+ @dbName+'.dbo.sysusers b '+char(13)+

' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c

on a.groupuid = c.uid )s

Group by USERName

order by UserName'

--Print @mSql1

Execute (@mSql1)

FETCH NEXT FROM DBName_Cursor INTO @dbname

END

CLOSE DBName_Cursor

DEALLOCATE DBName_Cursor

DECLARE @db varchar(1024)

DECLARE @name1 varchar(200)

DECLARE @name2 int

DECLARE @hasdbaccess varchar(200)

DECLARE @islogin varchar(200)

DECLARE @isntname varchar(200)

DECLARE @isntgroup varchar(200)

DECLARE @isntuser varchar(200)

DECLARE @issqluser varchar(200)

DECLARE @isaliased varchar(200)

DECLARE @issqlrole varchar(200)

DECLARE @isapprole varchar(200)

SET @name2 = 1

DECLARE cur CURSOR FOR

select dbname,username,db_owner,db_accessadmin,db_securityadmin,db_ddladmin,db_datareader,db_datawriter,

db_denydatareader,db_denydatawriter,db_backupoperator from DBROLES WHERE username <> 'DBO'

OPEN cur

FETCH NEXT FROM cur INTO @counter,@name1,@hasdbaccess,@islogin,@isntname,@isntgroup,@isntuser,@issqluser,@isaliased,@issqlrole,@isapprole

WHILE (@@FETCH_STATUS = 0)

BEGIN

print '--@@@@@'+@name1+CONVERT(varchar(5),@name2)+'@@@@@--'

print 'DECLARE @'+@name1+CONVERT(varchar(5),@name2)+' varchar(1024)'

print 'DECLARE @'+@name1+CONVERT(varchar(5),@name2)+'2 varchar(1024)'

print 'DECLARE @'+@name1+CONVERT(varchar(5),@name2)+'3 varchar(1024)'

print 'DECLARE '+@name1 +CONVERT(varchar(5),@name2)+ ' CURSOR for'

print 'select name from master..sysdatabases where name IN ('+''''+@counter+''''+')'

print 'OPEN '+@name1+CONVERT(varchar(5),@name2)

print 'FETCH NEXT FROM '+@name1+CONVERT(varchar(5),@name2)+' INTO @'+@name1+CONVERT(varchar(5),@name2)

print 'WHILE (@@FETCH_STATUS = 0)'

print 'BEGIN'

print 'SET @'+@name1+CONVERT(varchar(5),@name2)+'2 = @'+@name1+CONVERT(varchar(5),@name2)+'+'+''''+'..sp_grantdbaccess '+''''+'+'+''''+@name1+''''

print 'EXEC (@'+@name1+CONVERT(varchar(5),@name2)+'2)'

-- @hasdbaccess

IF (@hasdbaccess = 'YES')

BEGIN

print 'SET @'+@name1+CONVERT(varchar(5),@name2)+'3 = @'+@name1+CONVERT(varchar(5),@name2)+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_owner'+','+@name1+''''