Solved

SQL - Alter Login with Parameter

Posted on 2015-02-11
5
126 Views
Last Modified: 2015-02-23
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
0
Comment
Question by:holemania
  • 2
  • 2
5 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40604381
Yes, you could use the EXEC command. You just build up the string, e.g.

EXEC('ALTER LOGIN ' + @USERNAME + ' WITH DEFAULT_DATABASE=' +@DB)
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40605406
Why do you need the USE @DB?
The ALTER LOGIN doesn't run in a specific database context.
0
 

Author Comment

by:holemania
ID: 40606623
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
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40607433
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
 

Author Closing Comment

by:holemania
ID: 40625946
Thanks so much.  That worked awesome.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now