Меню

Microsoft sql server ошибка 15151 при смене пароля

How to fix the 15151 error on the SQL server? Bobcares, as a part of our Server Management Services offers solutions to every query that comes our way.

15151 error on the SQL server

A login is a security principal or an object that a secure system may authenticate. In order to connect to SQL Server, users must log in. You have the option of creating a login based on a Windows principal (such as a domain user or a Windows domain group) or one that is not (such as an SQL Server login).

A user is a database security principle. To connect to a database, there is a need to map logins to a database user. A login maps to several databases as distinct users, but it can only map as one user in each database. A user without a login can be generated in a partially contained database. By enabling a database’s guest user, a login that doesn’t map to a database user can access the database as the visitor user.

15151 error sql server

Logins are not the same as database users. In a separate step, we must map logins or Windows groups to database users or roles. Then we should provide users or roles permission to view database items.

Case 1

Error:

Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ‘sa’, because it does not exist or you do not have permission.

Solution:

This mistake occurred due to a lack of rights. SA stands for system administrator and is the highest level of user in the system. If a user needs to change SA’s permissions, that user must have higher or comparable rights as the SA user.

Only users in the systemadmin group have the ability to change the rights of the SA user. By first adding any user to the systemadmin role and then using the same account to modify the system admin’s tool, we can fix the problem.

Case 2

Error:

Msg 15151, Cannot alter the user ‘xxxx’, because it does not exist or you do not have permission

Solution:

We need to create the user (CREATE USER [xxxx]) before we try to alter it. Missing the “create user” instruction before the alter may show the error. Login gets user entry to the server Database and the User gets user entry to a particular database. So in the script, we have to include create user from login.

[Looking for a solution to another query? We are just a click away.]

Conclusion

In this article, we have provided a general idea from our Tech team regarding the 15151 error along with a discussion on two use cases and its solution.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

Источник
  • Remove From My Forums

  • Question

  • Hi,

       We are getting the below error when we are trying to reset the password of ‘sa’ using a Security admin user.

    Error:

    Cannot alter the login ‘sa’ because it does not exist or you do not have permission. (Microsoft SQL server, Error: 15151)

Answers

  • No. When you want to alter a different principal you need at least the same or higher permissions; and SysAdmin is already the role with the highest permissions.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by

      Thursday, October 13, 2016 12:04 PM

Источник
  • Remove From My Forums

  • Question

  • I need to change the password for the SA account on a SQL Server 2005 instance using TSQL.  I can change the password using Management Studio if I select the «Map Credential» option but this needs to be done via an automated process so I have
    to use TSQL.  SMO isn’t viable and I’ve not tried it since I have instances ranging from SQL Server 7.0 to SQL Server 2012.  The exact error text is:

    Msg 15151, Level 15, State 1, Line 1

    Cannot alter login ‘SA’, because it does not exist or you do not have permission.

    I’ve tried several different syntactical variations of the ALTER LOGIN command but without success.  It is supporting a commercial application so I can’t really afford to experiment with altering server settings.

    Can someone offer the correct way to change the SA password via TSQL?

    • Edited by

      Wednesday, April 10, 2013 3:02 PM

Answers

  • After some additional exploration, it looks like I can use SMO.  None of the instances I need to automate the password change on is lower than SQL Server 2000 and the ChangePassword method of the Login object works on versions 2000 and above. 
    Using SMO simplified the code in that I no longer need to connect use a SQLCommand and submit TSQL.  For anyone else needing to do this here is brief Powershell function to accomplish the task.

    Function reset-sapwd([string] $instance, [string] $password)
{
    try
    {
        $target = New-Object Microsoft.SqlServer.Management.Smo.Server($instance)
        $target.Logins["sa"].ChangePassword($password)
    }
    catch
    {
        # perform any error handling or notification
    }
}

    • Marked as answer by
      Ira Davis
      Wednesday, April 10, 2013 5:00 PM

Источник
  • Remove From My Forums

  • Question

  • I need to change the password for the SA account on a SQL Server 2005 instance using TSQL.  I can change the password using Management Studio if I select the «Map Credential» option but this needs to be done via an automated process so I have
    to use TSQL.  SMO isn’t viable and I’ve not tried it since I have instances ranging from SQL Server 7.0 to SQL Server 2012.  The exact error text is:

    Msg 15151, Level 15, State 1, Line 1

    Cannot alter login ‘SA’, because it does not exist or you do not have permission.

    I’ve tried several different syntactical variations of the ALTER LOGIN command but without success.  It is supporting a commercial application so I can’t really afford to experiment with altering server settings.

    Can someone offer the correct way to change the SA password via TSQL?

    • Edited by

      Wednesday, April 10, 2013 3:02 PM

