2

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?

  • 2
    Do you mean a user in a Contained Database? There is no other type of user that has a password at the database level. – Aaron Bertrand Jun 13 '16 at 13:19
  • No, I meant sqllogin which has permissions to access only one database. – Nate Jun 14 '16 at 2:35
  • In the below code, the windows account has admin permissions to change the sqllogins. In my case, we dont have windows account which has that permissions. Is there any workaround to change the sqllogin password , by using the the existing password and change it to new password. . social.technet.microsoft.com/wiki/contents/articles/…, – Nate Jun 14 '16 at 2:41
  • You can't change that login's password at the database level. It has absolutely nothing to do with how many databases they have access to. – Aaron Bertrand Jun 14 '16 at 3:33
  • Is there way to change the sqllogin user password using powershell, if we have following information (a) SQL Server name (b) SqlLogin name (c) Password (d) newpassword to change to. I donot have access to windows account which has admin rights on the sql server or on the sqllogin users. – Nate Jun 14 '16 at 19:04
2

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.

  • Thank you, My script has following parameters sqllogin and existing password for the sql login. I want to change the password of the sql login using powershell. $srvObj = New-Object "Microsoft.SqlServer.Management.Smo.Server" 'servername' $srvObj.Logins['testusr'].ChangePassword('Test@12345') You cannot call a method on a null-valued expression. At line:1 char:41 + $srvObj.Logins['testusr'].ChangePassword <<<< ('Test@12345') + CategoryInfo : InvalidOperation: (ChangePassword:String) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull – Nate Jun 14 '16 at 2:35
  • In the below code, the windows account has admin permissions to change the sqllogins. In my case, we dont have windows account which has that permissions. Is there any workaround to change the sqllogin password , by using the the existing password and change it to new password. . social.technet.microsoft.com/wiki/contents/articles/…, – Nate Jun 14 '16 at 2:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.