SQL Server Administration

MSDN Documentation > SQL Server > Administration > Managing Logins

Managing Logins in SQL Server

This document provides comprehensive guidance on managing server-level principals, commonly known as logins, in SQL Server. Proper management of logins is crucial for securing your SQL Server instances and controlling access to your databases.

What are Logins?

Logins are security principals that are recognized by SQL Server. They are used to authenticate connections to an instance of SQL Server. SQL Server uses Windows authentication and SQL Server authentication for connecting to an instance. Logins can be granted permissions to access resources in SQL Server.

Types of Logins

  • Windows Logins: These use security accounts from a Windows domain or the local Windows user accounts. They provide a higher level of security and are generally recommended for most environments.
  • SQL Server Logins: These are created and managed entirely within SQL Server and use a SQL Server-generated username and password. They are useful when Windows authentication is not feasible.
  • Mixed Mode Authentication: Allows both Windows and SQL Server authentication.

Creating Logins

You can create logins using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL). It's important to use strong passwords for SQL Server logins and follow security best practices.

Using SSMS:

  1. Connect to your SQL Server instance in SSMS.
  2. In Object Explorer, expand the Security folder.
  3. Right-click on Logins and select New Login....
  4. Configure the login properties, including the login name, authentication type, and permissions.

Using T-SQL:

Example of creating a SQL Server login:


CREATE LOGIN MyNewLogin WITH PASSWORD = 'aStrongPassword123!',
    DEFAULT_DATABASE = AdventureWorks2019,
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = ON;
GO

Modifying Logins

You can modify existing logins to change their passwords, default databases, or server roles.

Using SSMS:

  1. In Object Explorer, expand the Security folder, then expand Logins.
  2. Right-click on the login you want to modify and select Properties.
  3. Make the desired changes in the Login Properties dialog box.

Using T-SQL:


ALTER LOGIN MyNewLogin WITH PASSWORD = 'aNewStrongerPassword456!';
GO

ALTER LOGIN MyNewLogin WITH DEFAULT_DATABASE = master;
GO

Dropping Logins

When a login is no longer needed, it should be dropped to maintain security and reduce the attack surface.

Using SSMS:

  1. In Object Explorer, expand the Security folder, then expand Logins.
  2. Right-click on the login you want to drop and select Delete.
  3. Confirm the deletion.

Using T-SQL:


DROP LOGIN MyOldLogin;
GO

Permissions and Server Roles

Logins are granted permissions to perform actions on SQL Server resources. Server roles grant sets of permissions that can be assigned to logins.

  • Public role: All logins are members of the PUBLIC server role.
  • sysadmin: Has full control over the SQL Server instance.
  • securityadmin: Can manage logins and their properties.
  • serveradmin: Can configure server-wide settings.
  • dbcreator: Can create and manage databases.

It is a security best practice to grant the minimum necessary permissions to a login.

Best Practices for Login Management

  • Use Windows Authentication whenever possible.
  • For SQL Server authentication, use strong, complex passwords and change them regularly.
  • Avoid using the sa account for everyday tasks.
  • Grant logins only the permissions they require (principle of least privilege).
  • Regularly review and audit login activity.
  • Disable or drop logins that are no longer in use.
  • Do not grant membership in the sysadmin role unless absolutely necessary.

Related Topics