Administrative task: users and groups

microsoft.public.sqlserver.programming

29 Jul 2005 8:29 AM

Enric

Dear all,

I am pursuing any solution for an administrative task which once put on
production, it cut off a lot of time. I know I've written this request before
and maybe I am such a pain (sorry for that) but it's important.

The idea is to write a job/dts/sp which will be able to show all the NT
users and groups (excluding sql users) as well as its permissions in each
database of each Sql Server (almost twenty) of a domain using two cursors.
Nowadays it is not working because of the first cursor.

*sp_helprotect and sp_helpuser provide us the information required.
*userperDB table save the information showed per sp_helprotect
*Also, a new insert might be added (sp_helpuser <nameuser> to the same table
or another one, never mind)

Any help or though would be great,

Regards,

DECLARE @BD AS CHAR(80)
DECLARE    @STRING AS CHAR(20)

declare cursorbd cursor fast_forward for
    SELECT ' ' + NAME FROM MASTER.DBO.SYSDATABASES
        WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSDB','NORTHWIND')

open cursorbd

fetch next from cursorBD into @BD

while @@fetch_status = 0
begin

                                                USE '+@BD+''

            declare @sql varchar(300)
            declare @USER as char(25)
            declare @login1 as char(30)

            declare cursor1 cursor fast_forward for
                select name from sysusers nolock
                where isntuser = 1 or isntgroup = 1

            open cursor1
            fetch next from cursor1 into @USER

            while @@fetch_status = 0
            BEGIN

                set @sql = ('insert into userperDB sp_helprotect NULL, ''' +
lTRIM(rTRIM(@USER)) + '''')
                exec (@sql)

                fetch next from cursor1 into @USER
            end
            close cursor1
            deallocate cursor1

            fetch next from cursorBD into @BD

END
CLOSE CURSORBD
DEALLOCATE CURSORBD