Solved

SQL - Alter Login with Parameter

Posted on 2015-02-11
5
136 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 48

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 48

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
performance query 4 32
database level memory cache..? 8 31
awk and Pythagoras? 5 19
Accessing variables in MySQL query 4 28
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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