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, Aug 29 '18 at 15:24
Missed that (how I have no idea), thanks for the correction @LukaszSzozda. — Thom A, Aug 29 '18 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, Aug 29 '18 at 15:30
Possible duplicate of [How to change a sql login password with variables](https://stackoverflow.com/questions/14858034/how-to-change-a-sql-login-password-with-variables) — Diado, Aug 29 '18 at 15:40

Parrish Husband · Accepted Answer · 2020-12-01T19:25:25.890

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 01 '20 at 19:25

answered Aug 29 '18 at 15:39

Parrish Husband

3,148
18
40

Excellent use of `QUOTENAME`. – Thom A Aug 29 '18 at 15:40
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? – mJ222398 Aug 29 '18 at 15:57
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. – Parrish Husband Aug 29 '18 at 16:04

Parameterizing SQL command to change Contained DB user's password

1 Answers1