17

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

UB01
  • 937
  • 2
  • 9
  • 18

2 Answers2

17

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.

Remus Rusanu
  • 51,846
  • 4
  • 95
  • 172
  • Thank you, this comment has some info that I could use. But I have a follow up question. when I tried to add Sql login using CREATE LOGIN [AD\Sql1] FROM WINDOWS, I got an error saying that the user does not exist in Windows. So, I had to add him as used to local machine (then it works). – UB01 Mar 28 '12 at 18:26
  • I'll look into the point that you mentioned about '..the local admins being by default sysasmins on the machine'. Thank you, I will read up on that. But how does the mapping work? Between Domain accounts and local accounts with the same name. – UB01 Mar 28 '12 at 18:31
  • If machine where instance of SQL Server installed, is part of domain you should be able to add domain account without any issue. – JackLock Mar 28 '12 at 19:01
  • Okay, that's what I thought, but I kept getting this error: ...user could not be found in windows. May be there is something else going on. I will check again. – UB01 Mar 28 '12 at 19:11
  • 1
    I got an error saying that the user does not exist in Windows: that means your SQL host machine is not member of the AD domains, or member of a domain that trusts AD. You should add the machine to AD domain. You are now pursuing the so called 'NT mirrored accounts' approach (perhaps w/o knowing you're doing so...) and this is a wrong approach. Add the SQL hosting machine to the AD domain. – Remus Rusanu Mar 28 '12 at 19:12
  • Okay, thanks for that last bit. I had a feeling that what ever I was doing, was not the best approach. But it worked, so I did not check BoL for more info. I am glad I posted a question here. – UB01 Mar 28 '12 at 19:17
2

Connect to your instance in SSMS. Expand security / logins. Rt-click add new login, put in the info. On server roles, grant it sysadmin if you want it to have total power over the SQL instance. Done. This account does not need to be local admin on your Windows machine to be sysadmin in SQL Server.

Eric Higgins
  • 2,689
  • 1
  • 18
  • 25
  • Thank you for the quick comment. I do understand the concept you conveyed in your response. My question (may be not very clear) is to understand how the relation (or mapping) works between Domain accounts and local accounts. And how that affects Sql Server. – UB01 Mar 28 '12 at 18:09
  • A SID is create in the master db for any accounts, domain or SQL Server only accounts. – Eric Higgins Mar 28 '12 at 18:47
  • SID created for each account.. is a concept I understand. Let me give you an example: Is the account AD\Sql1 same as Server1\Sql1 on the server. Does each have a seperate Security ID that is mapped so that OS know that they are one and the same? Does this even happen? (I am only guessing, I do not know) – UB01 Mar 28 '12 at 18:59
  • I see. You are more trying to understand Windows creds I think. AD and local Windows accounts are not the same (even if name is the same). Here's some info about how domain SIDs, access tokens work. I hope this helps: http://technet.microsoft.com/en-us/library/cc785913(v=ws.10).aspx – Eric Higgins Mar 28 '12 at 19:18
  • Thank you, I'll read that article and see if that helps me understand the concepts better. – UB01 Mar 28 '12 at 19:23