SQL SERVER – Create Login account with Accessibility

Question: How to create New User using SQL Server Authentication Mode?

Question: How to give Server level Role to User?

Question: How to give Database   level Role to User?

There are two techniques to Create, Change Role or Delete SQL Login User.

Technique 1 – Using SSMS wizards

Technique2– Using T-SQL Script

·       Using SSMS wizards

Step 1- Open SQL Server Management Studio

 

Step 2 – Go to the Object Explorer and open ‘Security’ Folder.

 

Step 3 – Now right click on the ‘Login’ and select ‘New Login’ then you will get following New Login Window-

 

Step 4 -  Now you can see ‘General’ you can write Login name, Mode of Authentication, Default Database etc. Here we will choose the SQL Server Authentication Mode and write password and Uncheck the User Must change password at next login.

 

Step 5 - Then Select the ‘Server Role Page’ from Left Side Property Tab button. Here you can grant the server role to user, As per your requirement.

 

Step 6- Then Select the ‘User Mapping Role Page’ from Left Side Property Tab button. Here you can Map to User with Databases and can grant the role or membership with database.

 

Note- Here we have selected multiple Databases such as AASDB, AdventureWorks, CMS and DBFGTEST. So here you can choose Individually Database role membership for each selected database

Here We have selected Database role for AdventureWorks.

 

Step 7 - Then select the ‘Status Page’ from Left Side Property Tab button. Here you can set the status of database engine and also enable or disable to User.

 

Step 8- Now click on OK Button and See you ‘JAI_TEST_LOGIN’ account in Object Explorer -> Security -> Login.

·    ---------------------------------------------------------------------------------------------------------------------------------------------------------------

***** Using T-SQL Script -

Generally I preferred this second technique.

Syntax:

CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }

<option_list1> ::=

    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]

    [ , <option_list2> [ ,... ] ]

<option_list2> ::= 

    SID = sid

    | DEFAULT_DATABASE =database   

    | DEFAULT_LANGUAGE =language

    | CHECK_EXPIRATION = { ON | OFF}

    | CHECK_POLICY = { ON | OFF}

    | CREDENTIAL =credential_name <sources> ::=

    WINDOWS [ WITH <windows_options>[ ,... ] ]

    | CERTIFICATE certname

    | ASYMMETRIC KEY asym_key_name<windows_options> ::=     

    DEFAULT_DATABASE =database

    | DEFAULT_LANGUAGE =language

1) To create a login using Windows Authentication

Syntax:

CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;

      GO

Example:

CREATE LOGIN [JAI-MATA-DI\jainendra] FROM WINDOWS;

        GO

2) To create a login using SQL Server Authentication

Example1:

CREATE LOGIN JAI_TEST_LOGIN

WITH PASSWORD = 'PWD12345'

GO

Example2:

CREATE LOGIN [JAI_TEST_LOGIN] WITH PASSWORD='PWD12345',

DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

EXEC sys.sp_addsrvrolemember @loginame = N'JAI_TEST_LOGIN', @rolename = N'dbcreator'

GO

-- Enable New Creted user

ALTER LOGIN [JAI_TEST_LOGIN] ENABLE

GO