Adding Windows-authenticated logins or groups to a SQL Server database

To add a Windows-authenticated login or group to Microsoft SQL Server, you first create a domain or local login or group, add it to the SQL Server instance, add a user to each database that the user needs to access, and map the login to it. Additionally, create a schema for the database user if the user will own data.

Note:

You cannot use Windows-authenticated users if you are using an ArcSDE application server that is installed on a different server than your SQL Server instance. See technical article 36420 on the ArcGIS Resource Center for details.

Steps:

1. Connect to your SQL Server Instance.
2. Navigate to the Security folder, right-click Logins, then choose New Login.
3. On the General page, click the Search button to browse for a local or domain login or group.

   The Search button opens the Select User or Group dialog box, allowing you to query for local or domain users and groups. If you are adding a Windows group, be sure the object types include groups.

   

4. Click OK to close the Select User or Group dialog box.

   The user or group you selected is added to the Login - New dialog box.

5. Be sure the Windows authentication option is selected.
6. You can alter other configurations on the General page or accept the defaults.

   See the SQL Server Books Online for instructions if you want to alter these configurations.

7. Click OK to close the Login – New dialog box.
Now add a user to the database to which you want the login or group to have access.
8. In the Object Explorer, expand the Database folder.
9. Expand the database that the login or group needs to access.
10. Expand the Security folder under the database.
11. Right-click the Users folder and click New User.
12. Type a name for the user in the User name field.
13. Click the ellipsis button next to the Login name field to browse for the login you added to the SQL Server instance in the first 7 steps.
14. If you are adding an individual login rather than a group, and the user will own data in the geodatabase, type the schema name in the Default schema field.

    Remember, the user name and schema name must be the same.

    For Windows groups, a matching schema will be created for any group member the first time the user creates data in the geodatabase. The schema name will be the same as the login name, not the name of the group. This is done automatically; you do not have to create the schema ahead of time.

15. You can place the user in a role by checking one of the roles in the Role Members list.
16. Click OK to close the Database User – New dialog box.
If you added a Windows login that will own data, add a matching schema. As stated previously, if you added a Windows group, you do not need to add a matching schema.
17. If the user will own data in the geodatabase, right-click the Schemas folder in the Object Explorer and click New Schema.
18. Type the same schema name you typed in the Default schema field for the user.
19. Click Search to choose the schema owner.
20. Type the user name and click Check Names.
21. Click OK to close the Search dialog box.
22. Click OK to close the Schema – New dialog box.