Sql-server – Changing SQL account password in specific database

passwordpowershellsql server

I am trying to change the password for userId in a specific database called LionsDB.
If I am changing the password manually, I would login into SQL Management Studio using that user account and update the password.

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
$SQLUser = $srv.Logins | ? {$_.Name -eq "$login"};
$SQLUser.ChangePassword($password);

With powershell, I cant find the specific logins in the LionsDB database. The above code gives me the logins at server level. Is there anyway to get to the userIDs in specific catalog and change password for them?

Best Answer

All password authentication for userid is at the server level for a login. Therefore the LionsDB database will have users but does not have per database password for those users since the login is the authentication point.

If you have enough rights, you can change the passwords of the logins and use a different password, but that password would be the same for all databases on your server.

If you need a special login for a database, then you should create another login, e.g. LionsDB_George or some such pattern that suits you. Then you grant that login rights to the LionsDB database.

Related Question