CODEWITHSUNDEEP
c#sqlsql-server
mJ222398
mJ222398

Reputation: 130

Parameterizing SQL command to change Contained DB user's password

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.

Upvotes: 1

Views: 517

Answers (1)

Parrish Husband
Parrish Husband

Reputation: 3178

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...
}

Upvotes: 2

Related Questions