SQL server 2008 finding databases owned by a specific user

Question

I'm going to create something like Visual Studio "Add Connection" box, my users can enter database server name, username & password, and then they can get a list of available databases (only for the entered username/password) or an error should be displayed if entered information is not correct. I've used following code which returns all databases in server:

            using (var con = new SqlConnection("Data Source=" + txtServername.Text + "; User ID=" + txtUsername.Text + ";Connect Timeout=200;pooling=false;Password=" + txtPassword.Text))
        {
            con.Open();
            DataTable databases = con.GetSchema("Databases");
            foreach (DataRow database in databases.Rows)
            {
                String databaseName = database.Field<String>("database_name");
                short dbID = database.Field<short>("dbid");
                DateTime creationDate = database.Field<DateTime>("create_date");
            }
        }

how can I limit results to databases owned by specified user?

Answer 1

You could use this query (from this article) and add the WHERE clause you need on the owner_sid :

SELECT name, SUSER_SNAME(owner_sid)
FROM   sys.databases

For database granted access enumeration, following that thread, you can use this query, and add the needed WHERE statement :

CREATE TABLE #tempww (
    LoginName nvarchar(max),
    DBname nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)

INSERT INTO #tempww 
EXEC master..sp_msloginmappings 'sa' -- REPLACE IT BY YOUR USERNAME PARAMETER

-- display results
SELECT DBname
FROM   #tempww 
WHERE LoginName='sa' -- REPLACE IT BY YOUR USERNAME PARAMETER
ORDER BY dbname, username

-- cleanup
DROP TABLE #tempww