Get database name for a particular user

Question

Post reply

Get database name for a particular user

Pavas Malviya

Ten Centuries

Points: 1109
More actions
March 14, 2006 at 6:40 am

#112232

Hi,
We are working on a web interface and we need to list all the database with particular login details.Is there a query from where i can geta list of database name authorised to a particular login.
Thanks and Regards
pavas
Kindest Regards,
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

You will have to query sysusers for all the databases and use some unconventional features :

declare @cmd varchar(300)

select @cmd = 'select ''?'' as DB from [?].dbo.sysusers where isaliased = 0 and sid = ' + dbo.fn_varbintohexstr(suser_sid(suser_sname()))

--select @cmd

if object_id('tempdb..#t1') Is not null

drop table #t1

create table #t1 (databases varchar(128))

insert into #t1 (databases)

exec master..sp_MSforeachdb @cmd

select * from #t1

Cheers,

* Noel

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 2

Hi,

The query looks good and fantastic but i m not able to understand this query,let me know where i can pass the user name of the database.

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's noeld's script witht he username pulled out as a parameter:in my example the username is 'hds'; i don't know how this will work for NT logins.

declare @cmd varchar(300)

declare @user varchar(30)

set @user='hds'

select @cmd = 'select ''?'' as DB from [?].dbo.sysusers where isaliased = 0 and sid = ' + dbo.fn_varbintohexstr(suser_sid(@user))

--select @cmd

if object_id('tempdb..#t1') Is not null

drop table #t1

create table #t1 (databases varchar(128))

insert into #t1 (databases)

exec master..sp_MSforeachdb @cmd

select * from #t1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Burrows SSC Guru Points: 65142 More actions · Answer 4

or

declare @cmd varchar(300)

declare @user varchar(30)

set @user='loginname'

select @cmd = 'SELECT DISTINCT ''?''

FROM master.dbo.syslogins l

inner join [?].dbo.sysusers o ON o.sid = l.sid

WHERE o.name = ''' + @user + '''

and o.hasdbaccess = 1'

create table #t1 (databases varchar(128))

insert into #t1 (databases)

exec master..sp_MSforeachdb @cmd

select * from #t1

drop table #t1

Far away is close at hand in the images of elsewhere.
Anon.

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 5

Thanks a lot both the queries are of great help

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration