Avatar of johnhardy
johnhardyFlag for United Kingdom of Great Britain and Northern Ireland asked on

Usernames and passwords for MS SQL

I am using MS SQL 2008 express on my local machine XP
I can connect to MS SQL databases using windows authentication but some software I wish to use requires a username and password
server=SERVERNAME;uid=USERNAME;pwd=PASSWORD;database=DATABASE_NAME

Can anyone advise how I create the username and password please?
Servername and databse names are known.
Many thanks
John hardy
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
johnhardy

8/22/2022 - Mon
Som Tripathi

You may use below t-sql statement -

CREATE LOGIN login_name WITH PASSWORD='password'
GO
USE DATABASE_NAME
GO
--Creating User - user_name .. Login and user_name is mostly the same --
CREATE USER user_name FROM login_name
GO
--Also you might need to grant some roles - such as dbo (db_owner), datareader (db_datareader) or datawriter (db_datawriter)--

USE DATABASE_NAME
GO
exec sp_addrolemember 'db_owner' , 'user_name'
GO

Qlemo

You can also just create that user in Management Studio (SSMS) under "Security", if you prefere the GUI approach.
Om Prakash

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
johnhardy

Thanks for the info
I created a login via the GUI but the login fails as image.
Being new to MS SQL I regret I dont know about t-sql statements.
 Error
ASKER
johnhardy

What is the password for sa?
Qlemo

The sa password is defined when installing MSSQL Express. It might be empty, but I don't think so.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Bhavesh Shah

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bhavesh Shah

hi,

if you doing for your own use then below link currently wont require.

if you trying to create multiple users with different role then check out following link.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26532011.html?sfQueryTermInfo=1+10+30+brichsoft+creat+login
ASKER
johnhardy

Thanks,
I revised the sa password as suggested by Brichsoft but get the fail error message.
 error5
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
johnhardy

You will need to enable the sa account under Security.
This completed the puzzle so thanks for all of the help.
Many thanks
John
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes