Server Login/Role And DB User/Role Script

  • Lester Policarpio

    SSCertifiable

    Points: 7109

    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

    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: 21517

    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

    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

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

    "-=Still Learning=-"

    Lester Policarpio

  • RGP

    SSCarpal Tunnel

    Points: 4381

    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

    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: 12896

    still lots of syntax errors

  • sunshine-587009

    SSCrazy Eights

    Points: 8815

    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 ¤

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply