Get database name for a particular user | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Get database name for a particular user

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 get database name authorised to a particular login. Thanks and Regards
pavas
Check out the INFORMATION_SCHEMATA.SCHEMATA view. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
&gt;&gt;INFORMATION_SCHEMATA.SCHEMATA view.<br /><br />That is INFORMATION_SCHEMA.SCHEMATA view. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />select * from INFORMATION_SCHEMA.SCHEMATA<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Too much schema, I guess. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
I am not sure if the INFORMATION_SCHEMA.SCHEMATA view will return all the database names since it is limited to the current user. You won’t be able to retrieve all the database names. You can verify this using sp_databases or selecting * from the sysdatabases table. Alternate: SELECT dbid, a.name, loginname FROM sysdatabases A
JOIN syslogins B
ON a.sid = b.sid
WHERE loginname = ‘login search criteria goes here’
ORDER BY dbid – Tahsin
The INFORMATION_SCHEMA.SCHEMATA is the correct one to get the information, as querying against system tables must be refrained due to changes from SQL 2000 to 2005 and this will create a problem. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Alright, for the sake of argument, suppose you have databases created with sqlcharsets equal to NULL. Then how do you view them using the SCHEMATA view? Also, what would cause the sqlcharsets to be equal to NULL and not 1? To get a list, I created the following:
SELECT name, DatabasePropertyEx(name, ‘sqlcharset’) SQLCharset FROM master..sysdatabases – Tahsin
]]>