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. 🙂
Best Answer
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.