Sql server ошибка 15517 - Исправление ошибок и поиск оптимальных решений проблем

Remove From My Forums

Question
please need help ..

this the wrong message shown in my log

An exception occurred while enqueueing a message in the target queue. Error: 15517<c/> State: 1. Cannot execute as the database principal because the principal «dbo» does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.

MCP MCSA MCSE MCT

All replies

Most probably the ownership of the queue’s database is broken somehow. Try to ensure the owner is a valid server login. You may do that by running ALTER AUTHORIZATION on DATABASE::[your_db_name] to [some_valid_login]
- Proposed as answer by
  
  Friday, July 15, 2011 7:45 AM
Most probably the ownership of the queue’s database is broken somehow. Try to ensure the owner is a valid server login. You may do that by running ALTER AUTHORIZATION on DATABASE::[your_db_name] to [some_valid_login]

a new error shown

Cannot show requested dialog.

Additional information:

Cannot show requested dialog. (SqlMgmt)

Property Owner is not available for Database ’[ ]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

(Microsoft.SqlServer.Smo

MCP MCSA MCSE MCT
{$content}

The link you provided is very useful, Now I have a more
clear idea about it.
This error is related to an orphaned database owner on your database. This can be verified by either looking
at object explorer details and identifying the database(s) without an entry in the Owner column or running a select against sys.databases to see the database owner that is no longer valid.

Once you have the databases then you can run:

USE [DB Name]
GO
EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false
GO

This will produce the result:

The dependent aliases were dropped.

And now the issue should be corrected and the errors will stop being generated in the Windows event log and SQL Server Error Log.
- Proposed as answer by
  Kieran Patrick Wood
  Wednesday, April 11, 2018 6:17 PM
Thanks Brown.. It really Helped.

USE [DB Name]
GO
EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false
GO
- Proposed as answer by
  Kieran Patrick Wood
  Wednesday, April 11, 2018 6:17 PM
Excelente, muchas gracias por su aporte.
Cobrow, it really works. Thanks..
Best response to correct this error and it worked perfectly! I got the error when upgrading from 2014 to 2016. Thank you very much!
I’ve restored a database into a different machine and was having the same issue:

«Cannot execute as the database principal because the principal «dbo» does not exist, this type of principal cannot be impersonated, or you do not have permission.»

The command suggested by cobrow worked perfectly for me:
```
USE [DB Name]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
```
Thank you!
- Proposed as answer by
  Kieran Patrick Wood
  Wednesday, April 11, 2018 6:17 PM
But how do you determine which DB Name to use? How did you determine the DB this is happening on?

Last week I had the following error message repeating over and over again in the SQL Server log of one of my servers. It was repeating so much that the logs were growing very large, very fast:

Message
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal «dbo» does not exist, this type of principal cannot be impersonated, or you do not have permission.

If you do a search for this error, you won’t find a whole lot on it. You’ll find that if you can figure out what database it is, then you should change the owner of the database.

First, check sys.databases:

Query sys.databases joined with sys.server_principals like so:

SELECT d.name AS 'Database', s.name AS 'Owner'
FROM sys.databases d
  LEFT JOIN sys.server_principals s
    ON d.owner_sid = s.sid;

You’re looking for databases owned by logins you know are no longer valid. We had a DBA recently retire and found a few that way.

Second, check to see if sys.database_principals and sys.databases match up:

We were still getting the errors and by looking at sys.databases, nothing was showing up as being wrong. However, that error means there is a mismatch with dbo matching up to a login. That means you have to take it a step further and query sys.database_principals and see how dbo matches up. For a particular DB, say Example, here’s the type of query you’d run:

SELECT sp.name AS 'dbo_login', o.name AS 'sysdb_login'
FROM Example.sys.database_principals dp
  LEFT JOIN master.sys.server_principals sp
    ON dp.sid = sp.sid
  LEFT JOIN master.sys.databases d
    ON DB_ID('Example') = d.database_id
  LEFT JOIN master.sys.server_principals o
    ON d.owner_sid = o.sid



  


                       
  




WHERE dp.name = 'dbo';

Obviously, if you wanted to run it for all DBs, you’d do something like:

EXEC sp_MSForEachDB
'SELECT ''?'' AS ''DBName'', sp.name AS ''dbo_login'', o.name AS ''sysdb_login''
FROM ?.sys.database_principals dp
  LEFT JOIN master.sys.server_principals sp
    ON dp.sid = sp.sid
  LEFT JOIN master.sys.databases d
    ON DB_ID(''?'') = d.database_id
  LEFT JOIN master.sys.server_principals o
    ON d.owner_sid = o.sid



  


                       
  




WHERE dp.name = ''dbo'';';

By doing this, I found several databases that sys.databases said had an owner. However, when I checked it from the database’s sys.database_principals, the SID didn’t match up for dbo. The column I had for dbo_login came back NULL. That was a clear sign of the issue. There is also the possibility you will see a mismatch between dbo_login and sysdb_login. It appears that as long as dbo_login matches a legitimate login, the error is not generated. I found that on some DBs on one of my servers. While it’s not causing a problem now, I’ll be looking to correct the mismatch.

Correcting the Error:

The easiest way to correct the error is to use ALTER AUTHORIZATION on the databases which have the NULL login match for dbo. It’s as simple as:

ALTER AUTHORIZATION ON DATABASE::Example TO sa;

Источник

Could not delete publication ‘XXX’. Microsoft SQL Server, Error: 15517

Hey friends, I am filling good to write my 1st post in 2015. This is related to error in replication which i faced in 2014 he he.

Last month i was facing an error during roll backing Transnational replication as below..

——————————
Could not delete publication ‘XXX’. Microsoft SQL Server, Error: 15517

TITLE: Microsoft.SqlServer.ConnectionInfo

——————————
SQL Server could not disable publishing and distribution on ‘SUMAN-29’.

Cannot execute as the database principal because the principal «dbo» does not exist, this type of principal cannot be impersonated, or you do not have permission.
Changed database context to ‘master’. (Microsoft SQL Server, Error: 15517)

I resolved through below :-

1st run below query in SSMS.

ALTER AUTHORIZATION ON DATABASE::[DBNAME] TO [sa]

2nd Then Right click On publication and choose delete

3rd Then disable the distributor..

Now just refresh instance

Popular posts from this blog

For encrypting and decrypting , we must use the bytea data type on the column which we implement. Bcoz bytea will use the pgcrypto method by default. However, you will need to create the pgcrypto extension to enable these functions as they are not pre-defined in PostgreSQL/PPAS. Example CREATE EXTENSION pgcrypto; CREATE TABLE userinfo (username varchar(20), password bytea); >> Inserting the data in an encrypted format INSERT INTO userinfo VALUES(‘ suman ‘,encrypt(‘111222′,’password’,’aes’)); select * from userinfo ; >> Retrieving the data as decrypted format SELECT decrypt(password,decode(‘password’,’escape’::text),’aes’::text) FROM userinfo; Thanks for reading Plz dont forget to like Facebook Page.. https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

It was Monday 9 th Jun 47 degr. temperature of Delhi-NCR. Temperature was like boiling me and database. When I reached my office( @ 8.45 am) got an alert from one of Server. “MSDB is in suspected mode” At the same time comes in my mind, this issue will boil me today.. I just tried to cool my self through cold drink then connected server from my local system using windows authentication mode..

Last week I got this error from one of developer who was trying to deploy his project from Testing server to SQL Azure QA server. He was using “Deploy Database to SQL Azure” option from SSMS Tool-Task option. After connecting to SQL Azure portal when operation started to deployment below errors occurs. Validation of the schema model for data package failed. Error SQL71562: Error validating element xx.xxx.xx:function .dbo.xxx has an unresolved refrence to object xx.dbo.xxxx external refrences are not supported when creating a package from this platform . Reason: The reason of the this error was; some functions of project was dependent on master database and only single database was being deploy to SQL Azure. DACFx must block Export when object definitions (views, procedures, etc.) contain external references, as Azure SQL Database does not allow cross-database external references So, this error was coming. Solution : I suggested him to create those function to locally

Источник

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

Question

All replies

Could not delete publication ‘XXX’. Microsoft SQL Server, Error: 15517

Popular posts from this blog