Add all users to user databaes as readonly

,

I am new to writing scripts so dont blast be to bad.  This is a script to make all logins of a sql server readonly users to all user databases.  Mainly after a restore from a different server. (for reporting maybe)

Any methods to optimize this script are fine.

declare @usrname varchar(100), @rcommand varchar(100), @dbcommand varchar(100)
declare @dbname varchar (100), @gcommand varchar (100), @dwcommand varchar(100)

declare Crs insensitive cursor for

SELECT     name AS UserName
FROM         syslogins
WHERE     (sysadmin <> 1)
ORDER BY name

for read only

declare dbCrs insensitive cursor for

SELECT     name AS dbname
FROM         sysdatabases
WHERE     (dbid <> 1) AND (dbid <> 2) AND (dbid <> 3) AND (dbid <> 4) AND (dbid <> 5)

for read only


open dbCrs


  fetch next from dbCrs into @dbname
	while @@fetch_status=0
begin

  select @dbcommand = 'use ' + @dbname

  open Crs

    fetch next from Crs into @usrname
	while @@fetch_status=0

  begin

    select @gcommand='EXEC sp_grantdbaccess '''+@usrname+''', '''+@usrname+''' '
    select @rcommand='EXEC sp_addrolemember ''db_datareader'', '''+@usrname+''' '
    select @dwcommand='EXEC sp_addrolemember ''db_denydatawriter'', '''+@usrname+''' '	

	exec (@dbcommand + CHAR(13) + 'go' + CHAR(13) + @gcommand + CHAR(13) + 'go' + CHAR(13) + @rcommand + CHAR(13) + 'go' + CHAR(13) + @dwcommand + CHAR(13) + 'go')
		
    fetch next from Crs into @usrname

  end

    close Crs

fetch next from dbCrs into @dbname

end

close dbCrs

deallocate Crs
deallocate dbCrs

Rate

Share

Share

Rate