August 20, 2010 at 1:57 pm
hii,
I have a development server with sql server 2005 on it.
When i have to change a password for a login, i go to
server--> security-->logins--> user-->right click-> properties--> change password
I have to do manually..
I want to change all the users(around 500) password to 'password' ..
Do i have to go to every user and do manually or is there any otehr way/script??
Thanks,
Sushant
Regards
Sushant Kumar
August 20, 2010 at 2:21 pm
ALTER LOGIN [login_name] WITH PASSWORD = 'password'
GO
You could probably loop through the sys.logins table if you wanted too as a quick fix.
Actually thinking about it use an excel spreadsheet. Query the sys.logins for the loginname and then split the command above into the cells surrounding the [login_name]. Then you should be able to past that into a query window (yeah Mohit idea much better:-D).
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 20, 2010 at 3:03 pm
[font="Courier New"]
SELECT 'ALTER LOGIN [' + name + '] WITH PASSWORD=N''password'''
FROM MASTER.sys.server_principals
WHERE TYPE = 'S' AND principal_id <> 1
[/font]
Pretty much what has been said just without help of excel :). Execute this with "Results to Text", copy & paste & execute.
Cheers.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 23, 2010 at 9:03 am
@ mohit
SELECT 'ALTER LOGIN [' + name + '] WITH PASSWORD=N''password'''
FROM MASTER.sys.server_principals
WHERE TYPE = 'S' AND principal_id <> 1
This script when run with results to text is speified gives all(500)alter login commands.
We just have to copy and paste and run it.
But, if we want the enforcing password policy and expiration policy also to be enforced, then how should we edit this script.
Regards,
Sushant
Regards
Sushant Kumar
August 23, 2010 at 10:09 am
SELECT 'ALTER LOGIN [' + name + '] WITH PASSWORD=N''password''
, CHECK_POLICY = ON
, CHECK_EXPIRATION = ON''
Change the first line to this.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 23, 2010 at 12:53 pm
@ meltonDBA & mohit
Thxs a lot.
Regards,
Sushant
Regards
Sushant Kumar
August 24, 2010 at 12:19 am
Very useful script.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply