List all databases accessible by a login

Hi all. I am new to SQL-DMO so sorry if what i am asking is really easy.

Is it possible to get a list of all objects (including databases) which are accessible for a particular login.

So far I have:

Dim oSQLServer As SQLDMO.SQLServer
Dim oSQLDatabase As SQLDMO.Database
Dim oSQLLogin As New SQLDMO.Login
Dim oUser As New SQLDMO.User

oSQLServer = New SQLDMO.SQLServer
oSQLServer.Name = _oSQLServer.Host

' Check server is alive

If (oSQLServer.Status = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running) Then

Try

oSQLServer.LoginSecure = False

oSQLServer.Connect(_oSQLServer.Host, _oSQLServer.Login, _oSQLServer.Password)

For Each oSQLDatabase In oSQLServer.Databases

'oSQLDatabase.Users.

' Get a list of databases accessible by user

oSQLLogin.Name = _oSQLServer.Login

'oSQLObjectList = oSQLUser.ListOwnedObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_Database)

'oSQLDMONameList = oSQLServer.ListMembers(SQLDMO.SQLDMO_ROLE_TYPE.SQLDMORole_All)

System.Diagnostics.Debug.Write(oSQLDatabase.Name.ToString & " : " & oSQLDatabase.IsUser(oSQLLogin.Name))

'oSQLDatabase.IsUser()

Next

Catch ex As Exception

System.Diagnostics.Debug.Write(ex.Message.ToString)

Finally

oSQLDatabase = Nothing

oSQLServer.DisConnect()

oSQLServer = Nothing

End Try

Else

System.Diagnostics.Debug.Write(oSQLServer.StatusInfoRefetchInterval( _

SQLDMO.SQLDMO_STATUSINFO_TYPE.SQLDMOStatInfo_All))

End If

The "oSQLLogin.Name = _oSQLServer.Login" statement does not seem to satisfy your requirements. Another side question, is there a particular reason why you are starting out with DMO? If not, you should consider using SMO, which ships with SQL Server 2005.

I have created a SMO program to meet your needs...The program checks to see if a user exists in the database. By access, you might want to check if the user has certain permissions. You may also need to deal with logins that are mapped to a different user name.

Server srv = new Server("MyServer"); //Yukon

srv.ConnectionContext.LoginSecure = true;

srv.ConnectionContext.Connect();

srv.DefaultTextMode = false;

string checkUser = "bob";

try

{

foreach (Database db in srv.Databases)

{

if (db.Users[checkUser] != null)

{

Console.WriteLine(checkUser + " has access to " + db.Name.ToString());

}

}

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

|||

I am using DMO as when I wrote the specification for the project, I stated that I would be using SQL-DMO. SQL 2005 had not be released and I could not wait for release date, incase of delays.

I am able to get a list of accessible databases for a particular login using

Dim oSQLDatabase As SQLDMO.Database

......

System.Diagnostics.Debug.WriteLine(oSQLDatabase.UserName & " user within database " & oSQLDatabase.Name)

However, it throughs an exception when every it hits the 'model' databases, dont suppose anybody knows why?

Thanks

DAN

|||

I tried the following code and it works against model (i.e no exception). What exception are you seeing? Which version of SQLDMO are you using -- located in "%ProgramFiles%\Microsoft SQL Server\80\Tools\Binn" folder? Which version of SQL Server are you targeting? (I am using the version of SQL-DMO that ships with SQL Server 2005 and I am targeting SQL Server 2000).

foreach (Interop.sqldmo._Database db in srv.Databases)

{

try

{

Console.WriteLine(db.Name + ":" + db.IsUser("bob"));

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

}

|||

Forget about the error, it was problem with the code else where in the applcation, conflicting names me thinks. Took your advice and moved over to SMO, after some alteration to the spec. Things a lot easier!

Thanks for your help Peter.