I've run across a very odd behavior on SQL Server 2008R2.

I have a third-party application which acts as a SQL Server client. I have a subset of my users who should have access to this application. I created an Active Directory group, call it "DOM\appusers", with a couple of test users. I created a SQL Server login by the same name, created a database, call it "appdb", created a user on the appdb database, "DOM\appusers", associated with the login, and assigned the "db_owner" role to this user.

Most operations work. But "some" of them get errors. In particular, one query fails:

SELECT x,y,z
  INTO tmpTable
  FROM (table1 INNER JOIN table 2 on table1.col1 = table2.col1)
       INNER JOIN table3 ON table1.col2 = table3.col2
 GROUP BY x,y,a,b,c
HAVING (((table2.col4)=0) AND
        ((table3.col5)=0)) AND

This was pulled out of SQL Profiler, where it flagged the query with an error, but there was no more detail about the error, and the application is unhelpful, so I can't tell you exactly what error was thrown.

I'm no SQL expert, so I don't know what's special about this query, or why it should require permissions beyond db_owner, but this isn't really a question about SQL queries.

What's really weird to me is that if I bypass the group, I get different results. If I delete the SQL server login and user detailed above, then create a new login and user referencing an Active Directory user directly, and assign it the same db_owner role, then the above query works. Indeed, all queries work as expected.

This was strange enough that I thought it had to be an error on my part. I did the same experiment three times, with the same result. I verified that the test users have no access to the database without one of the above setups. I tried assigning the server-wide "sysadmin" role to the AD group, and the query started working. Obviously, I can't leave it that way, but it was a data point. I also created a login using SQL Server authentication instead of Windows authentication, and that worked too.

So, the obvious question is this: What is different about permissions when a user gains them through a group vs "directly" (insofar as anything is "direct" with MSSQL's extra layers of login and database user)? Does db_owner not mean the same thing for a group as for a user? Is this just a bug? Is there a fix? I'd rather not have to add each and every app user to the SQL Server user/login list individually, but that's the only workaround I've thought of so far. Is there a different solution?

Thanks in advance for any help. :-)

  • Is SQL Server running as a domain user? – Katherine Villyard Aug 7 '13 at 19:06
  • No, it's running as the local "NETWORK SERVICE" account. – Rick Koshi Aug 10 '13 at 15:17

It turns out that this was not exactly a "permissions" question, despite the error message I was getting.

To make a long story short, a SQL server user that's correlated to a Windows group does not have a default schema, and there's no way to configure one. It's a limitation of SQL Server 2008R2 (and probably earlier versions) which has been corrected in SQL Server 2012.

As such, the application's query was trying to create its tmpTable in a schema in which it did not have permission. The reason it worked when I created a SQL Server user connected to a single Windows user is that, in that case, it assigned a default schema of "dbo", which was the correct schema. The user had permission to write there, and everything worked.

Ultimately, then, the only solution for me was/is to create individual SQL Server users and logins connected to the individual Windows accounts. It's an ongoing maintenance hassle, but it appears to be unavoidable with this version of SQL Server.

| improve this answer | |

I'm pretty sure the problem is that the service account (you said it was running as "NETWORK SERVICE") doesn't have the necessary credentials to look up domain group members. If a domain user is verified by the OS as really being DOMAIN\user, SQL Server trusts this, but if SQL Server needs to look up whether DOMAIN\groupmember is really a member of DOMAIN\group, this fails because the service account doesn't have the necessary permissions to look up this information. So the group member is able to log in because the OS says it's okay, but when it's checking permissions on objects later the lookup fails.

Try running the SQL Service as a domain user/domain service account and see if that helps.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.