Re: Problem Changing a Principal Password



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

.



Relevant Pages

  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... Hard code WHERE clauses. ...
    (comp.lang.cobol)
  • Re: SQL Injection- Bypassing magic_quotes
    ... Because i was trying to execute: ... Don't terminate the query and you most ... Or try something like bobcat or one of the other SQL injection tools out ... Chief Information Security Officer ...
    (Pen-Test)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... explicitly code dummy words as bind variable placekeepers, put the host variables on the ...
    (comp.lang.cobol)
  • Re: Poor performance when executing stored procedure
    ... > Generally I would write stored procedures to do only one job. ... If SQL Server ... > which is even worse those sps can execute each other. ... > then executed spOrders which is executed in spCustomers and then got stuck ...
    (microsoft.public.sqlserver.programming)
  • Re: UPDATE query in Access 2003 raising error
    ... string into a variable strSQL but create the qrydef from a string strSQLx (I ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)