Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Set Username and Password for a database in SQL Server

Mar 19, 2012
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 RSS subscribe by email Subscribe by Email

12 comments :

Anonymous said...

Created the separate user for one of my database but i am accessing the database using windows authentication also...Then what is the benefit..?

Suresh Dasari said...

Now we have a situation like 100 members are there everybody have to get data from one database to use in their application it's difficult to give permission for each user in that situation we will create one separate user credentials and give those details to users to access database in their applications. it won't delete your access permissions for database

Anonymous said...

very good

Anonymous said...

Hi,
i have two doubts.
1)How to create secure connection to database?
Using this username and pwd, somebody can access database.
What is the method to access database securly from asp.net ?
need to set or install anything in sql serer?

2) If two users access the particular column to modify means how to manage this situation?

Suresh Dasari said...

if you want allow users to access the database connection securly write db connection in web.config file and use that one in your application http://www.aspdotnet-suresh.com/2011/11/write-connection-strings-in-webconfig.html

Anonymous said...

Thank you so much, i am reading your blogs now only. Really excellent.

If you dont mind, Could you please explain about locks?

Anonymous said...

Hi Sir Can u Please Explain How to Create Stored Procedure and Triggers

vj said...

Sir,i want to send email on button click.If user fill a reg form then his all data should be send at a desired Email id(provided in coding) with showing which field is filled by user for what. and it should be send in grid view form or,in text form.

Unknown said...

sir i have online medical insurance in that it has errror i need u r help to correct that error plz help me sir

AMAR GOALA said...

Hi sir,
how to do SQL connection string in encrypted format in web.config file .Plz Explain ?

Unknown said...

Hi,
I'm new to asp.net.i have one doubt.
im using window authentication using sql server..what is the purpose of using this username and password..
Only one person can access the database because it is window authentication...then how u telling "will create one separate user credentials and give those details to users to access database in their applications" can u explain it briefly...
Thanks in advance
Renuka

sakthideveloper said...

Hi suresh,
i created login and user according to your article. But it throws the Exception like"login failed for user sakthi, The user is not associated with a trusted SQL server connection". Can you help me?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.