Solved

SQL - Alter Login with Parameter

Posted on 2015-02-11
5
141 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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 50

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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