A server login grants you access to the SQL Server instance. How much access you have depends on your login privileges.

The SQL Server installation uses the default login name sa (system administrator) without a password. The password can be changed only after the installation is complete.

Sa is a special login. By default it has the role of sysadmin fixed server, which cannot be changed. Even though sa is the default administrator login, it is not recommended to use it. Instead, create new users and assign them the sysadmin role.

When using SQL Server with Windows NT authentication, the sa login is irrelevant. But when using Mixed Mode, the default password must be changed to prevent unautorized access.

Creating a Login with Enterprise Manager

1. In Enterprise Manager, find the desired SQL Server instance.
2. Go to Security and then Logins.
3. In the right-hand panel, existing logins for the server instance are listed. Right-click inside the panel and select New Login...
4. Type in a name and password for the login.
5. In the Server Roles tab, select a server role for the login.
6. In the Database Access tab, select to which databases the login has access (see also Database role).
7. Click OK to create the login.

The login name can be from 1 to 128 characters long, cannot be blank (null), cannot contain backslashes (\), and cannot be a reserved name.

Creating a Login with T-SQL

The T-SQL syntax for the function for adding logins is like follows:

sp_addlogin [@loginame =] 'login'
[,[@passwd =] 'password']
[,[@defdb =] 'database']
[,[@deflanguage =] 'language']
[,[@sid =] 'sid']
[,[@encryptopt =] 'encryption_option']

Parameters

[@loginame =] 'login'

[@passwd =] 'password'

[@defdb =] 'database'

[@deflanguage =] 'language'

[@sid =] 'sid'

[@encryptopt =] 'encryption_option'

Deleting a Login

You can delete a login by running the following command:

sp_droplogin [@loginame =] 'login'

[@loginame =] 'login' is the name of the login you wish to delete.

Changing the Password

You can change a login's password with the following command:

sp_password [[@old =] 'old_password',] 
{[@new =] 'new_password'}
[,[@loginame =] 'login']

Parameters

[@old =] 'old_password'

[@new =] 'new_password'

[@loginame =] 'login'

Granting and Denying Access

If you want to explicitly deny server access to a login, use the following command:

sp_denylogin [@loginame =] 'login'

where [@loginame =] 'login' is a user name or Windows NT group.

If you want to grant server access to a login, use the following command:

sp_grantlogin [@loginame =] 'login'

where [@loginame =]'login' is a user name or Windows NT group.