How to allow access for a sql server user

sql-server-2005windows-server-2008

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)

Best Answer

SQL Server "users" have two parts to them.

  1. The server login. Defined as server level, and maybe associated with server roles (e.g. dbcreator: "Members of the dbcreator fixed server role can create, alter, drop, and restore any database"). Map Windows users to server roles (some of this is done automatically, but depends on the version of SQL Server1)

  2. Database users. These are created by database, given database roles (e.g. "dbo" for full control ("database owner") or "dbreader" to be able to read data). When create these are associated with a server login.

You can define server and database roles to give specific permissions).

See SQL Server Books Online (or on MSDN) for more details.

So you need to, using the administrator account, create a server login for your Windows user account, and give it access to the databases you need to work with. If you are defining things (tables, views, stored procs, ...) you really need "dbo" role, or quite some work for finer grained control.

Remember to test the application with just the access that application needs (e.g. it is a rare application that would create/drop/alter views or tables).

1 In SQL Server 2005 the host machine's local Administrators group is mapped to a login with "sysadmin" server role, in 2008 you specify which accounts are to be so mapped in the setup).

Related Topic