October 10, 2008 at 5:46 am
Hi all,
I'm a little confused, allow me to explain, lets say we have two servers, both with 5 databases on them, A,B,C,D and E.
Now on server A I have granted db_owner permissions to the developer group (which is an active directory group) on some of the database (A,B,C)
A quick test, bingo, works the developers can do their work on A,B and C, but when they try to access the D or E databases they get a message say "No, you dont have permissions" or words to that effect.
Right, now onto server B, exactly the same permissions granted, however when testing, the developers can access A,B and C without any issues. All good so far. But when they try to get to D or E, it allows them to open the database but they cant see any objects.
So in effect the permissions are being enforced on both servers but on one server they get a message say "you cant do that" and on the other they dont, they just cant see any objects etc.
What am I missing here?
Both servers are running 2K5 and have the same build numbers for SQL.
Thanks,
Nic
October 10, 2008 at 8:56 am
A few things.
1. If there is a guest user in the database, then the login will get mapped to there, can access the database, but they have the rights of the public role. That might not have rights to any objects.
2. There might be other groups/logins that are allowing them security. You can run http://msdn.microsoft.com/en-us/library/ms176097.aspx to find effective permissions for the users. That might clue you in.
Chances are security is different somehow on the servers.
October 10, 2008 at 9:08 am
Hi Steve,
Thanks alot for the reply.
As for the guest logins, they both appear to be identical on server 1 and 2. When I try it drop the login on server 2, database D I get the message below.
"User 'guest' cannot be dropped, it can only be disabled. The user is already disabled in the current database. (.Net SqlClient Data Provider)"
I agree there must be something different on the machines, but I cant for the live of me see what it can be, it does make sense though that it could be getting it's permissions from another login, but I cant see from where.
I'll keep investigating, thanks again for your help.
Jackal
October 10, 2008 at 9:46 am
Figured it out.
The connection was being made using the correctly restricted account, however there was another user group in the database (not on the server logins) that was allowing access. Hope that makes sense.
By removing the login that was not on the server this corrected the issue and everything works correctly now.
Thanks for your help Steve.
Jackal
October 10, 2008 at 10:03 am
My pleasure, thanks for the update.
Security can be a pain when people are a member multiple groups. Not a bad reason to watch which groups you give SQL access.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply