Set Username and Password for a database in SQL Server

Introduction:

In this article I will explain how to create credentials or set userid and password for a particular database in SQL server.

Description:

In our asp.net web applications generally we used database connection strings like “Data Source=MyServer; Initial Catalog=MySampleDB;uid=test;pwd=test” to connect with SQL server database to get data.

If you observe connection string we are using uid=test;pwd=test to connect with database MySampleDB in SQL server MyServer.

Generally in SQL Server we will create one common credentials for database to allow users to display data in asp.net applications from particular database. To create credentials for particular database we have two ways one is directly from SQL Server Management Studio and another one is by writing query.

First Method

To create credentials for particular database first open SQL Server Management Studio >> enter required servername and credentials >> click connect button

After login to SQL Server open Security >> select Logins >> Right Click on Logins and select New Login that would be like this

After click on New Login another window will open in that first enter Login Name >> select SQL Server Authentication >> Enter Password >> after that uncheck Enforce password policy (if we uncheck Enforce password policy then we have a chance to set password as per our requirements otherwise we need to enter password that meets certain requirements) >> Click OK    

Once Login created now select your required database from your database list to set credentials >> After open database select security >> select users >> Right click users >> select New User

 Whenever we click New User another window will open in that enter Username >> enter LoginName (Previously created Login name for me it’s “abcd”) >> Click OK

Now logoff from your database and connect to your SQL Server with new credentials whatever we created.

Second Method:

In above method we had seen how to set username and password for database using SQL Server Management studio now I will explain how to set same credentials using query in sql server.

USE [master] GO CREATE LOGIN [abcd] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [MySampleDB] GO CREATE USER [abcd] FOR LOGIN [abcd] GO

By running above query we will create username and password for MySampleDB database.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email