Skip to content

Create Login in SQL Server

    How to create 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

    You can also use SQL Server’s GUI to create a Login and User as well.

    Expand your instance and Security directory and right click on Logins.  Click on ‘New Login…’

    The Login screen will be displayed.  Type in your new Login name along with a password.  You have options here.  You can chose to have the password be enforced by Windows rules or bypass them.  Please keep your own environment’s security in mind when deciding password rules.  Choose a default database as well and a default language.  Here, we left the language at ‘Default’ which is US English.

    Once you are done, hit ‘OK’ to complete the process.

    Next, expand the database you want to create a new user on.  Expand ‘Security’ and right click on ‘Users’ and click on ‘New User…’

    Leave SQL user with login as the User type.  Specify a new User name.  Afterward, click on the browse icon next to the Login name text field.

    Click on ‘Browse’.

    Find the Login that you created earlier at your instance level.

    Click on ‘OK’.

    Back at the User window, click on ‘Default Schema’.  The Select Schema window will appear.  Click on Browse to select a schema for the user.

    In this case we chose DBO as the default schema.

    Click on ‘OK’ and then ‘OK’ again at the User window and you have successfully created a Login and User.