Posted by Erland Sommarskog on 07/07/06 22:21

rcamarda (robc390@hotmail.com) writes:
> I wish to use something other than sql's SA account user to connect to
> my data warehouse, so I created a user in our active directory user.
> Ill use dw as the new user as example.
> after I created the user, dw, in ADS, I added the user via Management
> Studio in Security> Logins.
> I grant ower of ads\dw to my datawarehouse.
> I try to connect to the database engine using SQL Servier
> Authentication, Login: ads\dw.
> I get Cannot connect to xxxx, Login failed for user 'ads\dw' (Microsoft
> SQL Server, Error: 18456).

Mixing apples and oranges, I see. To log into SQL Server as ADS\dw,
you need to be logged into Windows as ADS\dw. That's what integrated
security is all about. By already being authenticated by Windows,
there is no need for SQL Server to authenticate you again. But you
cannot log into SQL Server with another Windows login than the one
you are logged into Windows with. You can only log into SQL Server
with an explicit username/password with an SQL login.

> Next, I add this user to the local server's administrators group (the
> server is in admin mode) and login.

And dw now has sysadmin rights in the server, unless you remove
BUILTIN\Administrators.

> Now I can connect to the database as user dw. ( i suspect the users
> memebership of administrator is the reason).
> I dont wish to have the dw user part of administrator, but I want it to
> have control over just the datawarehouse database.
> What am I doing wroing?

First descide whether it's a Windows login or an SQL Login you want.
Next grant this user access to the server and database. Next you grant
him CONTROL on the database. (You are on SQL 2005, right?)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]