SQL - Alter Login with Parameter

I have a need to create a store procedure to reset user password.  I know that the script is as followed:

ALTER LOGIN [username] WITH DEFAULT_DATABASE=[db1]
GO
USE [db1]
GO
ALTER LOGIN [username] WITH PASSWORD=N'NewPassword' MUST_CHANGE
GO

Would like to have something like

DECLARE @USERNAME VARCHAR(30), @DB VARCHAR(30), @PASSWORD VARCHAR(30)
SET @USERNAME = 'BOB'
SET @DB = 'DB1'
SET @PASSWORD = 'NewPassword'

ALTER LOGIN @USERNAME WITH DEFAULT_DATABASE=@DB
GO
USE @DB
GO
ALTER LOGIN @USERNAME WITH PASSWORD=N'+@PASSWORD+' MUST_CHANGE
GO
holemaniaAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
Yes, you could use the EXEC command. You just build up the string, e.g.

EXEC('ALTER LOGIN ' + @USERNAME + ' WITH DEFAULT_DATABASE=' +@DB)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why do you need the USE @DB?
The ALTER LOGIN doesn't run in a specific database context.
0
 
holemaniaAuthor Commented:
Thanks Phillip.  Will try what you suggested.  

Vitor,

I thought that the default_database would be the database that the user was created in needs to be specify?  So you think I just need this script?

ALTER LOGIN @USERNAME WITH PASSWORD=N'+@PASSWORD+' MUST_CHANGE

And not have to use this?

ALTER LOGIN [username] WITH DEFAULT_DATABASE=[db1]
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you need to specify the default database and for that you should keep both statements. I was referring only to the statement between those two (USE @DB).
You don't need to be in the default database context to change password. Everything is running on master database context.
0
 
holemaniaAuthor Commented:
Thanks so much.  That worked awesome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.