Sql-server – Will an AD Account inherit all the User Mappings to databases that it’s parent AD Group is mapped to

loginssql serversql-server-2008-r2users

If a Windows AD Group and an AD Account that belongs to that AD Group are both setup as Logins on a SQL server, will the AD Account Login inherit all the User Mappings that it's parent AD Group Login are currently mapped to?

Or would I need to re-apply the same User Mappings to the AD Account Login that are already setup on the parent AD Group Login?

Best Answer

Yes, a Windows account will have all of the permissions that are assigned directly to the login as well as any permissions that are assigned to any groups the account is a member of.

Permissions are cumulative, but DENYs will override. It would be difficult or impossible to accommodate some security needs if they were not.

See Effective SQL Server permissions when user is in several AD groups. The same principals apply to permissions assigned to a user and to a group they are a member of.

In general, DBAs that have been around a while will advise you to never start giving permissions to individual user accounts, as you can then end up with a long list of logins, and when employees leave you have to go clean them up. The preferred way is to only assign permissions to groups, and then when an employee leaves, or is hired, the DBA doesn't have to do anything as the request for access is granted by whoever manages the AD groups.

Related Solutions

Sql-server – Do I trace an Active Directory group based database login the same way as an AD user based login on SQL Server

Unfortunately, the group is not presented in the LoginName column => it is the individual user account (DOMAIN\user).

You can get the groups for a given member by executing:

EXEC XP_LOGININFO 'domain\groupname','members'
EXEC XP_LOGININFO 'domain\groupname','all'

This may be able to be wired up using a user-defined event.

Sql-server – User in multiple windows groups mapped to sql logins

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

Best Answer

Related Solutions

Sql-server – Do I trace an Active Directory group based database login the same way as an AD user based login on SQL Server

Sql-server – User in multiple windows groups mapped to sql logins

Related Question