Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Blog about SQL Server and related technologies.
Browse by Tags · View All
SQL Server 20
Database Administration 12
TSQL 10
Denali 10
SQLServer Denali 9
SQL Server 2012 8
#SQLServer 7
SQLServer 2012 6
PowerShell 4
Performance 4

Archive · View All
August 2011 6
September 2011 4
October 2011 3
November 2011 3
February 2013 2
January 2013 2
December 2011 2
January 2012 2
April 2013 1
November 2012 1

How to change/reset SQL login password

Sep 23 2011 12:00AM by Ana   

Yesterday I saw a question about required permission for changing SQL Server login password and I decided to write a blog post about it. Question was: I have a SQL logins: Login1 and Login2. I gave ALTER LOGIN permission on Login1 to Login2 so that Login2 can change the password for Login1. But when I connect as Login2 and run ALTER LOGIN Login1 WITH password = ‘NewPassword’ an error occur. Any idea if this is a bug or by-design?

Before I answer the question let’s create a script to demonstrate the problem.

USE master;
GO
-- Create the login Login1
CREATE LOGIN Login1 WITH PASSWORD = 'Pa$$w0rd';
-- Create the login Login2
CREATE LOGIN Login2 WITH PASSWORD = 'Pa$$w0rd';
-- Grant the Login2 the required permissions to change Login1 password
GRANT ALTER ON LOGIN::Login1 TO Login2;
GO

-- Set the execution context to Login2.
EXECUTE AS LOGIN = 'Login2';
-- Reset password for the Login1
ALTER LOGIN Login1
WITH Password = 'Sec123';

An error occurs:
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login ‘Login1′, because it does not exist or you do not have permission.

To understand required permission for changing SQL login password we need to understand the difference between password reset and password change. When you reset password for SQL login you only need to supply the new password, but when you change password for SQL login you must supply the new password and the old password. Required permission for resetting password is at least ALTER ANY LOGIN and for changing password required permission is at least ALTER LOGIN.
From a previous script, you can see that Login2 tries to reset password for Login1 but it has only permission to change the password.  To change the password for Login1, Login2 must supply the new and the old password.  Next example shows how to change the password for the login Login1.

-- Set the execution context to Login2.
EXECUTE AS LOGIN = 'Login2';
-- Change the password for Login1
ALTER LOGIN Login1
WITH Password = 'Sec123'
OLD_PASSWORD = 'Pa$$w0rd';

Now command executed successfully. If Login2 doesn’t know the old password for Login1, it must have at least ALTER ANY LOGIN permission to reset the password for Login1. But with ALTER ANY LOGIN permission, Login2 can reset password for all logins except for logins which are member of sysadmin fixed server role or a grantee of CONTROL SERVER permission. Next example shows how to give ALTER ANY LOGIN permission to Login2 and demonstrate password reset for the login Login1.

-- Grant the Login2 ALTER ANY LOGIN permission
GRANT ALTER ANY LOGIN TO Login2;
-- Set the execution context to Login2.
EXECUTE AS LOGIN = 'Login2';
-- Reset password for Login1
ALTER LOGIN Login1
WITH Password = 'NewPa$$word';

Now I can answer to the question. :) No, it’s not a bug. It’s a normal behavior of SQL Server. Same behavior also applies for resetting and changing password on Windows OS.

In this post, I showed the difference between resetting and changing the SQL login password. Login needs ALTER LOGIN permission to change the password.  At least ALTER ANY LOGIN permission is required if login want to reset password.


Republished from DBA Journey [55 clicks].  Read the original version here [32134 clicks].

Ana
120 · 1% · 430
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • Thanks Ana.Great post.Very helpful.Just learned it.

    commented on Oct 11 2011 11:11PM
    Ashraf
    227 · 1% · 197
  • Good Post

    commented on Jan 9 2012 5:36PM
    prathap reddy
    1498 · 0% · 14
  • I think SQL Server Password Changer is the easiest software to reset forgotten SA password for SQL Server 2008. It can download from http://www.top-password.com/sql-server-password-recovery.html

    commented on Mar 3 2012 7:36PM
    joeloreti
    2020 · 0% · 9

Your Comment


Sign Up or Login to post a comment.

"How to change/reset SQL login password" rated 5 out of 5 by 4 readers
How to change/reset SQL login password , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]