I am a developer-trying-to-play-admin and wish to connect to a remote sql server from my development machine using a sql server user ("op_web").
When I try to connect from vs2008 Server Explorer, I can connect to the server, but no databases are listed. If I connect using the server admin user, all databases are listed as expected.
The server is a relatively fresh install made by me.
I have
allowed for remote connections in sql server.
created the login op_web at server level
created a user at database level and assigned to login with same name
assigned roles to the user to allow for reading and writing - I have assigned no schemas and default schema for the user is dbo.
If I log on (locally at server) using sqlserver management studio/sqlserver authentication and the created login, I can display and alter table data as I would expect.
Remote access gives me no choice of databases.
In Visual Studio on the client machine, I get the server name listed in the dropdownlist with discovered sql server - and as mentioned, I am able to connect using windows user (adminstrator account). Furthermore I've tried from another physical machine with same negative results. It doesn't smell like a firewall issue, but I've tried to disable the server firewall just in case, but that didn't fix it either. I have another database server where everything works, and I've cloned every setting user-to-user (So it smells like the problem is related to the sql server instance rather than the user).
Any pointers to what I might have missed?
(This question was asked on https://stackoverflow.com/questions/1386223/how-to-allow-access-for-a-sql-server-user)