Identify and resolve SQL Server problems BEFORE they happen with SQL diagnostic manager

SQL Server Service Account Privileges

By: Chad Boyd | Read Comments | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: 1 | 2 | 3 | 4 | More

Problem
SQL Server 2005 Books Online indicates that the SQL Server service account requires permission to start the following related services (among many other requirements): SQL Server Active Directory Helper and SQL Server VSS Writer services. How can I verify service-related permissions for the SQL Server service account?

Solution
Obviously, the service really would not NEED permissions to start these services if you were not going to make use of them.  Let's assume for the moment that we are going to do so, and that you want to determine first of all if the SQL Server service account has these permissions and/or what permissions the account has to services on the machine in question.

A tool called "AccessChk" will allow you to see this type of information, along with lots of other access-related information for given accounts. You can find information on the tool at the following link - http://www.microsoft.com/technet/sysinternals/utilities/AccessChk.mspx.

As an example, the following command line will give you effective permissions to all services on the local machine for an account named "LTCBOYDMS\sqlService":

To determine service-related permissions, the -c option must be used. Without it, you get by default ACL information for files/folders/shares and other information, which is also handy in many situations. If you specify the wildcard character (i.e. *), you will get permission related information for the given account against ALL services on the machine.  If you are looking for only a specific service information, you can replace the wildcard with the appropriate service name.  For example, to get effective permissions to only the SQLWriter.exe service (the executable called for the SQL Server VSS Writer service), you could use the following command:

An example command for the Active Directory Helper service, would be something like the following:
 

The output from the command should look similar to the following:

 

In the output, the "RW" designators at the beginning of each line indicate Read/Write privileges (if present), then the service name, then using the -v option will provide you the additional output shown below each service above. The SERVICE_ALL_ACCESS implies all access, if you don’t have that, you should see individual service related privileges like in the line above for the "Pml Driver HPZ12" service.

Next Steps

• The Access Check application provides a means to have a good understanding of the privileges assigned to the SQL Server service accounts at a Windows Service level.  On servers with sensitive data, it is important to check items like this to ensure that the appropriate rights are assigned.
• Consider the Access Check application as a tool in your tool box for assessing and troubleshooting your overall SQL Server security.
• As the SQL Server service accounts change in your environment, keep this tool in mind to validate that the correct account and privileges are setup.
• Check out the following related MSSQLTips:
  • Guest User Account
  • Giving and removing permissions in SQL Server
  • When not to use the sa password
  • <-- SQL Server Security Note --> BUILTIN\Administrators Group
  • SQL Server 2005 - Enabling xp_cmdshell 
• Special thanks to Chad Boyd of my MSSQLTips.com community for this tip.

Related Tips: 1 | 2 | 3 | 4 | More | Become a paid author

Last Update: 2/20/2007

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Optimize your SQL Server storage: compress live databases by up to 90%. Download a free trial. SQL Server Issues? Not sure where to turn for answers? Innovative SQL DBA consultants Join the over million SQL Server Professionals who get their issues resolved daily. Are you waiting on SQL Server? Learn about these DMV's.