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
((table3.col6)=0)
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. :-)
