Ошибка 15517 ms sql как исправить - Не ошибается лишь тот, кто ничего не делает!

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?

Newbie DBA here, just getting settled into my first DBA job and trying to figure out how things work outside of a classroom environment. Your patience is appreciated.

I’m trying to create diagrams for all the databases so I can try and get a grasp on how things are laid out here and a problem I’m running into is that when I attempt to create a diagram for one database in particular I’m getting the
following error:

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. (Microsoft SQL Server, Error: 15517)

I’ve been searching for more info on this issue and it looks like it usually is caused by the owner of the database getting deleted but this database shows SA as the owner so that shouldn’t be the issue. I’m not even able to create a diagram if I sign in as sa…

Not sure if this may have something to do with it but this particular database was migrated to a new server a few months ago so something may have broken when that was done (although the database is live and in use so it isn’t entirely broken).

Maybe its looking for the sa account from the old server and it just needs to be updated to use the sa account on the current server?

Edit: This DB may not actually have an owner after all. Performing a Right click > Properties > Files on the database shows no owner for the database even though the owner field under general does.

I tried to change it through the GUI and was informed that set owner failed with the following error:

Lock request time out period exceeded. (Microsoft SQL Server, Error:
1222)

I also tried the alter authorization ... statement and it just never completes (or I cancelled it after a few minutes to be more accurate). Am I being to impatient with that statement or is something else going on here?

Источник

Today’s blog post is directly inspired by the conversation I had during my Comprehensive Database Performance Health Check. During the consulting engagement, one of the developers reported below error in the database while trying to open a database diagram.

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. (Microsoft SQL Server, Error: 15517)

As per message, it is clear that something is not right with database principal dbo. I asked history about it and learned that this database was restored from some other server. I was able to run some queries and find the issue.

To verify if you are running into the same issue, you can run below query to find who is mapped to “dbo” in the database. My sample database name is AdventureWorks, so please change accordingly.

USE AdventureWorks
GO
SELECT SUSER_SNAME(sid), * from sys.database_principals

If the first column shows as NULL then below fix would work.

WORKAROUND/SOLUTION

This was just a different variation of error which I have explained in my earlier blog. SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed

We went ahead and changed the owner of the database in the UI (Properties > Files tab)

Here is the equivalent command.

USE [AdventureWorks]
GO
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa]
GO

After doing this, the issue was resolved, and they were able to use this feature?

How many of you use this old feature? Truly speaking, I have not seen many using it in production.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Источник

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?

Источник

Remove From My Forums

Question
Hello,

I have an .mdf file that I tried to attach in SSMS. I am using (LocalDB)MSSQLLocalDB (SQL Server 12.0.2569) The attach process seems to have completed successfully but when I want to make any changes at all I get the message:

«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 (Microsoft SQL Server, Error: 15517)»

I can see the database diagram in VS when I create and Entity Framework Model but I am unable to perform any functions in SSMS like being able to establish relationships and the like.

I would appreciate some guidance on this.

Thank you.

Answers

Is this what you are looking for?

https://msdn.microsoft.com/en-us/library/ms178630.aspx
- Marked as answer by
  
  Saturday, November 17, 2018 6:35 PM
- Marked as answer by
  Geol4549
  Wednesday, January 24, 2018 3:47 PM

Источник

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

Источник

Новичок DBA, только что устроился на свою первую работу DBA и пытаюсь понять, как все работает вне классной комнаты. Ваше терпение высоко ценится.

Я пытаюсь создать диаграммы для всех баз данных, чтобы попытаться понять, как здесь все устроено, и столкнулся с проблемой: когда я пытаюсь создать диаграмму для одной базы данных в частности, я получаю следующую ошибку
следующую ошибку:

Cannot execute as the database principal because the principal «dbo»
не существует, этот тип принципала не может быть имперсонифицирован, или у вас
не имеете разрешения. (Microsoft SQL Server, ошибка: 15517)

Я искал больше информации об этой проблеме, и похоже, что она обычно вызвана удалением владельца базы данных, но эта база данных показывает SA в качестве владельца, так что это не должно быть проблемой. Я даже не могу создать диаграмму, если вхожу под именем sa…

Не уверен, что это может быть как-то связано с этим, но эта конкретная база данных была перенесена на новый сервер несколько месяцев назад, так что что-то могло сломаться, когда это было сделано (хотя база данных живая и используется, так что она не совсем сломана).

Возможно, она ищет учетную запись sa на старом сервере, и ее просто нужно обновить, чтобы использовать учетную запись sa на текущем сервере?

Редактирование: Возможно, у этой БД на самом деле нет владельца. Выполнение Правого щелчка > Свойства > Файлы на базе данных показывает отсутствие владельца для базы данных, хотя в поле «Владелец» в разделе «Общие» он есть.

Я попытался изменить его через GUI и получил сообщение, что установить владельца не удалось со следующей ошибкой:

Превышен период ожидания запроса на блокировку. (Microsoft SQL Server, Error:
1222)

Я также попробовал выполнить оператор alter authorization ..., но он просто не завершается (или я отменил его через несколько минут, если быть более точным). Я слишком нетерпелив с этим оператором или здесь происходит что-то другое?

Источник

<img decoding="async" alt="RRS feed" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001" />

Question

All replies

WORKAROUND/SOLUTION

<img decoding="async" alt="RRS feed" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001" />

Question

All replies

Question

Answers

Question

All replies

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

Popular posts from this blog

Не пропустите эти материалы по теме: