List databases with user access

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)

List databases with user access

Author

Topic

shg
Starting Member

3 Posts

Posted - 2004-06-17 : 02:13:51

Is there a way to get a list of all databases that the currently authenticated user has access to? I'm getting all databases right now by querying master.db.sysdatabases, but I only want to present those where they have some level of access.

If they can't do a 'use dbname' then I don't want to get it in the list.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-17 : 02:35:22

Check out the SP

sp_helplogins

in BOL. It should do what you want.

shg
Starting Member

3 Posts

Posted - 2004-06-17 : 02:38:54

That doesn't seem to be runnable by a normal user. You've got to be an admin to call it.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-17 : 02:54:56

Struggling to do it with the currently authenticated user. a system administrator could do it for another userid, using the following- hopefully someone can give you one that better matches your needs.

create table #db_access
(userid	sysname,
dbname	sysname)

set nocount on
set quoted_identifier off
go

DECLARE @Name varchar(255)

DECLARE DB_cursor CURSOR FOR select Name from MASTER..SYSDATABASES 

OPEN DB_cursor

FETCH NEXT FROM DB_cursor INTO @Name

WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec ("use " +  @name + " 
/* *** Sql View Workbench Template Record Transfer Script *** */
/* The target database name needs to still be set. */
declare @userid	sysname
declare @dbname sysname
select @userid = 'test'
select @dbname = db_name()
if exists (select 1 from sysusers where name = @userid)
	insert into #db_access values (@userid,@dbname)
")

FETCH NEXT FROM DB_cursor INTO @Name

END 
close DB_cursor
DEALLOCATE DB_cursor

select * from #db_access

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

safigi
Starting Member

15 Posts

Posted - 2004-06-17 : 10:44:32

This srcipt will give you databases that you can use int your current connection.

select [Name] from master.dbo.sysdatabases
WHERE IsNull(HAS_DBACCESS ([Name]),0)=1
order by [Name]

Safi

shg
Starting Member

3 Posts

Posted - 2004-06-17 : 14:00:36

Thank you SO much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources