Befriending Dragons

Transform Tech with Anti-bullying Cultures

How to use PsGetSid for SQL Server

Leave a comment

To translate a SID to a name or vice versa, you can use this tool from sysinternals:

 

 

PsGetSid v1.43

http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx

 

You can use it to translate the SID in these registry keys into the SQL Domain Groups or local groups used for SQL Server 2005/2008 instance permissions. The SID is stored in this key:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.XSetup

 

AGTGroup (Setup grants this domain group the permissions needed by the SQL Server Agent service account)

SQLGroup (Setup grants this domain group the permissions needed by the SQL Server service account)

FTSGroup (Setup grants this domain group the permissions needed by the Full Text Search service account)

ASGroup (Setup grants this domain group the permissions needed by the Analysis Services service account)

 

If you’re not sure what MSSQL.X value to use, look up your instance name in

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL

 

SQL Server creates these keys and populates them with SID values. The service accounts you specify during setup are added to the groups.

 

On a standalone system those SIDs map to local groups created by setup including:

SQLServerMSSQLUser$ComputerName$InstanceName

SQLServerSQLAgentUser$ComputerName$InstanceName

SQLServerMSOLAPUser$ComputerName$InstanceName

SQLServerDTSUser$ComputerName

SQLServerSQLBrowserUser$ComputerName

SQLServerMSSQLServerADHelperUser$ComputerName

 

On a cluster, the cluster aware components SQL, AS, and Agent don’t have local groups created. Instead you pre-create global groups (for SQL 2008 on Windows 2008 you have the option to use Service SIDs instead) and specify the group names during setup. Then setup grants the permissions to the global groups. Since often the account you are using for setup doesn’t have permissions to add accounts to groups you may want to have someone with appropriate permissions pre-add the service accounts to the groups.

 

If you ever need to find out what the name of the local or domain groups are, you can use the above PsGetSid tool to find that. For example, you may want to move a system to a new piece of hardware configured exactly like the old one, then you will remove the old system. You will need to know what groups were used for that instance of SQL Server (you are following good security practices and using a unique group for each instance and each service within each instance to reduce your vulnerabilty across systems if one instance is hacked, right?) so that you can reuse that group on the new system. A disaster recovery scenario is another situation where you will want to know how to map the SIDs to the names. You may want to back up these keys.

 

915846  Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;915846

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s