Answers

  • After some additional exploration, it looks like I can use SMO.  None of the instances I need to automate the password change on is lower than SQL Server 2000 and the ChangePassword method of the Login object works on versions 2000 and above. 
    Using SMO simplified the code in that I no longer need to connect use a SQLCommand and submit TSQL.  For anyone else needing to do this here is brief Powershell function to accomplish the task.

    Function reset-sapwd([string] $instance, [string] $password)
{
    try
    {
        $target = New-Object Microsoft.SqlServer.Management.Smo.Server($instance)
        $target.Logins["sa"].ChangePassword($password)
    }
    catch
    {
        # perform any error handling or notification
    }
}

    • Marked as answer by
      Ira Davis
      Wednesday, April 10, 2013 5:00 PM

Источник
  • Remove From My Forums

  • Question

  • I need to change the password for the SA account on a SQL Server 2005 instance using TSQL.  I can change the password using Management Studio if I select the «Map Credential» option but this needs to be done via an automated process so I have
    to use TSQL.  SMO isn’t viable and I’ve not tried it since I have instances ranging from SQL Server 7.0 to SQL Server 2012.  The exact error text is:

    Msg 15151, Level 15, State 1, Line 1

    Cannot alter login ‘SA’, because it does not exist or you do not have permission.

    I’ve tried several different syntactical variations of the ALTER LOGIN command but without success.  It is supporting a commercial application so I can’t really afford to experiment with altering server settings.

    Can someone offer the correct way to change the SA password via TSQL?

    • Edited by

      Wednesday, April 10, 2013 3:02 PM

Answers

  • After some additional exploration, it looks like I can use SMO.  None of the instances I need to automate the password change on is lower than SQL Server 2000 and the ChangePassword method of the Login object works on versions 2000 and above. 
    Using SMO simplified the code in that I no longer need to connect use a SQLCommand and submit TSQL.  For anyone else needing to do this here is brief Powershell function to accomplish the task.

    Function reset-sapwd([string] $instance, [string] $password)
{
    try
    {
        $target = New-Object Microsoft.SqlServer.Management.Smo.Server($instance)
        $target.Logins["sa"].ChangePassword($password)
    }
    catch
    {
        # perform any error handling or notification
    }
}

    • Marked as answer by
      Ira Davis
      Wednesday, April 10, 2013 5:00 PM

Источник

I have the following script and I keep getting errors when it is executed.

USE [master]
GO

If Not EXISTS (Select loginname from [master].[dbo].[syslogins]
    Where name = 'xxxx' and dbname = 'xxxx-xxxx')
BEGIN
    CREATE LOGIN [xxxx] WITH PASSWORD=N'xxxxx', DEFAULT_DATABASE=[xxxx-xxxx], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    ALTER LOGIN [xxxx] ENABLE
END
GO

USE [xxxx-xxxx]
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [xxxx]
ALTER ROLE [db_datawriter] ADD MEMBER [xxxx]
ALTER ROLE [db_owner] ADD MEMBER [xxxx]
GO

I have tried several permutations of this script with no success. It does create the login but won’t let me alter the «user» or «role» properties. When I can get it to work with no errors, it does not make the changes to the user profile.

asked Oct 2, 2019 at 23:29

Eldon Z's user avatar

3

You are missing create user before you alter it.

Login — gets you entry to the server
Database User — gets you entry to a particular database

so in your script, add create user from login ...

USE [xxxx-xxxx]
create user [xxxx] from login [xxxx] <--- this is needed !
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [xxxx]
ALTER ROLE [db_datawriter] ADD MEMBER [xxxx]
ALTER ROLE [db_owner] ADD MEMBER [xxxx]
GO

few things :

  • sys.syslogins is deprecated.
  • dbatools has cmdlets for Login and user management which you can leverage for automation.

answered Oct 3, 2019 at 1:23

Kin Shah's user avatar

Kin ShahKin Shah

61.6k5 gold badges115 silver badges235 bronze badges

Few Basic ideas.

A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login).

A user is a database level security principal. Logins must be mapped to a database user to connect to a database. A login can be mapped to different databases as different users but can only be mapped as one user in each database. In a partially contained database, a user can be created that does not have a login. For more information about contained database users, see CREATE USER (Transact-SQL). If the guest user in a database is enabled, a login that is not mapped to a database user can enter the database as the guest user.

Logins are distinct from database users. You must map logins or Windows groups to database users or roles in a separate operation. You then grant permissions to users or roles to access database objects.

As mentioned by @Dan Guzman you need to create a user in the database [xxxx-xxxx] mapped to the login xxxx you are creating. Then you can run the 2nd section of your code.
Example:

CREATE LOGIN WanidaBenshoof   
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';  
USE AdventureWorks2012;  
CREATE USER Wanida FOR LOGIN WanidaBenshoof

In case you are thinking of contained user.
Contained Database Users — Making Your Database Portable

Reference:

  1. Create a Login
  2. Create a Database User

answered Oct 3, 2019 at 1:30

SqlWorldWide's user avatar

SqlWorldWideSqlWorldWide

12.6k3 gold badges25 silver badges50 bronze badges

Источник

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest

0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии

А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Microsoft sql server ошибка 15025
  • Microsoft sql server ошибка 1418

    • ➤