Sql error 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.

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 ShahKin Shah

61.6k5 gold badges116 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:

Create a Login
Create a Database User

answered Oct 3, 2019 at 1:30

SqlWorldWideSqlWorldWide

12.6k3 gold badges25 silver badges50 bronze badges

Источник

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.

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

Источник

SQL Server 2012 Developer SQL Server 2012 Enterprise SQL Server 2012 Express SQL Server 2012 Standard SQL Server 2012 Web SQL Server 2014 Standard SQL Server 2014 Express SQL Server 2014 Developer SQL Server 2014 Enterprise SQL Server 2014 Web SQL Server 2016 Developer SQL Server 2016 Enterprise SQL Server 2016 Enterprise Core SQL Server 2016 Standard Еще…Меньше

Проблемы

Вы создаете полнотекстовый индекс, использующий полнотекстовый каталог в Microsoft SQL Server. Если каталог очень большой, SQL Server может создать некоторые схемы и функции секционирования. Эти данные сохраняются в файловых группах. Это поведение ожидается. Однако после того как вы удалите полнотекстовый индекс и каталог, эти схемы и функции секционирования не удаляются из файловых групп. Кроме того, при попытке удалить файловые группы, содержащие схемы и функции секционирования, появляется сообщение об ошибке, подобное следующему:

Сообщение 5042, уровень 16, состояние файловой группы ‘ ‘filegroup_name‘ ‘ не может быть удалено, так как оно не пустое.

При попытке удаления схем секционирования и функций появляются сообщения об ошибках, похожие на приведенные ниже.

Сообщение 15151, уровень 16, состояние 1Cannot Drop схема секционирования «scheme_name«, так как она не существует или отсутствует разрешение. Сообщение 15151, уровень 16, состояние 1Cannot отменяет функцию секционирования «function_name«, так как она не существует или отсутствует разрешение.

Решение

Эта проблема впервые устранена в следующем накопительном обновлении SQL Server:

Накопительное обновление 6 для SQL Server 2014 с пакетом обновления 1 (SP1)
Накопительное обновление 1 для SQL Server 2016
Накопительное обновление 13 для SQL Server 2014
Накопительное обновление 10 для SQL Server 2012 с пакетом обновления 2
Накопительное обновление 2 для SQL Server 2012 с пакетом обновления 3 (SP3)

Примечание.Сведения о последних сборках SQL Server можно найти в разделе где найти сведения о последних сборках SQL Server.

Статус

Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе «Применяется к».

Ссылки

Ознакомьтесь с терминологией , которую корпорация Майкрософт использует для описания обновлений программного обеспечения.

Нужна дополнительная помощь?

Источник

Содержание

Microsoft sql server error 15151
Answered by:
Question
Answers
Microsoft sql server error 15151
Asked by:
Question
All replies
15151 Error SQL Server | Solution Revealed
15151 error on the SQL server
Conclusion
PREVENT YOUR SERVER FROM CRASHING!
Microsoft sql server error 15151
Question
All replies

Microsoft sql server error 15151

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I downloaded the SQL Server 2017 and opened the manager as a adminstrator.

In the management studio, I cannot create a new database. It returns error 262, I don’t have privileges.

So I followed this guide https://stackoverflow.com/questions/24198458/how-to-fix-microsoft-sql-server-error-262 but it didn’t work. Error 15247, I don’t have permission to create a new user with sysadmin privilege.

I tried to change my user permission to have sysadmin privilege, however it gave me error 15151.

I am loging with windows authentication. I, however, can’t choose to login with «sa» because the user box is grayed out.

I clicked on sa > Properties > Status and changed the Logon option to enabled. Again, error 15151.

Could you help me with this? I tried to re-install and still didn’t work. It seems I don’t have permission for anything.

Answers

Running SSMS as admin won’t let you in as the admin of SQL Server. You’ve got to have admin access to the SQL instance specifically.

I believe you missed a couple of items while installing SQL Server —missed clicking «Add current user» as the administrator and typing password for the «SA» user while on the installation wizard, which seems to have caused this. Nevertheless, there are ways you can hack into SQL Server through back door (if you’re the local admin on the windows box where SQL is hosted). Once you are in, you can grant yourself sysadmin rights and then you’ll be able to log in using SSMS normally (as the sysadmin user).

