Parameterizing SQL command to change Contained DB user's password

Question

I have some Contained DB users in SQL Server 2017. In C# I am trying to execute a SQL command to change the password of a particular one of these users. The DB connection has permission to do this and does not require me to know the current password of the user in order to do so.

var sqlStatement = $"ALTER USER {userName} WITH PASSWORD = '@Password';";

        using (var cmd = new SqlCommand(sql, sqlConnection))
        {
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = userName;
            cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;

            var rowsAffected = cmd.ExecuteNonQuery();
        }

Above is the code I had been using. Running a trace on what was produced shows the command to be:

exec sp_executesql N'ALTER USER UserName1 WITH PASSWORD = ''@Password'';',N'@Password nvarchar(18)',@Password=N'Hqc5w7m98s4J!9'

This does not error at all, in fact it seems to work. But when I attempt to login (before the password change I could log in fine) I cannot do so. It appears to have changed the password but not to the one I specified. If I run the following command instead (no parameters in C#, just string building):

ALTER USER [UserName1] WITH PASSWORD = 'Hqc5w7m98s4J!291'

Then I am now able to login again with the new password. I cannot seem to find out what is happening in the first case? Why is it not changing the password as I expect? I've tried a few variations of it to try and get it to work using parameters but no luck. I don't want to just use the parameterless version of course but have spent around 5 hours looking at this already.

Remove the quotes around '@Password' - you're setting the password to the string @Password, not the value of the variable. — Diado
– Diado, Commented Aug 29, 2018 at 15:24
Missed that (how I have no idea), thanks for the correction @LukaszSzozda. — Thom A
– Thom A ♦, Commented Aug 29, 2018 at 15:26
@Diado you cannot do that, get the error Msg 102, Level 15, State 1, Line 11 Incorrect syntax near '@Password'. Though you are right, it is changing the password to @Password. — mJ222398
– mJ222398, Commented Aug 29, 2018 at 15:30
Possible duplicate of How to change a sql login password with variables — Diado
– Diado, Commented Aug 29, 2018 at 15:40

Parrish Husband · Accepted Answer · 2020-12-01 19:25:25Z

2

Alter User may not work with traditional parameterization.

string sqlStatement = @"
DECLARE @sql NVARCHAR(500)
SET @sql = 'ALTER USER UserName1 WITH PASSWORD= ' + QuoteName(@Password, '''') 
EXEC(@sql)";

using (var cmd = new SqlCommand(sqlStatement , sqlConnection))
{
    cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;
    // execute...
}

edited Dec 1, 2020 at 19:25

answered Aug 29, 2018 at 15:39

Parrish Husband

3,17620 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thom A Over a year ago

Excellent use of QUOTENAME.

mJ222398 Over a year ago

Thanks, this worked, though I had to put parentheses around the @sql in the EXEC statement to avoid an error. Is there any documentation on why traditional parameterization does not work then? Is this a bug with SQL that might be resolved?

Parrish Husband Over a year ago

I believe this is just a problem with string literals. The ALTER USER command expects single quotes when changing the password, however when quotes are surrounding a @param it doesn't get properly replaced. A bit of an edge case, but this way you still avoid injection problems.

Collectives™ on Stack Overflow

Parameterizing SQL command to change Contained DB user's password

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related