Creating SQL Server Logins

See Also

Users gain access to SQL Server databases through a login that establishes the ability to connect (authentication). SQL Server uses Windows NT authentication, therefore, each SQL Server login must be associated with a valid Windows NT account. Then, each SQL login based on the Windows NT user or group account can be granted permissions to connect to SQL Server.

Before trying to create new SQL Server logins, be sure your users have valid Windows NT accounts. For details, see Creating Windows NT User and Group Accounts.

If you have system administrator privileges on SQL Server, you can create and modify SQL Server logins using the following methods:

For additional information about SQL Server logins, see "Logins" the SQL Server Books Online.

Once your SQL Server logins are established, you can create your database roles. For details, see Creating Database Roles.

Naming Rules

A user or group name cannot be identical to any other user or group name of the domain or computer being administered. It can contain up to 20 uppercase or lowercase characters except for the following:

" /  \ [  ] :  ; |  = ,  + *  ? <  >

A user or group name cannot consist solely of periods (.) and spaces.

For additional information about naming conventions for SQL Server logins, see the SQL Server Books Online.

Groups

There are no groups in SQL Server version 7.0. The groups from earlier versions of SQL Server have been replaced with roles, which are more powerful. However, you can manage SQL Server security at the level of an entire Windows NT group.

If you create an SQL Login and specify the name of a Windows NT group, then all members of the group can connect to SQL Server using Windows NT Authentication. For details, see Creating Windows NT User and Group Accounts.

Creating SQL Server Logins with Enterprise Manager

By default, SQL Server Enterprise Manager is installed by SQL Server Setup as part of the server software on computers running Windows NT and as part of the client software on computers running Windows NT, Microsoft Windows® 95, and Windows 98.

To grant a Windows NT user or group account access to SQL Server

  1. From the Start menu, select Programs, then Microsoft SQL Server 7.0, and then Enterprise Manager.

  2. Expand a server group, and then expand a server.

  3. Expand Security, right-click Logins, and then click New Login.

  4. Select Windows NT Authentication.

  5. In the Name field, enter the Windows NT account (in the form DOMAIN\User) to grant access to SQL Server.

  6. Optionally, in the Database list, select the default database for the user to connect to when logging into SQL Server.

  7. In the Language list, select the default language in which messages are displayed to the user.

  8. Click OK.

Creating SQL Server Logins with Microsoft Access

From an Access data project, you can administer basic security tasks such as adding, editing, or deleting server logins; creating database users; and creating database roles. You must have SQL Server installed on the same computer as the Access project for these commands to work.

To grant a Windows NT user or group account access to SQL Server

  1. Open your SQL Server database in Access using an Access data project. For details, see Opening your Solution in the Access Workflow Designer in the Access Workflow Designer Developer's Guide.

  2. From the within the Access data project, select Tools menu, and then Security, Database Security.

    Note   When an Access data project is loaded and you are working in the Access Workflow Designer, you can access the Security option from the Tools menu in the designer as well.

  3. On the Server Logins tab, select Add.

  4. Select Windows NT Authentication.

  5. In the Name field, enter the Windows NT account (in the form DOMAIN\User) to grant access to SQL Server.

  6. Optionally, in the Database list, select the default database for the user to connect to when logging into SQL Server.

  7. In the Language list, select the default language in which messages are displayed to the user.

  8. Click OK.

Creating SQL Server Logins with the New Team Solutions Wizard

Access Workflow Designer provides the opportunity to create SQL logins during the creation of a team solution based on a template. However, this functionality succeeds only when the person creating the solution has system administrator privileges on the SQL Server and if the SQL logins already have valid Windows NT accounts. A member of modAppOwners does NOT have the ability to create SQL Server logins unless the server administrator also gives the modAppOwners group system administrator privileges on the SQL Server.

When you create a team solution based on a template, the new solution is based on values stored in the template, such as the database schema, workflow processes, the Web site, database users, roles, permissions, and even data.

If you are using a template created by a different team or at a different location, it is probable that you will be required to add SQL Server logins for individuals who will be using this new solution. Therefore, the wizard makes it possible for you to create SQL Server logins, assign these logins as database users, and designate roles for them.

To create SQL Server user logins using the New Team Solutions wizard

  1. Open the Team Solutions Manager.

  2. Select a Server, and click Refresh.

  3. On the Solutions tab, click New Solution.

  4. Select a team template, and proceed through the wizard.

  5. On the Solution Roles page, select New SQL login.

  6. In the Name field, enter the Windows NT account (in the form DOMAIN\User) to grant access to SQL Server.

  7. Optionally, select a server from the List names from Domain field, and select the Show all users check box. Double-click under the list of Available names to populate the User name box.

  8. Once you have the user name entered, click OK.

  9. You may now add SQL Logins to the Database users list using the Add button.

  10. To designate a role for the login, select an SQL login from the list of Database users, and then select roles under Roles for selected user.

  11. Proceed through the wizard, and click Finish.

The wizard then creates your SQL Server User Accounts.

Note   Any SQL Server users you create must have a valid Windows NT user or group login, and you must have system administrator privileges on the server.