Query : Running a job/step in a loop for ALL databases

[Garry Clarke]

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'

[Simon Hayes]

"Garry Clarke" <gc*****@euro.banta.com> wrote in message
news:fe**************************@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