Solved

SQL - Alter Login with Parameter

Posted on 2015-02-11
5
124 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
Comment Utility
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 45

Expert Comment

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

Author Comment

by:holemania
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
Thanks so much.  That worked awesome.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 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

9 Experts available now in Live!

Get 1:1 Help Now