Server Login/Role And DB User/Role Script

• Lester Policarpio

  SSCertifiable

  Points: 7109

  More actions

  November 30, 2007 at 12:39 pm

  #177090

  Comments posted to this topic are about the item Server Login/Role And DB User/Role Script

  "-=Still Learning=-"

  Lester Policarpio

• RML51

  SSCarpal Tunnel

  Points: 4410

  More actions

  January 25, 2008 at 6:53 am

  #771724

  Errors...

  Msg 156, Level 15, State 1, Line 36

  Incorrect syntax near the keyword 'FETCH'.

  Msg 156, Level 15, State 1, Line 43

  Incorrect syntax near the keyword 'DEALLOCATE'.

  Msg 156, Level 15, State 1, Line 110

  Incorrect syntax near the keyword 'FETCH'.

  Msg 156, Level 15, State 1, Line 168

  Incorrect syntax near the keyword 'DEALLOCATE'.

  Msg 102, Level 15, State 1, Line 231

  Incorrect syntax near '?'.

  Msg 102, Level 15, State 1, Line 235

  Incorrect syntax near '?'.

  Msg 102, Level 15, State 1, Line 248

  Incorrect syntax near '?'.

  Msg 137, Level 15, State 2, Line 268

  Must declare the scalar variable "@mSql1".

• YSLGuru

  SSC-Insane

  Points: 21632

  More actions

  January 25, 2008 at 10:37 am

  #771888

  I also get the same batch of error msgs upon parseing of the SQL.

  Kindest Regards,

  Just say No to Facebook!

• Oleg Oransky

  SSChasing Mays

  Points: 625

  More actions

  January 27, 2008 at 7:57 am

  #772191

  I also get the same batch of error msgs upon parseing of the SQL.

  Very strange...

  I fixed ONLY first part of script "creating logins"

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

  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 <> 'sa')

  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 master_tc CURSOR FOR

  SELECT name,dbname FROM master..syslogins WHERE name <> 'sa'

  OPEN master_tc

  FETCH NEXT FROM master_tc INTO @name,@default

  WHILE (@@FETCH_STATUS = 0)

  BEGIN

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

  FETCH NEXT FROM master_tc INTO @name,@default

  END

  CLOSE master_tc

  DEALLOCATE master_tc

  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

• Lester Policarpio

  SSCertifiable

  Points: 7109

  More actions

  January 27, 2008 at 5:41 pm

  #772243

  sorry for the inconvenience guys there has been a problem posting the script because it outputed in only 1 line please try the script below and message me if its ok now.. 1 thing though i found out that users that owned a table in the database are not included in the script as of now so the script is 100% functioning only to a user not owning a table.. ill be updating the script so that orphaned users that owned tables will be fix keep in touch for any other related questions thanks GUDAM

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

  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 master CURSOR FOR

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

  OPEN master

  FETCH NEXT FROM master INTO @name,@default

  WHILE (@@FETCH_STATUS = 0)

  BEGIN

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

  FETCH NEXT FROM master INTO @name,@default

  END

  CLOSE master

  DEALLOCATE master

  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 ''

  print ''

  DECLARE master 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 master

  FETCH NEXT FROM master 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 master INTO @logins,@default1,@sysadmin,@securityadmin,@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin

  END

  CLOSE master

  DEALLOCATE master

  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 @mSql1varchar(8000)

  DECLARE DBName_Cursor CURSOR FOR

  select name

  frommaster.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+''''

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

  END

  -- @islogin

  IF (@islogin ='YES')

  BEGIN

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

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

  END

  -- @isntname

  IF (@isntname = 'YES')

  BEGIN

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

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

  END

  -- @isntgroup

  IF (@isntgroup ='YES')

  BEGIN

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

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

  END

  -- @isntuser

  IF (@isntuser = 'YES')

  BEGIN

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

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

  END

  -- @issqluser

  IF (@issqluser = 'YES')

  BEGIN

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

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

  END

  -- @isaliased

  IF (@isaliased = 'YES')

  BEGIN

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

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

  END

  -- @issqlrole

  IF (@issqlrole = 'YES')

  BEGIN

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

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

  END

  -- @isqpprole

  IF (@isapprole = 'YES')

  BEGIN

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

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

  END

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

  print 'END'

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

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

  SET @name2 = @name2+ 1

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

  END

  CLOSE cur

  DEALLOCATE cur

  DROP TABLE DBROLES

  "-=Still Learning=-"

  Lester Policarpio

• RGP

  SSCarpal Tunnel

  Points: 4381

  More actions

  July 21, 2008 at 4:45 am

  #845657

  Msg 156, Level 15, State 1, Line 36

  Incorrect syntax near the keyword 'FETCH'.

  Msg 156, Level 15, State 1, Line 43

  Incorrect syntax near the keyword 'DEALLOCATE'.

  Msg 156, Level 15, State 1, Line 110

  Incorrect syntax near the keyword 'FETCH'.

  Msg 156, Level 15, State 1, Line 168

  Incorrect syntax near the keyword 'DEALLOCATE'.

  -----------> getting this error

• srikanth anne

  SSC Rookie

  Points: 47

  More actions

  September 7, 2009 at 2:33 am

  #1050015

  the cursor name is declared as "master" which is a reserve word. Change the cursor name from "master" to "master_cursor" and the script should work fine.

• DBA-640728

  SSChampion

  Points: 12898

  More actions

  February 1, 2011 at 8:59 am

  #1280995

  still lots of syntax errors

• sunshine-587009

  SSCrazy Eights

  Points: 8981

  More actions

  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 ''

  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+''''

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

  END

  -- @islogin

  IF (@islogin ='YES')

  BEGIN

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

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

  END

  -- @isntname

  IF (@isntname = 'YES')

  BEGIN

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

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

  END

  -- @isntgroup

  IF (@isntgroup ='YES')

  BEGIN

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

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

  END

  -- @isntuser

  IF (@isntuser = 'YES')

  BEGIN

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

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

  END

  -- @issqluser

  IF (@issqluser = 'YES')

  BEGIN

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

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

  END

  -- @isaliased

  IF (@isaliased = 'YES')

  BEGIN

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

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

  END

  -- @issqlrole

  IF (@issqlrole = 'YES')

  BEGIN

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

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

  END

  -- @isqpprole

  IF (@isapprole = 'YES')

  BEGIN

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

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

  END

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

  print 'END'

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

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

  SET @name2 = @name2+ 1

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

  END

  CLOSE cur

  DEALLOCATE cur

  DROP TABLE DBROLES

  ¤ §unshine ¤