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
- In Enterprise Manager, find the desired SQL Server instance.
- Go to Security and then Logins.
- In the right-hand panel, existing logins for the server instance are
listed. Right-click inside the panel and select New Login...
- Type in a name and password for the login.
- In the Server Roles tab, select a server role for the login.
- In the Database Access tab, select to which databases the
login has access (see also
Database role).
- 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'
|
The login name. |
[@passwd =] 'password'
|
The login password; default is NULL. |
[@defdb =] 'database'
|
The default database for the login; default is master. |
[@deflanguage =] 'language'
|
The default language for the login; default is the same as the
SQL Server instance. |
[@sid =] 'sid'
|
The security identification number; default is zero. If
it is zero at login, the system generates a unique SID. |
[@encryptopt =] 'encryption_option'
|
Password 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'
|
The old password; default is NULL. |
[@new =] 'new_password'
|
The new password. |
[@loginame =] 'login'
|
The login whose password you want to change. Only logins with
the sysadmin fixed role
can change passwords. |
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.