Is there a XP or SP in SQL 2K that will give you a list of all the users defined on that server and what their security rights are in each database on that server?


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

__________________
Hal Levy

you can find current users is the master database table sysusers. It is normally not recommend querying system tables. Also
EXEC sp_helpsrvrolemember 'sysrole'

example: EXEC sp_helpsrvrolemember 'sysadmin'

ok-= lets go about it this way.

We are trying to create a report that lists all the Local users, NT users or NT Groups with access to SQL server and what rights they have.

Is there anything in SQL server that will help me do that?


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Ok,

here a script that will do what you need
set nocount on
declare @name sysname,
    @SQL nvarchar(600)

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
    drop table #tmpTable

CREATE TABLE #tmpTable (
    [DATABASENAME] sysname NOT NULL ,
    [USER_NAME] sysname NOT NULL,
    [ROLE_NAME] sysname NOT NULL)

declare c1 cursor for
    select name from master.dbo.sysdatabases

open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
    select @SQL =
        'insert into #tmpTable
         select N'''+ @name + ''', a.name, c.name
        from ' + QuoteName(@name) + '.dbo.sysusers a
        join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
        join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
        where a.name != ''dbo'''

        /* Insert row for each database */
        execute (@SQL)
    fetch c1 into @name
end
close c1
deallocate c1

select * from #tmpTable



http://www.sqlservercentral.com/scri...utions/276.asp
weirdMan

This is very close to what I need- however, it does not list people in the "public" role and it doesn't list people defined as System Administrators as users.. so this is much closer, but not complete. It's a great start.


Thanks!

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

from books online

 



Sal