I am posting the links to the articles that talk about how that is done.

Another not-so-good approach (only if you’re working with a brand new instance) is to reinstall SQL instance and this time make sure to add «sa» password to allow mixed authentication and add yourself as the admin on the installation wizard.

Please remember to click «Mark as Answer» if my response answered your question or click «Vote as helpful» if it helped you in any way.

Источник

Microsoft sql server error 15151

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 15151, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

What was you trying to do when you encountered this error? Include full details including the SQL Serve version(s) involved, the OS Versions, what was you trying to upgrade/patch/install.

Please click «Mark As Answer» if my post helped. Tony C.

Might be help Solution of the problem :-

Start SQL Server service with Trace Flag 902:
- >Net Start MSSQL$InstanceName /T902
Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under Program FilesMicrosoft SQL ServerMSSQL11.MSSQL$InstanceName MSSQL
Stop SQL Server services:
- >Net Stop MSSQL$InstanceName
Start SQL server service from SQL Server configuration manager
Add SSISDB back to Availability Group

Please click Mark As Answer if my post helped.

Microsoft SQL Server Management Studio 12.0.4213.0
Microsoft Analysis Services Client Tools 12.0.4213.0
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.9600.18125
Microsoft .NET Framework 4.0.30319.34209
Operating System 6.3.9600

Get error when trying to execute

Please refer to my earlier reply; without the required information we cannot help you out here; the error you have posted can occur for any number of reasons.

An example is regarding the post re SSISDB; this Database was introduced with SQL Server 2012; if you’re attempting to patch a 2008 SQL Server, or upgrade a 2008 SQL Server 20 2012 this does not apply.

Another reason for this could be you’re attempting to upgrade from 2008 Enterprise Edition to 2012 Standard Edition; this won’t work either as there are features in Enterprise that are not available in Standard.

Another reason could be because you have detached databases.

Please click «Mark As Answer» if my post helped. Tony C.

12.0.4213.0 = SQL 2014

I am not trying to upgrade from 2008 to 2012. It is already on 2014. It is part of a SQL cluster and since update, will not fail over to other node.

so I assume that the cluster is working on one node?

I will have a look to see if there is a solution. One possible solution would be to drop the failing node from the cluster; delete all SQL Server content and then try and reintroduce the node?

Please click «Mark As Answer» if my post helped. Tony C.

The Service Pack 2 update for SQL Server 2012 has a hotfix that needs to be applied to the SSISDB database. If the SSISDB database is involved in an Availability Group, it cannot apply the hotfix. To by DNSUnlocker»>WORK around this, you need to remove the SSISDB from the Availability Group, apply the hotfix then add the SSISDB back into your Availability Group.

For this kind of error, you need to do the following:

Start the MSSQLSERVER instance with trace flag 902 from your cmd window as shown below.

In SQL Server Management Studio (SSMS), remove the SSISDB database from the Availability Group. Alternatively, you may execute the following statement. For this step, you will need CONTROL SERVER permission or at least ALTER AVAILABILITY GROUP permissions.

From your primary server, add the SSISDB database back into your Availability Group. Alternatively, you execute the following statement:

After this, you should be able to check that the build number for SQL Server 2012 is something like 11.0.5058 within SSMS. Your Service Pack 2 patch is complete

Please click Mark As Answer if my post helped.

Источник

15151 Error SQL Server | Solution Revealed

by Shahalamol R | Sep 21, 2022

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

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:

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.

Источник

Microsoft sql server error 15151

Question

Hi there, we ran into the above issue over the weekend when installing SP3 on a instance and it seems like the work around is the steps below while you have the failure and master db is corrupt .

Start SQL Server service with Trace Flag 902:
- >Net Start MSSQL$InstanceName /T902
Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under Program FilesMicrosoft SQL ServerMSSQL11.MSSQL$InstanceName MSSQL
Stop SQL Server services:
- >Net Stop MSSQL$InstanceName
Start SQL server service from SQL Server configuration manager
Add SSISDB back to Availability Group

My question is will it also not work to remove SSISDB from Availability Group and detaching it, before you start installing SP3 ?

With this approach you wont have any issues during your SP3 install.

