Get user databases - SQL Server Forums

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)

Get user databases

Author

Topic

achobbs
Starting Member

11 Posts

Posted - 2004-03-20 : 16:59:57

How can I get the databases that the current logged on user has access to without searching in every database.

ie. if I login with a SQL account "user1" and the default db is "database1", how can I determine what other databases "user1" has access to?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-21 : 21:51:34

see sp_helplogins

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

achobbs
Starting Member

11 Posts

Posted - 2004-03-21 : 22:12:25

How about if I am not member of sys or sec admin groups? Any way to do it as a normal user?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-22 : 11:13:09

Why not edit/copy the sp_helplogin code and remove the 'microsoft (admin only) limitation' which is very close to the start?

Best to execute this on a 'safe' copy of your database/server!

achobbs
Starting Member

11 Posts

Posted - 2004-03-22 : 14:54:36

I found a solution similar to what Andrew posted and thought I would post here in case anyone else has same problem.

There is a tsql function has_dbaccess that takes a database name and returns 1 if the current user has access and 0 if the user does not have access and null if the database name is invalid, so the following would print a list of all databases the current user has access to:

declare @db varchar(255)
declare cur cursor for select name from master..sysdatabases
open cur
fetch next from cur into @db
while @@fetch_status = 0
begin
if (has_dbaccess(@db) = 1)
print 'User has access to ' + @db
else
print 'User does not have access to ' + @db

fetch next from cur into @db
end
close cur
deallocate cur

Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-22 : 20:13:34

So this might work also:

SELECT
CASE
WHEN has_dbaccess(name) = 1 THEN 'User has access to ' + name
ELSE 'User does not have access to ' + name
END AS access
FROM
master.dbo.sysdatabases

?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 09:01:07

SELECT DISTINCT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME NOT IN ('master', 'tempdb', 'model', 'msdb')

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources