Add user to MSSQL server 2012 for the web user linked to coldfusion

I have migrated from SQL Server 2005 to SQL server 2012 and CF11.  A user was set up as the user in coldfusion in my old setup - that is the web visitor. I dont know the password for the old web user set up. I am a novice and this is a once in 12 years  for me -  SQL Server 2012 seems complex. I just want to add a user for the CF (web visitor) and it asks for a password. Cant even see where you add a password to the user. Anyhow appreciate some help So the webuser can read - write to db - register, update etc.

Ian WhiteOwner and FounderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What is asking for a password - SQL Server or ColdFusion? Might be easier to post a screen shot.

Also, did you upgrade - or are these completely new installs?
Ian WhiteOwner and FounderAuthor Commented:
I upgraded from SQL server 2005 to 2012 via 2008 - migrating to a new host

Coldfusion needs a username and password for the public user (surfer) to login - it is where you define, verify the
DSN for the database. The user needs read and write access

Does anyone have the Transactional SQL so I can try and create a new public user with read and write access (not admin). Sorry could not do screen print from RDT - but here is a copy of labels on CF admin page

Data & Services > Datasources > Microsoft SQL Server
 Microsoft SQL Server :  housecare2  
 CF Data Source Name      
 Server        Port      
 User name    
> Coldfusion needs a username and password for the public user

EDIT: Yes, I understand but it's not clear whether you really need to create a new user account.  That should only be necessary if it's completely new db.  If you upgraded an existing db, it would still contain all the old data AND logins.  So you wouldn't need to a new login account at all.  Just to reset the password of the old one.

If that's the case, just log into the SQL Server database with something like Management Studio.  Locate the login and change the password to something else.  It can also be done with TSQL:

      ALTER LOGIN surfer WITH PASSWORD = 'the new password you want here';

Then use the new credentials when creating the DSN in the CF Administrator.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

> Does anyone have the Transactional SQL so I can try and create a new public user with read and write access (not admin).

I normally use SSMS for managing logins, instead of TSQL. So I'll let someone else offer up the TSQL.  But if you're interested in using SSMS it looks like 2012 hasn't changed much since 2008:

Add new login

1. Open Management Studio. Select Object Explorer > Security
2. Right click Logins > Add New
3. Enter the desired user name. Check "SQL Server Authentication" and enter a password
* Note: For web application accounts, you usually want to uncheck the last two options, ie "expire" and "change at next login"
create login and password4. Without closing the window, select "User Mapping" from the left menu. Check the box next to the name of your web database.
5. Under "Database role membership", check the boxes for the desired roles. Then click OK to apply.

* Note: For DEV db's I often use "db_owner".  For PROD you may want to assign different roles like db_datareader, db_datawriter, etc... All depends on what permissions your app needs.

assign login roleWhen done, verify the new password works in SQL Server. Then enter the new credentials in your CF DSN.

Ian WhiteOwner and FounderAuthor Commented:
No 2012 has changed .  It is right click on user then add new user - then SQL user with login - it asks for username and login name -    and there is no provision to put in a password.

I altered an old login password and it excecuted - then when I tried to add to coldfusion dsn verification I got
Connection verification failed for data source: housecare2
java.sql.SQLInvalidAuthorizationSpecException: [macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'house-pub'.
I've gotten that error when updating an existing DSN -after changing the password. As if the credentials were cached somehow. It didn't stick until I edited the DSN a 2nd or 3rd time (changing the password - then clicking submit)

If it's a brand new DSN:

1. Did you verify you've enabled SQL Server Authentication in the db? Mixed mode authentication is NOT enabled by default.
2. Did you verify the credentials in SQL Server 1st?
Ian WhiteOwner and FounderAuthor Commented:
Thanks. I finally got a response from the new hosting company. So it appears it something you need to be aware of when migrating? The DSN works now after below. It appears the login lives below the user - 2 entities.

In this case the issue was related to the "house-pub" user that was restored when the database was migrated onto the VPS server. Since the MSSQL server and MSSQL databases maintain their own user accounts and login, it turned out that the "house-pub" user that was created for the MSSQL server, was not linking to the "house-pub" user that was restored with the database. Hence when trying to access the database the login would fail. To correct this issue I ended up documenting the access rights granted to the "house-pub" user attached to the database, and removing all instances of the user account from the server, before adding it back and re-configuring the account with the proper user rights as you had previously setup.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ohh... that makes sense.  I'd forgotten about that. The same thing happened to me once when restoring a dump of another db into my DEV environment due to how SQL Server treats server level logins vs db users.  

Glad you solved it! Don't forget to mark your comment as the answer to close out this thread.
Ian WhiteOwner and FounderAuthor Commented:
Thanks for your contributions, as it helped me get to a solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.