PROBLEM

With a new SQLServer install, if you selected Mixed Mode authentication, chances are you want to be able to use your sa user to connect to the database. You will be able to connect just fine, but will soon discover that the sa user is limited in what it can do on your local machine. For instance, you will be unable to attach mdf files the way you are accustomed to. The reason is that, if you took all the defaults during your SQL Server installation, your sa account was defaulted to logon (to windows) as a service (a local service).  This needs to be changed – the sa account needs to be able to logon as a Local account (specifically, a Local System account). The reason is that the System group, by default, has access to the entire filesystem on your machine. 

SOLUTION

To change the way the sa account logs on to windows, you need to go into the Configuration Manager (not the SQL Server management studio). From the configuration manager, click on ‘SQL Server Services’. From the list of services displayed on the right, select your database server, right click its properties and then change it to log on as Local System.

 

sa_account_logon_as_localsystem

 

Once, the sa account is able to logon as a local system account, it will be able to do all the things that require local filesystem access (such as attach, detach mdf files).

ALTERNATIVE SOLUTION

NOTE: If you do not want to change the sa account to logon as a Local System account, there is another option (a more tedious one). Since the sa account logs on as a Service, you will need to grant permissions to the Service group to every mdf file that you need to attach (Right click your mdf file, security and add the group called Service. This will also allow the sa user running under a Local Service account to access the mdf file.

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin