How to Create Login and Password in SQL Server

This post is to help you create a new login and password for the users who want to use data in SQL database or Warehouse for analysis and reporting.

So, Let’s open SQL Server Management Studio (SSMS) and begin.

Make sure you have selected master Database in SSMS before running the query.

Step 1: Create Login:

CREATE LOGIN [LoginName] WITH PASSWORD= ‘Password’;

Example: CREATE LOGIN [Ization] WITH PASSWORD= ‘Two1/2thisissqlserver’;

Once, Login in successfully created, then Create User for that login.

Make sure you have selected the Database where you want to create the user, not the master Database in SSMS before running the query.

Step 2: Create User:

CREATE USER [UserName] FOR LOGIN [LoginName];

Example: CREATE USER [UserIzation] FOR LOGIN [Ization];

Now the user can use the Login and Password to connect to the Database.

But they won’t be able to see the tables, for them to see and use the tables; you must grant permissions to the Schemas.

Step 3: Grant Permission to use Schema:

GRANT SELECT ON SCHEMA:: [SchemaName] TO [UserName];

Example: GRANT SELECT ON SCHEMA::[izt] TO [UserIzation];

Now the users and view and run select queries on the tables in that Schema.

If you want the users to access all the Schemas in the Database, then follow step 4.

Step 4: Grant Select Permission to user for entire Database:

GRANT SELECT TO [UserName];

Example: GRANT SELECT TO [UserIzation];

You can also grant multiple permissions in just one query.

GRANT SELECT, UPDATE, INSERT, DELETE TO [UserName];

Example: GRANT SELECT, UPDATE, INSERT, DELETE TO [UserIzation];

Thank you and Happy Learning.

Ready to elevate your skills? Click here for my Python book and here for my Machine Learning book on Kindle.