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

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 14, 2006 at 8:08 am

  #626524

  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

  March 14, 2006 at 11:17 pm

  #626686

  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

  March 15, 2006 at 6:09 am

  #626728

  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

  March 15, 2006 at 7:07 am

  #626747

  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

  March 16, 2006 at 12:04 am

  #626948

  Thanks a lot both the queries are of great help

  ---

  Kindest Regards,

  Pavas

  Dream The Dream
  Explore the World
  Experince The Exhilaration