When you start preparing for the 70-432, one of the first topics you’ll have to get used to is choosing the appropriate type of account you need to use for different types of access to your SQL server.  You’ll need to be able to recognize the differences in access you get when you choose a local account, local administrator, domain account, system account, etc.

Learning when to use each can be tough at first, but with a little experimentations you’ll pick up on when to use each.

First up, move services off the default accounts.  It’s generally considered best practice to NOT use the default accounts to run your SQL server service, agent service, etc from the accounts the in staler chooses to run them.  You generally want to set em each to use a different account, and en ONLY grant those accounts just enough privilege to run the service you’ve assigned them to run.

Next, if your computer is on a domain, you want to use domain accounts rather than local accounts.  Otherwise you’ll always have to connect to each SQL server to update permissions.  Seriously, why would you want to administer your accounts that way when you(or your systems administrator) could set those accounts up in active directory, and manage those accounts from a single point.

The only time I’d use a local account  (of any type) to run a service would be on my local development copy of SQL Server.  Any other time I’m asked to use that account, I usually will ask why we can’t use any other account.

 

Now, when would I use an administrator account?

Hardly ever.  I don’t want any of my services running as a local or a domain administrator since that’s far too much access to grant a service.  You never know what security exploit might be lurking, and allow some random script kiddy to get away with major damage to your system.

I also don’t want to allow a proxy account to an administrator account for the same reason.  The point of security is to allow the least permission to accomplish the task at hand.  Otherwise, someone is bound to do something that wasn’t intended, and it’ll fall on you as the DBA to clean up.  It’s better to just lock it down from the get go, and have to open permissions as needed.

Now that we’ve got all that out in the open, let’s cover a business case and see if you’re ready to answer a question like you’d face on the 70-432.

Scenario 1

You’re setting up a new SQL Server on a machine that will sit in your DMZ and host data to your company’s extranet site.  You need to set up an account that will update production tables by reading in files placed on that server via FTP.  What kind of account do you need to set up for this process?

You’ll have to set up a local user account.  Since this machine is in the DMZ, it won’t have access to your domain.  So you know you’re stuck using local accounts.  You don’t want to use an administrator’s account here, since all it needs access to do is run a SSIS package, read files from a folder, and INSERT/UPDATE records in a table.

 

Scenario 2

You’ve set up a new server in your network to act as an QA server.  You’re going to need to run a powershell script that will read the backup files from your production server, and restore those files to the QA server.  What kind of account do you need to set up for this process?

You’re going to need to set up a domain account.  This account will need to be granted (read-only) access to the folder where your production backups are written, it will then need read-write access to a working folder on your QA server.  It will need to be able to run a PowerShell script to move the files, then run the restore locally.  It should be granted only dbcreator permission in order to run the restore commands on the SQL instance.

 

Summary

While I’m not an expert in permissions yet, I do feel comfortable setting up accounts (as long as I have #sqlhelpand the BOL).  I’m comfortable enough with this topic to test on the 70-432.  Before I move up to the next level, I will be spending a bit more time studying this material, and learning exactly where one account ends and the next begins!

Good luck with your exams!  Let me know if I can help in any other way!