May 11, 2011 at 8:52 am
I'm trying to get my head around security for a group of users, but it seems like I'm doing something just a little wrong. Here's what I've done so far:
1) In the 'Reporting' database, I've created a role called 'Sales' that has explicit SELECT permissions on various tables within the database. In addition, I've given the 'Sales' role EXECUTE permission at the database level. To me, it makes sense to explicitly give all of the necessary permissions to the role (as opposed to the select permissions to the role and the execute to the user Domain\Sales user referenced in #2).
2) I've added 'Domain\Sales' AD group as a server login (with a server role of 'public'), mapped it to the 'Reporting' database, and assigned it to the 'public' (default) and 'Sales' roles (I think when I created the login for Domain\Sales in SSMS, it actually created a user with the same name, and that's technically what was added to the public and Sales database roles, correct?).
So, as you can see, all of my permissions are defined in the 'Sales' role, and therefore I would think executing the following would bring back results instead of
"Cannot execute as the database principal because the principal "Domain\Sales" does not exist, this type of principal cannot be impersonated, or you do not have permission."
EXECUTE AS USER = 'Domain\Sales'
SELECT * FROM People
REVERT
Here's how I thought the permissions were inherited:
Domain\JohnSmith (actual person) --> Domain\Sales (AD group) --> Domain\Sales (Server login) --> Domain\Sales (Database user) --> Sales (Database role with permissions) --> People
A few notes:
-Domain\JohnSmith is NOT a server login. Does it need to be? I thought that since his network accounts belongs to Domain\Sales, a separate login is not required.
-The 'Sales' role has at least SELECT permissions on the People table, so I don't believe that's the reason why I'm getting the error above.
If anyone can see a flaw in the way I perceive the permissions to work, please set me straight. Thanks for the help.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 11, 2011 at 9:47 am
As far as I can see you've done this correctly. To use as an example I'll describe how i give permissions to non dev users on a test database.
I have a role users in the database - exec on all procs is granted to this role -- e.g. grant exec on dbo.myproc to users;
domain group ourdomain\someusers added to the server as a login.
I then add rights for that user in the database .. usually datareader so they can look at tables and users so they can run procs. ( normally there is no table access in the databases at all )
so by my figuring you're doing things right.
so add domain group to server as login.
for a database grant to that group the role rights.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 11, 2011 at 9:58 am
I'm with Colin. I think you are right and my understanding of permissions as you've listed them is correct. JohnSmith does not need a SQL login as he's authenticated with the group.
However I think the ExecuteAs doesn't work with the groups as you've outlined it. Can you create a quick AD user in that group and log in as them?
May 11, 2011 at 10:33 am
I created a test AD user and added it to the Domain\Sales group. Then, I started another instance of SSMS by using Run As... and specified Domain\testuser and its password. I tried to connect to my database instance and it said "Login failed for user Domain\testuser" (Error 4064). This is really odd to me because I thought, at the very least, I should be able to connect, given that the testuser is part of the Domain\Sales group.
I created a login "Domain\testuser" on the server and mapped it to my 'Reporting' database and then was able to connect.
So, I said to myself, maybe I had forgotten to go to the properties of Domain\Sales server login and map it to 'Reporting.' Sure enough, the "Map" checkbox for the 'Reporting' database wasn't checked, so I checked it...BUT then I went to properties again to find that it was unchecked again.
So still, the only way I have been able to connect as testuser is if I add a separate server login and map it to a database, which is a whole new problem.
Is there some restriction that if you create a server login based on an AD group, you can't have it mapped to a database. That doesn't seem right at all. I just wonder why it won't stick, and if that's the reason testuser ultimately can't connect.
Any thoughts?
Mike Scalise, PMP
https://www.michaelscalise.com
May 11, 2011 at 12:32 pm
I'm not sure about the execute as, but basically the AD group you create the server login for should conatin as a member the user you wish to have permissions.
You could bypass the role as a test and just grant the AD group rights in a database as a test. Start extreme and make it dbo , then work backwards.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 11, 2011 at 1:04 pm
Ok, here's what I did:
1) I disassociated "Domain\Sales" from the "Sales" database role (that way I could test the user itself).
I tried logging in as Domain\testuser and received an error saying that it could not connect to the default database (which was 'Reporting')
2) So I tried changing the default database for "Domain\Sales" from 'Reporting' to 'master'
Now I'm able to log in with Domain\testuser just fine. However, the user couldn't access any database, including 'Reporting'
I think this is still because Domain\Sales can't be mapped to 'Reporting' (like I said, the mapping doesn't stick). So, in a way, I'm a little bit further ahead in that I can connect with Domain\testuser without it having to be listed in the server logins. However, in order for me to access any database, it seems as though Domain\Sales needs to be able to be mapped to a database (and actually have it stay that way).
What would cause Domain\Sales not to be able to be mapped to a database?
Also, not sure if this matters, but making Domain\sales part of the sysadmin server role allows me to do everything I need, but I'm looking to set the database level permissions, not server permissions.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 12, 2011 at 3:54 am
yes if you add a user or an ad group you can't set the default database unless you've added the user to that database.
so add your domain user group in create login, then go to user mapping tab and tick the database(s) you wish that user to connect to. They'll only have public rights at this stage which may well mean they can't do anything at all. Then click datareader in the page below. now click ok button and any member of that group should have the read writes on that database. leave the default database as master.
There is only one scenario I can think of where this can go wrong and this is if at another level the group or user has had deny set. e.g. a user is in two groups both of which are logins on the server. in one group read access is denied explicitly in the other datareader is granted. In this case the deny will block the read.
As I said I can't see you're doing anything wrong. Do you have any policies or restrictions set on the server?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 12, 2011 at 6:35 am
yes if you add a user or an ad group you can't set the default database unless you've added the user to that database.
Are you sure? I can click on any login and set any database as the default. Whether they're able to access it after trying to log in is a different story. I think that's why I received that cannot open user default database error with Domain\testreguser.
so add your domain user group in create login, then go to user mapping tab and tick the database(s) you wish that user to connect to. They'll only have public rights at this stage which may well mean they can't do anything at all.
This is the problem I'm having. When I go to the mapping tab, I tick the database I want the user to connect to (for roles, public is already selected, and in the past I've clicked the Sales database role that I created), then click OK to the login properties box. If I right click on the login and go back to the login properties box, then to user mappings, no databases are ticked.
Are you able to bring an AD group in as a login and map it to a specific database (and have it stay that way)?
Mike Scalise, PMP
https://www.michaelscalise.com
May 12, 2011 at 7:04 am
Actually, I may have figured it out.
I think what had happened is that I originally created a login Domain\Sales, then mapped a user with the same name to 'Reporting.' Then, I eventually deleted the Domain\Sales login, leaving the Domain\Sales user in the 'Reporting' database.
Later on, I created the login Domain\Sales again, then tried to map it to the 'Reporting' database. I think that it saw a username with the same name already there and did nothing (which is why the mapping didn't stick). I think the SIDs didn't match, so I had a new login and an orphaned user, both with the same name.
I deleted the Domain\Sales database user, then went to the login and ticked the box to map it to 'Reporting.' It created the database user and kept the tick next to the 'Reporting' database in user mapping.
I'm able to log in with Domain\testuser and get all of the appropriate permissions now.
Thanks for all of the help, guys.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 12, 2011 at 3:00 pm
yup that'd do it - missmatched sids the issue you have to resolve with log shipping and standby servers!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply