SQL Server – How to Create Login for Domain Account

sql-server-2008

All,

I have Sql Server 2008 installed on a server (let's say Server1) in a Domain (let's say AD). I also have a domain account called AD\Sql1. This is not an Admin account on the Domain (AD), but I want it to be an admin on the Server1. And then create a login on Sql Server for that Domain account (not local account).

How do I do that?

Questions:

  1. The user AD\Sql1, I want him as Admin on Server1. For that, do I just create a local account with the same user name (let's say, Server1\Sql1) and make the local account an Admin on the machine. Will that local account be automatically mapped to the domain account with the same name?
    May be I should add here that CREATE LOGIN [AD\Sql1] FROM WINDOWS did not work for me. I tried and got an error saying user does not exist in Windows (something like that)
  2. Now, if the above technique works. Lets assume, AD\Sql1, becomes an Admin on Sever1. So, in theory I do not need to create a Sql Login for that Windows Domain account on the Sql Server, right? All the local admins that are part of BUILTIT\Admins automatically have sysadmin access to Sql Server, right?

Where did I go wrong? Please share any URLs that could explain this concept better.

Edit: I should also add that Question 1 and 2 are important independently. I know how to add a domain/windows account as sysadmin. But I am interested in how mapping works between Domain and local accounts. My goal here is not how to add a local account as sysadmin, I can do that pretty quickly. My intention is not to be rude, but be as clear as possible. If my question is still not clear, please let me know, so I could add more details.

Thanks,
_UB

Edit: Grammar

Best Answer

Don't create a local account with the same name as the domain account. If you want to add a domain login as a sql admins do as follows:

  • create a login for the domain account: create login [AD\Sql1] from windows;
  • add the login to sysadmin group: exec sp_addsrvrolemember 'AD\Sql1', 'sysadmin';

Done. You would achieve the same result if you'd simply add the AD\Sql account to the local administrators via net localgroup Administrators /add AD\Sql1 (from a CMD shell) but that is not the correct solution as it grants AD\Sql1 all NT administrator privileges in addition to granting him SQL admin, which is not stated as a requirement therefore is unnecessary elevation. BTW the rule that members of the local Administrators group are SQL admins is not implicit, is an explicit privilege granted by default during SQL Setup and it can be revoked so you have to check for it.

Related Question