Creating Windows NT Groups and Users

To take advantage of integrated or mixed security for SQL Server, you must first create the Windows NT groups and users that will be authorized to access SQL Server. User Manager¾provided as part of the Windows NT operating system software¾is the tool you use to accomplish this.

Start User Manager by choosing its icon from the Administrative Tools program group. For information about using this tool, see the User Manager online Help. If you have not been granted access to User Manager, have a Windows NT Administrator use this tool for you.

When the Windows NT operating system is installed, an Administrators group is created. When SQL Server is installed, all users in this Administrators group are given system administrator privilege (SA) on the SQL Server.

The recommended way to implement SQL Server security integration is to create two locally defined groups to access SQL Server. You could, for example, use User Manager to create a local group named SQLUsers that has user-level privileges, and another named SQLAdmins that has system administrator privileges. Then you can add individual users to these groups.

Members of local groups can be locally defined users, users defined in the domain, or global groups defined in the domain. If you are using domain security, you will most likely want to add usernames that are defined in the domain, so that a separate set of Windows NT accounts do not need to be maintained on the Windows NT-based computer running SQL Server.

The following guidelines will help you smoothly set up accounts with the Windows NT User Manager that can then be mapped to SQL Server login IDs:

• When you create names for Windows NT users and groups, it is advisable to use only valid SQL Server identifiers. Do not use spaces, periods, or other characters that Windows NT allows but SQL Server does not, and limit group names to 30 characters or less. In addition, do not use the underscore (_), dollar sign ($), or pound sign (#) in the user or group names because these characters are used by SQL Server as special mapping characters.
• Avoid placing Windows NT users in more than one Windows NT group that can access SQL Server, because SQL Server does not allow overlapping group membership within databases. Keeping your Windows NT users limited to membership in only one group that has SQL Server access privilege will allow you to use SQL Security Manager to keep database groups consistent with Windows NT groups.
• Create one administrator-level group and one or more user-level groups for the accounts that will access SQL Server. The groups should correspond to the security levels that you want to grant in your SQL Server databases (for example, OrderEntry and OrderEntryManagers). If you want your Windows NT administrators to be the same as your SQL Server system administrators, you can leave the default permissions created by the setup program. Otherwise, you can create a separate group for SQL Server administrators and grant that group administrative privilege on both Windows NT and SQL Server. After you create a separate administrators group for SQL Server system administrators, you can revoke privilege from the Windows NT Administrators group.