I recently ran into a strange situation – I was expecting an “access denied” but it didn’t happen (yes – security guys are strange people ;). Here’s the long story:

I was writing some test code for LINQ to SQL (see here) in ASP.NET. Since this was on a freshly installed box I was expecting an access denied since I hadn’t created a SQL login for Network Service yet. But it worked – I could successfully query (and update) data in all databases. Shock.

After some investigation I found the reason for this behavior. Since I was using SQL Express, the SQL instance was running as Network Service (the default). Furthermore setup creates a Windows group for SQL Server service accounts (e.g. MACHINESQLServer2005MSSQLUser$…) and puts Network Service in there. It turns out that this Windows group is mapped to a SQL login with a server role of sysadmin…

This means (on my machine) that all SQL clients running as Network Service (or can get an impersonation token for that account) have sysadmin privileges in the SQL Server installation. Or more generally – when a client can use the same Windows account as SQL Server itself – it will get sysadmin privileges

I thought I might point this out, since running SQL Server and ASP.NET as Network Service seems to be a pretty common configuration.

The moral of the story: Always create dedicated service accounts for SQL Server (or every service you install).

btw – the full blown SQL Server installation specifically asks you for the account to use (but also gives Network Service as a choice).