Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two C# apps with Windows Auth to Microsoft SQL Server 2008 R2.

For every app:

  • SQL login based on Windows Group is created
  • SQL server login is mapped to SQL database user
  • user is member of a role
  • role has granted permissions to tables, views, procedures, etc.

Question is:

If I add a single windows user as a member to both windows groups (with are "mapped" to different SQL logins, which are mapped to different SQL users, which belongs to different roles and so on..) - could user run both apps without any problems or only one of them will work properly?

share|improve this question
    
This belongs on Database Administrators SE. Flagged for migration. –  Thomas Stringer Jun 15 '12 at 17:59
add comment

migrated from stackoverflow.com Jun 17 '12 at 5:07

This question came from our site for professional and enthusiast programmers.

1 Answer

If I am understanding your question correctly, then yes the user will be able to run both applications with database connection issues.

If User1 is part of Windows Groups Group1 and Group2, and Group1 is mapped as a SQL Server login, as well as Group2 is mapped to a different SQL Server login. And if Group1 is mapped to a database user in Database1 and Group2 is mapped as a database user in Database2, then User1 should have access to both Database1 and Database2. And if Group1 and Group2 are both mapped to TheOnlyDatabase, then the user will have the accumulations of both database users' permissions.

This will return only one of the database users (as expected):

select user_name() as database_user_name

This will return 1 for both queries if the users is a member of both Windows Groups:

select is_member('Domain\Group1') as member_of_group_1

select is_member('Domain\Group2') as member_of_group_2
share|improve this answer
    
90% match :) There is only one Database. Group1 and Group2 are mappped to "TheOnlyDatabase". –  huhu78 Jun 15 '12 at 18:05
    
Another words.. Group1 can run ProcA, ProcB, ProcC, Group2 can run ProcD, ProcE, ProcF. App1 uses ProcA, ProcB, ProcC, App2 uses ProcD, ProcE, ProcF. Users of App1 are members of Group1, users of App2 are members of Group2. Some of users should be users of App1 and App2. Will it works if I make them members of Group1 and Group2? –  huhu78 Jun 15 '12 at 18:10
    
@huhu78 Ok, I see now. Yes, I believe it should be the accumulation of the rights of users that the users have. So User1 using App1 and App2 should be able to run all of those procs. –  Thomas Stringer Jun 15 '12 at 18:33
    
That is correct. Permissions are always cumulative, so the windows user will have the sum of the permissions based on the two sql logins from the security groups. It's no different than file permissions based on two different groups. The only thing to watch out for is if one of the groups was denied permissions to an object that was needed by the other group. Deny will always take precedence, so if group1 was denied permissions to table t1 but app2 required the use of t1, then your user that is a member of both groups would not be able to run app2. –  sqlbattsman Jun 17 '12 at 19:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.