Permissions: Creating and maintaining users

Permissions: Creating and maintaining users

Difference between user and login

Although the terms login and user are often used interchangeably, they are very different. 

o A login is used for user authentication 

o A database user account is used for database access and permissions validation. 

Syntax:

User:

Create user <username> for login <loginname>

Login:

CREATE LOGIN [<domain_name,,domain>\<user_name,,windows_user>] 

FROM WINDOWS WITH DEFAULT_DATABASE= <default_database, sysname, master> GO

Logins:

Built in logins & users & their uses/importance:

Syntax:

--CREATING USER ROCK WITH LOGIN 

USE [master] GO 

CREATE LOGIN [ROCK] WITH PASSWORD=N'ROCK@123', DEFAULT_DATABASE=[master],

 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGO

USE [TESTDB] GO 

CREATE USER [ROCK] FOR LOGIN [ROCK]

Alter Login:

You can use the ALTER LOGIN statement to change a password, force a password change, disable a login, enable a login, unlock a login, rename a login, etc.

Syntax

The syntax for the ALTER LOGIN statement in SQL Server is:

ALTER LOGIN login_name
{ ENABLE | DISABLE
| WITH PASSWORD = 'password' | hashed_password HASHED
         [ OLD_PASSWORD = 'old_password' ]
         | MUST_CHANGE
         | UNLOCK
       | DEFAULT_DATABASE = database_name
       | DEFAULT_LANGUAGE = language_name
       | NAME = new_login_name
       | CHECK_EXPIRATION = { ON | OFF }
       | CHECK_POLICY = { ON | OFF }
       | CREDENTIAL = credential_name
       | NO CREDENTIAL
| ADD CREDENTIAL new_credential_name
| DROP CREDENTIAL credential_name };

Parameters or Arguments

login_name

The Login name currently assigned to the Login.

ENABLE

Enables the Login.

DISABLE

Disables the Login.

password

The new password to assign to the Login that is authenticated using SQL Server authentication.

hashed_password

The hashed value of the password to assign to the Login using SQL Server authentication.

old_password

The old password using SQL Server authentication.

MUST_CHANGE

It is used when you want to force the password to be changed the first time that the Login is used after the ALTER LOGIN statement.

UNLOCK

It will unlock a Login that has been locked out.

database_name

The default database to assign to the Login.

language_name

The default language to assign to the Login.

new_login_name

The new name of the Login if you are using the ALTER LOGIN statement to rename a Login.

CHECK_EXPIRATION

By default, it is set to OFF. This option determines whether password expiration policy is enforced. You must specifiy CHECK_EXPIRATION = ON when you use the MUST_CHANGE option.

credential_name

The name of a credential to assign to the Login.

NO CREDENTIAL

Removes any mapped credentials from the Login.

ADD CREDENTIAL

Adds a credential to the Login.

DROP CREDENTIAL

Removes a credential from the Login.

Example - Change Password

Let's look at how to change a password using the ALTER LOGIN statement in SQL Server (Transact-SQL).

For example:

ALTER LOGIN sqldose
WITH PASSWORD = 'Sqldose@123';

This ALTER LOGIN example would alter the Login called sqldose and change the password of this login to 'Sqldose@123'.

To Retrieve Users:

To retrieve all Logins in SQL Server, you can execute the following SQL statement:

SELECT *
FROM master.sys.sql_logins;

The sys.sql_logins view contains the following columns:

Column	Explanation
name	This is the login_name that was assigned in Create login statement
principal_id	Numeric value
sid	This is the sid that was assigned in CREATE LOGIN statement
type	Type of principal S = SQL Server user U = Windows user G = Windows group A = Application role R = Database role C = Certificate mapped K = Asymmetric key mapped
type_desc	Description for type of principal SQL_USER WINDOWS_USER WINDOWS_GROUP APPLICATION_ROLE DATABASE_ROLE CERTIFICATE_MAPPED_USER ASSYMETRIC_KEY_MAPPED_USER
is_disabled	0 or 1
create_date	Date/time when Login was created using the CREATE LOGIN statement
modify_date	Date/time when Login was last modified using the ALTER LOGIN statement
default_database_name	This is the default database assigned in CREATE LOGIN statement or ALTER LOGIN statement
default_language_name	This is the default language assigned in CREATE LOGIN statement or ALTER LOGIN statement
credential_id	This is the credential assigned in CREATE LOGIN statement or ALTER LOGIN statement
is_policy_checked	0 or 1, assigned by CREATE LOGIN statement or ALTER LOGIN statement
is_expiration_checked	0 or 1, assigned by CREATE LOGIN statement or ALTER LOGIN statement
password_hash	Hashed value of the password

DROP LOGIN:

The DROP LOGIN statement is used to remove an identity (ie: Login) used to connect to a SQL Server instance.

Syntax

The syntax for the DROP LOGIN statement in SQL Server (Transact-SQL) is:

DROP LOGIN login_name;

Parameters or Arguments

user_name

The name of the Login to remove.

Note

• You can not drop a Login when it is currently logged into SQL Server.
• If you drop a Login that has database users mapped to it, the users will be orphaned in SQL Server.

Example

Let's look at an example that shows how to use the DROP LOGIN statement in SQL Server (Transact_SQL).

For example:

DROP LOGIN sqldose;

To create orphan user without login:

USE [jobs_cp] 
GO

CREATE USER [job_test1] WITHOUT LOGIN 
GO