I've written a job to export user and database permissions for all
d/b's on a server. As you can see below, the T-SQL commands are the
same for each d/b. Can anyone assist with regard to re-writing this so
that any new d/b's added do not require ammending the job (loop)?
Thx,
GC.
use master
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use msdb
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test1
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test2
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'"Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0310240324.77f4ce60@.posting.google.c om...
> Hi,
> I've written a job to export user and database permissions for all
> d/b's on a server. As you can see below, the T-SQL commands are the
> same for each d/b. Can anyone assist with regard to re-writing this so
> that any new d/b's added do not require ammending the job (loop)?
> Thx,
> GC.
> use master
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use msdb
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test1
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test2
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
A cursor is one way to do this (cursors are usually a bad idea in
application code, but can be useful for admin scripts):
declare @.db sysname
declare cur_dbs cursor fast_forward
for select name from master..sysdatabases
order by name
open cur_dbs
fetch next from cur_dbs into @.db
while @.@.fetch_status = 0
begin
select @.db
exec('exec ' + @.db + '..sp_helpuser')
exec('exec ' +@.db + '..sp_helprotect NULL, NULL, NULL, ''os''')
fetch next from cur_dbs into @.db
end
close cur_dbs
deallocate cur_dbs
Simon
