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

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

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

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