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?

score 3 · Accepted Answer · edited May 23 '17 at 10:31

3

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

edited May 23 '17 at 10:31

Community

1
1

answered Jan 29 '14 at 13:37

cubitouch

1,929
15
28

Thanks but it gives 'sa' as user, how can I find correct name of my user? – Ali_dotNet Jan 29 '14 at 16:35
Define "correct" ? Do you need anything else than "ownership" relation ? (like granted access) – cubitouch Jan 29 '14 at 16:37
I have several databases in my server (more than 30), but this special user (for instance user1) has only one database, I want to have only this database, but it seems that 'sa' is returned as username of all databases, how can I find USERNAME of my databases? – Ali_dotNet Jan 29 '14 at 16:40
In fact I'm going to have something like Add Connection box in Visual Studio, you enter server name, username & password (in SQL Authentication Mode) and then you get databases belonging to this user – Ali_dotNet Jan 29 '14 at 16:41
1

Define "has" ? How can you humainly determine that this database is the **users's** database ? I think what you are talking about now is not ownership but granted access... Am I right ? – cubitouch Jan 29 '14 at 16:42
I think so, how can I have it? – Ali_dotNet Jan 29 '14 at 16:44
thanks cubitouch but could you please provide some notes? my user enters his username/password (and also servername), and he should see only databases available for this user. How can I perform it? Thanks – Ali_dotNet Jan 29 '14 at 17:04
@Ali_dotNet have a try with the last edit, more precise. – cubitouch Jan 29 '14 at 17:06
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/46359/discussion-between-ali-dotnet-and-cubitouch) – Ali_dotNet Jan 29 '14 at 17:14

SQL server 2008 finding databases owned by a specific user

1 Answers1