Granting a Windows NT User or Group Access to a Database

To obtain access to a Microsoft® SQL Server™ database, a Microsoft Windows NT® user or group must have a corresponding user account in each database they need to access, and to which permissions are applied.

It is not necessary to add an individual user account in a database for each Windows NT user in a Windows NT group whose members all perform the same activities. Add an account for the group rather than for each member. When Windows NT group members need to work in a database, they are granted access through their membership in the Windows NT group; there is not a specific account for individual Windows NT users within the group. For example, a Windows NT group London\Managers contains the Windows NT user London\JoeB. The SQL Server system administrator grants login access only to London\Managers. The owner of database Accounts grants only London\Managers permission to access Accounts. Although London\JoeB does not have explicit permission granted to connect to SQL Server or access Accounts, he can connect to SQL Server and access Accounts due to his membership in London\Managers.

An individual Windows NT user needs to be added to a database only if the user performs activities different from other members of any Windows NT group; for example, special database administrative duties.

Note Users who are granted access to SQL Server through their memberships in a Windows NT group do not have entries for their individual Windows NT user accounts in the system tables. However, an entry is created for their individual Windows NT user accounts if they create objects, such as a table or a stored procedure, in a SQL Server database.

It is possible to grant a Windows NT user or group access to a database without explicitly granting the user or group access to connect to SQL Server first. Provided that the Windows NT user or group has permission to connect to SQL Server when they need to access the database, database access can be granted before login access is granted. For example, if the Windows NT group London\Managers is granted access to the Accounts database, but the user London\JoeB, a member of London\Managers, does not have login access to SQL Server, London\JoeB cannot access Accounts. However, as soon as London\JoeB is granted login access to SQL Server, he can also access Accounts.

To grant a Windows NT user or group access to a database