SQL Server

SQL Server security – (Part 2)

by Basit Aalishan Masood-Al-Farooq

SQL Server security – (Part 2)

Author: Basit A. Farooq

Editor’s Note: In this second part of the SQL Server security article series, you’ll first learn how to manage logins. You’ll also learn how to create and manage database users. This article applies to SQL Server 2005 and later editions.

Modifying logins

You can change a login using SQL Server Management Studio or by using the ALTER LOGIN statement. The properties you can alter depend on the type of login.

You can modify the following properties of a Windows login:

• Default database
• Default language

You can modify the following properties of a SQL Server login:

• Password
• Enforce password policy
• Enforce password expiration
• Default database
• Default language

To modify these properties using SQL Server Management Studio:

• Right-click the Login and choose Properties.
• On the General tab, change the desired properties.
• Click OK.

Using ALTER LOGIN

The ALTER LOGIN statement has the following syntax:

ALTER LOGIN login_name

<status_option>

| WITH <set_option_list>

The supported status options are ENABLE or DISABLE. You can use these options to disable a login temporarily. For example, you might want to disable a login of an employee who’s on an extended leave of absence.

The set_option_list options are described in the table:

Option	Description
PASSWORD	New password for the login. SQL Server logins only.
OLD_PASSWORD	Old password for the login. SQL Server logins only.
MUST_CHANGE	User must change the password after the next login. SQL Server logins only.
DEFAULT_DATABASE	Name of the default database for the login.
DEFAULT_LANGUAGE	Name of the default language for the login.
NAME = login_name	Rename the login. If the login is a Windows login, the name must match the Windows login name.
CHECK_EXPIRATION	Enforces password expiration policy if ON. SQL Server logins only.
CHECK_POLICY	Enforces password policy if ON. SQL Server logins only.
CREDENTIAL	Name of the credential used to access outside resources.
NO CREDENTIAL	Delete existing credential mapping.
UNLOCK	Unlock the login that was locked because of too many invalid login attempts. SQL Server logins only.

For example, to change the default database for BasitFarooq, execute:

ALTER LOGIN BasitFarooq

WITH DEFAULT_DATABASE = AdventureWorks2014

To change the password for BasitFarooq, and require him to change it next time he logs in, execute:

ALTER LOGIN BasitFarooq

WITH PASSWORD = ‘P@ssw0rd’ MUST CHANGE

Changing the login status using SQL Server Management Studio

You can also change a login status using SQL Server Management Studio. To do so:

• Right-click the login and choose Properties.
• Click Status.
• Choose Enabled or Disabled.
• If the account is a SQL Server login and is locked out, uncheck “Login is locked out” to unlock the account.
• Click OK.

Deleting logins

You can delete a login by selecting the login and clicking Delete. Or you can use the DROP LOGIN statement:

DROP LOGIN login_name

You can’t drop a login that:

• Has an active connection.
• Owns a securable server object.
• Owns a SQL Agent job.

You shouldn’t drop a login that’s mapped to a database user. A database user is a principal that provides user access to a database.

Retrieving information about logins

You can use the sys.server_principals catalog view to retrieve information about the logins on the server. To retrieve all rows and columns from sys.server_principals, you run:

SELECT * FROM sys.server_principals

The columns in the view are described in the following table:

Column	Description
name	Login name.
principal_id	ID number, unique within the instance of SQL Server.
sid	Security identifier. For Windows logins, this is the same as the SID assigned by the operating system.
type	Type of principal. Supported values are S (SQL login), U (Windows login), G (Windows group), R (Server role), C (Certificate-mapped login), and K (Asymmetric key-mapped login). A server role is a principal that has a set of default permissions.
type_desc	Description of the type of the principal.
is_disabled	Set to 1, the login is disabled.
create_date	Date and time the principal was created.
modify_date	Date and time the principal was last modified.
default_database_name	Default database name of the principal.
default_language_name	Default language of the principal.
credential_id	Credential mapped to principal. If no credential is mapped, stores NULL.

You can retrieve the login name for the current connection login with the SYSTEM_USER() or the SUSER_SNAME(). SYSTEM_USER is a SQL-92 function. For example, to retrieve the current login, you run either of the following two commands:

SELECT SUSER_SNAME()

SELECT SYSTEM_USER()

You can also use the SUSER_SNAME() function to retrieve the login associated with a specific SID. The SID is stored as a hexadecimal value. For example:

SELECT SUSER_SNAME(0x0105000000000000051500000000…)

You can obtain the SID used to login to the current session using the SUSER_SID() function without parameters. For example:

