SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server Login/Role And DB User/Role Script


Server Login/Role And DB User/Role Script

Author
Message
Lester Policarpio
Lester Policarpio
Say Hey Kid
Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)

Group: General Forum Members
Points: 677 Visits: 2459
Comments posted to this topic are about the item Server Login/Role And DB User/Role Script

"-=Still Learning=-"

Lester Policarpio
RML51
RML51
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 1612
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
YSLGuru
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1848 Visits: 1665
I also get the same batch of error msgs upon parseing of the SQL.

Kindest Regards,

Just say No to Facebook!
Oleg Oransky
Oleg Oransky
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 272
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
Lester Policarpio
Say Hey Kid
Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)

Group: General Forum Members
Points: 677 Visits: 2459
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
rinu philip
rinu philip
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 506
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
srikanth anne
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 269
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
DBA-640728
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 1995
still lots of syntax errors
sunshine-587009
sunshine-587009
SSC Eights!
SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)

Group: General Forum Members
Points: 835 Visits: 1273
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 ¤
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search