Re: Problem Changing a Principal Password
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 16 Oct 2009 21:50:17 +0000 (UTC)
Mike M (Mike M@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
In a SS 2005 test environment, I created a new user named 'trader1'. In
management studio, I open a new query windows using the new user's
credentials. I want to change the user's password using the following...
exec sp_executesql
N'alter login [trader1] with password = ''@newpwd'' old_password =
''@oldpwd'' ',
N'@newpwd varchar(8), @oldpwd varchar(7)',
N'trader42', N'trader1'
Executing this statement fails with the following error...
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'trader1', because it does not exist or you do not
have permission.
Doesn't make sense to me since I'm logged-in as that user. To make sure I
typed the 'alter login' command correctly, I extracted the statement and
substituted the parameters manually and execute this statement...
alter login [trader1] with password = 'trader42' old_password = 'trader1'
The alter command executed as expected.
Any thoughts? Does 'sp_executesql' really execute under a different user
context?
No, the above should work if your old password really is "@oldpwd". But
if the old password really is 'trader1' it will fail because the old
password does not match.
That is if you say '@something' that is a string literal, nothing
else. Whether you have a variable called @something is not going to
change the.
Thus, the correct way do what you want to do is:
EXEC sp_executesql
N'alter login [trader1] with password = @newpwd old_password = @oldpwd',
N'@newpwd varchar(8), @oldpwd varchar(7)',
N'trader42', N'trader1'
However, ALTER LOGIN does not accept variables for the passwords, so
this yields a syntax error.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Problem Changing a Principal Password
- From: Mike M
- Problem Changing a Principal Password
- Prev by Date: Problem Changing a Principal Password
- Next by Date: RE: SQL Express: is there a commandline way to set the default static port of a named instance?
- Previous by thread: Problem Changing a Principal Password
- Next by thread: RE: SQL Express: is there a commandline way to set the default static port of a named instance?
- Index(es):
Relevant Pages
|