SELECT SUSER_SID()

You can obtain the SID of a user with a specific login name by passing the login name to the SUSER_SID function:

SELECT SUSER_SID(‘BasitFarooq’)

Database users

A user uses a valid login to authenticate and connect to the instance of SQL Server. A user can also access a database under the guest account. The guest account has limited access permissions. To allow more access to a database than that provided by the guest account, you create a database user and map it to a login. You can improve security by disabling the guest account, but keep in mind that this eliminates default limited access to the database.

Default database users

When you first create a database, it has four users:

• INFORMATION_SCHEMA — Owns the INFORMATION_SCHEMA schema.
• dbo — Special user that specifies the permission set when a user is the database owner and is also the owner of the dbo schema.
• guest — Provides limited access to the database if the login isn’t mapped to a database user. By default, a user can connect using the guest account for the user’s default database only.
• sys — Owns the sys schema.

Database user creation

You can create a database user using SQL Server Management Studio or using the CREATE USER statement. You can create only a user mapped to a login account using SQL Server Management Studio.

To create a user in SQL Server Management Studio:

• In Object Explorer, expand Databases.
• Expand the database in which you want to create the user.
• Expand Security.
• Right-click Users and choose New User.
• Enter the user name.
• Enter the login name or browse to select a login name.
• Enter or select the user’s default schema. The default schema is the one assumed when an object is referenced without a schema identifier. If no default schema is selected, the default schema is set to dbo.
• Select any schemas owned by the user.
• Select database role memberships for the user. A database role defines a group of permissions.
• Click OK.

The CREATE USER statement gives you more flexibility. You can create a user based on:

• A login — The user is mapped to a Windows or SQL Server login.
• A certificate — The user is mapped to a PKI certificate.
• An asymmetric key — The user is mapped to an asymmetric key.
• Without login — The user isn’t mapped and can only access this database.

The CREATE USER statement has the syntax:

CREATE USER user_name

[FOR | FROM

LOGIN login_name

| CERTIFICATE cert_name

| ASYMMETRIC KEY asym_key_name

| WITHOUT LOGIN

]

[WITH DEFAULT_SCHEMA = schema_name]

If you don’t provide a login, certification, or asymmetric key, SQL Server attempts to map the user to the SQL Server login with the user_name, if one exists. For example, to create a user for the BasitFarooq login, you can execute either of the following two commands:

CREATE USER ‘BasitFarooq’

CREATE USER ‘BasitFarooq’

FOR LOGIN ‘BasitFarooq’

Database user modification

You can change the database user’s name or default schema with the ALTER USER statement. The ALTER USER statement has the syntax:

ALTER USER user_name

| DEFAULT_SCHEMA = schema_name

For example, to change the default schema for BasitFarooq, you execute:

ALTER USER BasitFarooq

WITH DEFAULT_SCHEMA = ‘Production’

You can also change the user’s default schema through the user’s Properties dialog in SQL Server Management Studio, but you can’t change the user name.

Database user deletion

To delete a database user in SQL Server Management Studio, right-click the user and then choose Delete from the menu. You can also use the DROP USER statement with the syntax:

DROP USER user_name

You can’t drop a user that owns securables.

Database user information

You can retrieve information about the users created on a database by using the sys.database.principals catalog view. The columns are described in the table:

Column	Description
name	Database user name.
principal_id	ID number, unique within the instance of SQL Server.
type	Type of database user. Supported values are S (SQL user), U (Windows user), G (Windows group), R (Database role), A (Application role), C (Certificate-mapped login), and K (Asymmetric key-mapped login).
type_desc	Description of the type of the database user.
default_schema_name	Default schema name of the database user.
create_date	Date and time the database user was created.
modify_date	Date and time the database user was last modified.
owning_principal_id	ID of the owner. Will always be dbo for users.
sid	Security identifier for the login. If not mapped to a login, it’s NULL.
is_fixed_role	Set to 1 for fixed (built-in) database roles.

You can use the USER_NAME, CURRENT_USER, or SESSION_USER functions to determine the name of the user in the current session. To use USER_ID, you call it with no parameters:

SELECT USER_NAME()

CURRENT_USER and SESSION_USER are SQL-92 synonyms for USER_NAME run with no parameters. For example, to determine the user name associated with the database user id of 40, you execute:

SELECT USER_NAME(40)

You can use the USER_NAME function to find the database user id associated with a name. To do so, pass the database user name:

SELECT USER_ID(‘BasitFarooq’)

WITH NAME = new_user_name

Continue to Part-3….