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

How to determine service related privileges for Sql Server service account

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: More

I had a customer inquire as to how to verify service-related permissions for the Sql Server service account...i.e., Books Online indicates the Sql Server service account requires permission to start the following related services (amoung many other requirements): Sql Server Active Directory Helper and Sql Writer services.  Obviously, the service really wouldn't NEED permissions to do so if you weren't going to make use of said services, but 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.

There's a tool called "AccessChk" which 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":

 accesschk "LTCBOYDMS\sqlService" -vc *

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 handy also). If you specify the wildcard character (the *), you'll get permission related information for the given account against ALL services on the machine...if you're 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 service (the service name for the Sql Writer service), you could use the following command:

 accesschk "LTCBOYDMS\sqlService" -vc "SQLWriter"

and, for the Active Directory Helper service, something like the following:

 accesschk "LTCBOYDMS\sqlService" -vc "MSSQLServerADHelper"

Output from the command should look similar to the following:

 C:\Projects\Toolbox\accessCheck>accesschk.exe "DUMMYDOMAIN\svcSqlService" -vc *

 AccessChk v2.0 - Check account access of files, registry keys or services
 Copyright (C) 2006 Mark Russinovich
 Sysinternals - www.sysinternals.com

 RW Alerter
  SERVICE_ALL_ACCESS

 RW ALG
  SERVICE_ALL_ACCESS

 RW MSSQL$SQL2000
  SERVICE_ALL_ACCESS

 RW MSSQL$SQL2005
  SERVICE_ALL_ACCESS

 RW MSSQL$SQL2005B
  SERVICE_ALL_ACCESS

 RW MSSQL$SQLEXPRESS
  SERVICE_ALL_ACCESS

 RW MSSQLServerADHelper
  SERVICE_ALL_ACCESS

 ...

 R  Pml Driver HPZ12
  SERVICE_QUERY_STATUS
  SERVICE_QUERY_CONFIG
  SERVICE_INTERROGATE
  SERVICE_ENUMERATE_DEPENDENTS
  SERVICE_PAUSE_CONTINUE
  SERVICE_START
  SERVICE_STOP
  SERVICE_USER_DEFINED_CONTROL
  READ_CONTROL

 ...

 RW SQLBrowser
  SERVICE_ALL_ACCESS

 RW SQLWriter
  SERVICE_ALL_ACCESS

 ...

 C:\Projects\Toolbox\accessCheck>

In the output, the "RW" designators at the beginning of each line indicate Read/Write privs (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 privs like in the line above for the "Pml Driver HPZ12" service.

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.

Related Tips: More | Become a paid author

Last Update: 10/15/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

"SQL diagnostic manager delivers response in minutes, not hours!" It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. Make the most out of SQL Server - Guaranteed Results - Innovative SQL Server DBAs Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Learn SQL Server 2008, Performance Tuning, Development, Administration, Replication and more - free webcasts