July 20, 2011 at 8:57 am
I have a SQL 2008 test server with about 30 different app databases on it. I have 2 developers who need access to 4 databases recently migrated to this server from a SQL 2005 server. The test application works fine with a dedicated SQL auth login. They need access for using SQL Management Studio. So I create a Win auth login on the server for each of them and map them to the 4 databases with db_owner on each. They connect to the server using SM fine, but when they expand the databases list, they get an error:
"The server principal XXXXX\Username is not able to access the database "CONFIG" under the security context."
CONFIG is a database for another application. No rights to this database are assigned.
To workaround, I gave them access (public) to this database and then added them to denydatareader role in order to prevent them from seeing any data. Once I did that, they got an error on another database (DTFM) when starting SM. I added them to that one too (public/denydatareader) and then the errors stopped. They can access their 4 databases fine. They see all the other 30 databases in the database list, but if they click on one, they get a "no access" message. Fine. If they click on CONFIG or DTFM they see the objects tree inside, but cannot see any objects or data, which works. But I don't understand how they are getting tripped up for these two databases (and not any others). I also tried creating a SQL auth login for one of the guys and had the same behaviour.
Any ideas?
July 21, 2011 at 4:05 pm
Nothing immediately comes to mind. It would be interesting to know the root cause. How much time do you have to fiddle with this to find the root cause? Time enough to revert their rights in the two DBs, start Profiler with a filter on their logins and have them try again.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 22, 2011 at 8:58 am
Only thing that comes to my mind is that there is some kid on deny rights on these two particular DB. It could be a login trigger on these two DB.
-Roy
July 24, 2011 at 12:55 pm
Any views or procs that reference objects in your CONFIG database?
anything that uses cross database ownership chaining?
July 25, 2011 at 1:21 am
thanks for the suggestions. i've checked everything but nothing comes up. in fact, it's even stranger now, i added a sql auth login for one of the guys today and there is no error popping up now. i haven't had a chance to re-test with their win logins.
July 25, 2011 at 8:55 am
I suspect there is something in the other four databases that reference the CONFIG database. Trying running a SQL Profiler trace filtered on the database object CONFIG, and then have the developers do whatever throws the error. Also script out all object definitions (everything but data) from the four databases and search the scripts to locate any reference to CONFIG. Just searching the scripts contained in source control may not be enough to identify the reference, because a DBA or even some 3rd party tool may have altered some object.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply