Security Principals missing in SQL Server 2008 R2

Question

SQL Server 2008 R2 on Windows Server 2012 R2, none of the built in security principals are appearing when trying to view user accounts and assigning permissions.

They are available on the service to assign the LOGON as value. Specifically I am looking for NETWORK SERVICE.

FOR CLARIFICATION: I am referring to the local accounts IUSER, NETWORK SERVICE, LOCAL SYSTEM, etc.

I just completed a parallel installation on a demo server with no issues both 2012 R2 boxes. The one with issue is a VM.

And just to confirm, the accounts DO IN FACT exist on the server. So they were created during the OS install.

I uninstalled an reinstalled both SQL Server and SSMS, both back up and running, same issue.

Any ideas on how to get those back?

Answer 1

IUSR isn't a default principal. That's an IIS user account in IIS 7 for anonymous access. SQL Server doesn't assume IIS needs access to the instance, let alone that IIS anonymous users need access. Beginning with IIS 7.5, that was considered a legacy account, IIRC, and isn't even created by default. If you have an account actually named "IUSER", then I imagine that's an account specific to your environment.

You may not see NETWORK SERVICE or LOCAL SERVICE since those accounts were virtualized in Windows Server 2008 R2 and later. SQL Server 2012 doc makes this more clear (scroll down to the bit about "New Account Types Available with Windows 7 and Windows Server 2008 R2"). SQL Server 2008 R2 doc likely wouldn't have been written with changes to Windows Server 2008 R2 wholly in mind. I can't seem to find clear documentation about what SQL Server 2008 R2 does on these systems, but on my installations at least (SQL 2008 R2 SP1 media installed on 2008 R2 SP1 installations, updated later) these older accounts aren't present at all while the virtual accounts are.

You should see NT AUTHORITY\SYSTEM, NT SERVICE\MSSQLSERVER (or NT SERVICE\MSSQL$InstanceName) group, and NT SERVICE\SQLSERVERAGENT (or NT SERVICE\SQLAgent$InstanceName) group. Those last two are the virtual accounts that are references to the service accounts used to start those services configured in SQL Server Configuration Manager. You should also see ##MS_PolicyTsqlExecutionLogin## and ##MS_PolicyEventProcessingLogin##, but they're probably disabled.

If you included SQL authentication, you should see the sa account, too. You should also see whatever accounts you added explicitly during the install. Beginning in SQL Server 2008, I think that's it for defaults. If you upgraded an existing instance, you may see different things, otherwise I would expect those accounts were explicitly added during or after the install on the other server.

Bottom line, you need to create missing server logins and associated database users yourself, but you may not be able to create NETWORK SERVICE or LOCAL SERVICE since they may not even exist.