How to update a DB user password in SQL Server 2012

Question

How to update a DB user password in SQL Server 2012

melinda 78672

SSC Enthusiast

Points: 153
More actions
November 5, 2013 at 8:11 am

#300744

I am needing to update the password of one of the users in the DB\security folder in SQL Server 2012. Unfortunately, we do not have a DBA in-house and consequently needing some help. I've been told that I have sysadmin privileges on this SQL Server but I cannot find WHERE to update a user's password in the database. When I googled this, msdn show me how to update a login in the SQL Server 2012 box but this user is NOT listed under the Security\Logins folder on this SQL Server but this user is only under the database\Security\Users folder.
I had tried the ALTER LOGIN username WITH PASSWORD = 'password'; command but I only got this error:
Msg 15151, Level 16, State 1, Line 2 Cannot alter the login 'ATM', because it does not exist or you do not have permission.
I also tried the Use DBName exec sp_password @old = null, @new = '@password1o^|', @loginame = 'userName'
Any help/direction would be appreciated. Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

Did you recently restore this database to a new server? Sounds like you are missing the login, but have the user in the database. You will need to create a login:

CREATE LOGIN ATM WITH PASSWORD = 'enterStrongPasswordHere';

then inside the restored database run this:

exec sp_change_users_login 'update_one', 'ATM'

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

melinda 78672 SSC Enthusiast Points: 153 More actions · Answer 2

Yes, the IT manager here recently copied the database from one server to this one. I'll go ahead and try what you've suggested and hope that works on this side.

Thanks so much for your help.

melinda 78672 SSC Enthusiast Points: 153 More actions · Answer 3

Tried the

Use DBName

exec sp_change_users_login 'update_one', 'ATM' and got this error:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 98

An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

Then I used the following command:

exec sp_change_users_login 'update_one', 'ATM', 'ATM' and I was able to connect via a SQL Server login connection.