Could you please provide more detailed information about your issue so we can have a better understanding about your issue? Based on your description, it seems you encounter this issue during upgrade, I would suggest you follow the instructions in BOL to locate the log files then upload it to some shared storage(OneDrive,Dropbox) and share the link here so we can have a better understanding about the issue. Also, I don’t think this workaround would work in your scenario as it’s applies to availability group scenario and you are having different error message.

If you have any other questions, please let me know.

Edited by Lin Leng Microsoft contingent staff Tuesday, July 26, 2016 7:52 AM link

Thanks for getting back to me.

We have 2 Instances on a SQL Server 2012 Enterprise server with SP2 installed. The one instance does’nt have SSIS catalog configured and the other one not. There’s also no Availibilty Groups configured and SSISDB is not part of one either.

SP3 installed successfully on the one instance without SSIS catalog.

SP3 installation on the instance with SSIS catalog configured failed, which corrupted the master db and we had to rebuild master and restore it from previous back to get the instance back up with SP2 as it was previously installed.

Below are the exact sequence of errors we got .

Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 15151, state 1, severity 16.
This is a serious error condition which might interfere with regular operation and the database will be taken offline.
If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting.
Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.
For more information about how to rebuild the master database, see SQL Server Books Online.

The only things I could find on the net relating to these above errors is reffering to the SSISDB being part of an Availibilty Group, but it is NOT. I’m also not sure if this SSISDB was maybe restored from a server where it was part of a Availibilty Group to get all the packages etc onto the server and maybe theres a DB setting thats making this SSISDB thinking it’s possibly part of a Availibility Group .

Источник

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

asked Oct 2, 2019 at 23:29

You are missing create user before you alter it.

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 ShahKin Shah

61.6k5 gold badges116 silver badges235 bronze badges

Few Basic ideas.

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:

Create a Login
Create a Database User

answered Oct 3, 2019 at 1:30

SqlWorldWideSqlWorldWide

12.6k3 gold badges25 silver badges50 bronze badges

Источник

I am trying to install DNN using Web Platform Installer. It tries to connect to .SQLEXPRESS, which I have installed along with SQL Server, and wants me password for sa. When I provide it
with a password, it says «password invalid or cannot connect to database».

I thought to myself there might be a problem with the sa account. I opened SSMS 2012. There is a red arrow downward sign on the sa account. Whenever I am trying to change login status to Granted it opens up an error message:

error 15151
Cannot alter the login ‘sa’ because it does not exist or you don’t have permission.

Nonetheless I tried it with administrator account and also by disabling UAC. None worked. Screen shot follows:

Details:

SQLEXPRESS version: 10.0.2531
SQL Server version: 11.0.2100
Update: Following some guidelines I tried to change authentication in SQL Server Management Studio from Windows Authentication to Mixed Authentication, but it failed with the following permission error:

asked Nov 23, 2014 at 4:09

Mehdi HaghgooMehdi Haghgoo

2,9245 gold badges45 silver badges87 bronze badges

Make sure you’re connecting with a server login that has the sysadmin server-level role. You’ll need that permissions level to do what you’re trying.

Check the server authentication mode. If it is or was Windows Auth only, the sa account is automatically disabled. Note that if SQL Authentication was disabled and later turned back on, the sa account will still be disabled.

If you find that nobody is in the sysadmin server-level role, you’ll need to stop the server and restart it in single-user mode so you can add at least one login to the sysadmin server-level role. In single-user mode, the Administrators group has sysadmin access, but you’ll have a limited subset of commands to manipulate data in tables (single user is for fixing servers, not running applications). NB: Only one connection is allowed in this mode, so if you have a broker or service that’s trying to connect you’ll need to disable that or it can take the session you were planning to use with SSMS.

answered Nov 23, 2014 at 4:23

Bacon BitsBacon Bits

30k5 gold badges56 silver badges63 bronze badges

Источник

15151 error on the SQL server

Case 1

Case 2

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Проблемы

Решение

Нужна дополнительная помощь?

Microsoft sql server error 15151

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Answered by:

Question

Answers

Microsoft sql server error 15151

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Asked by:

Question

15151 Error SQL Server | Solution Revealed

15151 error on the SQL server

Case 1

Case 2

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Microsoft sql server error 15151

Question