Login in SQL Server

How to create a login in SQL Server SQL Server Management Studio or T-SQL

A login is the identity of the person or process that is connecting to an instance of SQL Server. A SQL login is authenticated by a secure system

All Users need a login to connect to SQL Server

You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login)

  • To use SQL Server Authentication, the Database Engine must use mixed mode authentication
  • The scope of a login is the whole Database Engine (mapped to the whole database)
  • To connect to a specific database on the instance of SQL Server, a login must be mapped to a database user
  • A database user is an already created LOGIN that is mapped to a specific database that can access the database itself, but ‘not the objects’ in the database
  • Permissions inside the database are granted and denied to the database user, not the login
--Change the authentication mode 

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', 
REG_DWORD, 1  --<< Windows Authentication Mode
GO

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', 
REG_DWORD, 2  --<< SQL Server and  Windows Authentication Mode
GO


--create a login for SQL Server for a single login (Matt)

USE [master]
GO
CREATE LOGIN [Matt] 
WITH PASSWORD=N'password123' 
MUST_CHANGE, 
DEFAULT_DATABASE=[AdventureWorks2012], 
CHECK_EXPIRATION=ON, 
CHECK_POLICY=ON
GO

--Grant Select Only to Matt to view the table

use [AdventureWorks2012]
GO
GRANT SELECT ON [HumanResources].[Department] TO [Matt]
GO


--Granular access to table and columns [DepartmentID],[Name] only

use [AdventureWorks2012]
GO
REVOKE SELECT ON [HumanResources].[Department] TO [Matt] AS [dbo]
GO
use [AdventureWorks2012]
GO
GRANT SELECT ON [HumanResources].[Department] ([DepartmentID]) TO [Matt] AS [dbo]
GO
use [AdventureWorks2012]
GO
GRANT SELECT ON [HumanResources].[Department] ([Name]) TO [Matt] AS [dbo]
GO


--Grant access to all permissions

use [AdventureWorks2012]
GO
GRANT ALTER ON [HumanResources].[Department] TO [Matt]
GO
use [AdventureWorks2012]
GO
GRANT CONTROL ON [HumanResources].[Department] TO [Matt]
GO
use [AdventureWorks2012]
GO
GRANT DELETE ON [HumanResources].[Department] TO [Matt]
GO
use [AdventureWorks2012]
GO
GRANT INSERT ON [HumanResources].[Department] TO [Matt]
GO
use [AdventureWorks2012]
GO
GRANT REFERENCES ON [HumanResources].[Department] TO [Matt]
GO

use [AdventureWorks2012]
GO
GRANT TAKE OWNERSHIP ON [HumanResources].[Department] TO [Matt]
GO
use [AdventureWorks2012]
GO
GRANT UPDATE ON [HumanResources].[Department] TO [Matt]
GO
use [AdventureWorks2012]
GO
GRANT VIEW CHANGE TRACKING ON [HumanResources].[Department] TO [Matt]
GO
use [AdventureWorks2012]
GO
GRANT VIEW DEFINITION ON [HumanResources].[Department] TO [Matt]
GO


--mapp the login Matt to User in the database

USE [AdventureWorks2012]
GO
CREATE USER [Matt] FOR LOGIN [Matt]
GO


--sp_who
--Kill 59