Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server Login/Role And DB User/Role Script Expand / Collapse
Author
Message
Posted Friday, November 30, 2007 12:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 2:38 AM
Points: 398, Visits: 2,423
Comments posted to this topic are about the item Server Login/Role And DB User/Role Script

"-=Still Learning=-"

Lester Policarpio

Post #428291
Posted Friday, January 25, 2008 6:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:35 AM
Points: 1,277, Visits: 1,612
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".



Post #447478
Posted Friday, January 25, 2008 10:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:51 PM
Points: 889, Visits: 1,550
I also get the same batch of error msgs upon parseing of the SQL.

Kindest Regards,

Just say No to Facebook!
Post #447668
Posted Sunday, January 27, 2008 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:04 AM
Points: 21, 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






Post #448036
Posted Sunday, January 27, 2008 5:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 2:38 AM
Points: 398, Visits: 2,423
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

Post #448098
Posted Monday, July 21, 2008 4:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 2:22 AM
Points: 207, Visits: 427
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
Post #537542
Posted Monday, September 7, 2009 2:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:03 PM
Points: 3, Visits: 239
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.
Post #783661
Posted Tuesday, February 1, 2011 8:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 10:20 AM
Points: 537, Visits: 1,921
still lots of syntax errors
Post #1056889
Posted Monday, March 21, 2011 12:23 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:06 AM
Points: 361, Visits: 1,176
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 ¤
Post #1081500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse