SQL server permissions during installation

Posted on

Question :

I’m trying to find out when a SQL server installation grants permissions on the OS.

I know that the following permissions are giving during setup msdn article about permissions.

However what I don’t know is how SQL server will react if the permissions are there, but granted through a group. My problem is that the OS group policies do not allow for several of the required permissions to be granted. And we are going to add the users to a group, that is then included in the group policy.

Will SQL server correctly detect that the required permissions are present, and skip adding the users individually? Or will the installation end on error because it can not grant the required privileges?

We’re planning to test this soon, however due to time constraints I was hoping some-one here has experience with this issue, and can give me an answer.


Update: I’m quickly adding some information in the hopes that someone can explain the observed behavior to me…

We are able to install SQL server, despite group policies preventing nearly all the permissions mentioned in the before linked MSDN article. However if we then try and run an SSIS package, or certain server wide jobs, we receive the error that SQL Server lacks the required privileges.

Why was SQL Server able to install, without error even, despite not being able to add the sql server agent and service users to the User Right policies it needs according to the MSDN documentation?

Currently SQL Server is running, despite the user not having local permissions, and not having the Log on as a service permissions… Am I wrong in my understanding of why these permissions are required?

Answer :

I will say up front that I do not know why your company would have policies that lock down the OS so much that a Microsoft product cannot be installed and provided the appropriate permissions during an installation. I would think they might be doing that backwards. I supported the military as a contractor for many years and that included secured networks, we did not lock down the OS until after all software was installed on the server. We at most wanted to ensure the software installed itself appropriately without any issue.

Outside of that, it is going to depend on what permissions you are not allowing to be set. If it is being restricted on what OS level permissions are set within the local group policy that are going to prevent the services from starting the installation will likely fail.

Shorting SQL Server services the documented permissions required to operate might affect your supportability with Microsoft. Just something to keep in mind.

Edit/Update

There is no in-depth doc that is going to go through what all the installation process does on a server. You can weed through the installation log under the default directory here: C:program filesMicrosoft SQL Server110Setup BootstrapLog. The 110 would be based on which version of SQL Server you are working with: 100 = SQL Server 2008/R2; 110 = SQL Server 2012; 120 = SQL Serve 2014.

I don’t know anyone has gone through and determined the outcome of each little affect of an OS level permission not existing. All that is needed to understand is that permissions assigned according to documentation show what is required for a supported installation of SQL Server. You are basically going to experience sporadic issues based on what you need to do. Every permissions has a purpose, you just may not have reached or hit that purpose as of yet.

I’m trying to find out when a SQL server installation grants permissions on the OS.

During installation during Database Engine Configuration please make sure to add yourself as Administrator for SQL Server Administrator so that you have an access to Specific instance after installation.

Installation Screen
During this step you can include user groups to access SQL Server Instance.

enter image description here

You can always add windows groups to SQL Server and allow access depending on their privileges.

As long as you have user added to SQL Server you will not get any error during installation.

Hope this helps you.

Leave a Reply

Your email address will not be published. Required fields are marked *