Getting correct user list from sysusers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting correct user list from sysusers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-20 : 07:03:33

Rudy writes "Hello and Good Day,

I'm trying to general a user list from each database on a MSSQL 2000 serer so that I can submit it to the departments for validation. This monthly report would help to ensure that users that have access have the correct access or removed from the database(s)

Here's the problem. When I user either of the following, I get the correct list of users

Method 1)

select name from sysusers where status <> 0 and name <> 'guest'and name <>'dbo'and name <>'Administrator'

Method 2)

select
left(rtrim(case u1.islogin when 1 then u1.name end), 30) 'Login Name',
left(rtrim(u1.name), 30) 'User Name',
left(rtrim(u2.name), 30) 'Group Name'
from sysusers u1, sysusers u2
where u1.gid = u2.uid and
u1.sid is not null and
u1.name not in ('guest', 'dbo', 'Administrator')

Now, if you place either method into a loop so that I will get the user information from each database, I only get a partial list.

Any word of wizdom would be great!

Thanks in advance for your time and effort.

Rudy
------------------------------------

Here is the looping scipt I've been using with Method 1)

--/********* Start of Script
use master
go

if exists (select * from sysobjects where id = object_id('dbo.sp_cs_loginrpt') and sysstat & 0xf = 4)
drop procedure dbo.sp_cs_loginrpt
GO

create procedure sp_cs_loginrpt
as

exec ('use master')
print' '
print ' User Login Report'
print ' '
print ' Server name: ' + @@servername
print ' '
print ' The current time is: '+ CONVERT(char(30), CURRENT_TIMESTAMP)
print ' '

-- Create list of all databases on the MSSQL2000 server

DECLARE @dataname varchar(50)
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases
WHERE name not in ('tempdb')

-- Cycles through each database to get user list

OPEN datanames_cursor

FETCH NEXT FROM datanames_cursor INTO @dataname

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END

PRINT ' '
PRINT 'User Login Report for ' + @dataname + ' Database'
PRINT ' '

-- Changes to use each database

EXEC ('USE ' + @dataname)

-- Gets list of users

EXEC ('select * from sysusers where status <> 0')

FETCH NEXT FROM datanames_cursor INTO @dataname
END

DEALLOCATE datanames_cursor
print' '
print 'End of Login Report'
go

IF OBJECT_ID('dbo.sp_tb_loginrpt') IS NOT NULL And
OBJECTPROPERTY(OBJECT_ID('dbo.sp_cs_loginrpt'), 'IsProcedure') = 1

PRINT '*** The stored procedure: sp_cs_loginrpt was created ***'
ELSE
PRINT '*** ERROR! Failed to create stored procedure: sp_cs_loginrpt ***'
go
-- ******* End of Script"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-20 : 07:12:16

Here is other approach

Run this in Query Analyser

Select 'select name from '+name+'..sysusers where status <> 0 and name <> ''guest''and name <>''dbo''
and name <>''Administrator''' from master..sysdatabases

Copy the result back to Query Analyser and then run

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources