Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. You cannot run a statement or a module that includes the EXECUTE AS clause after you restore a database in SQL Server 2005

You cannot run a statement or a module that includes the EXECUTE AS clause after you restore a database in SQL Server 2005

Bug #: 397455 (SQLBUDT)

↑ Back to the top

Symptoms

When you run a statement or a module that includes the EXECUTE AS clause in Microsoft SQL Server 2005, you may receive the following error 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.
This problem occurs when all the following conditions are true:
  • You back up a database from an instance of SQL Server 2005. Then, you restore the database to an instance of SQL Server 2005 that is installed on another computer.
  • The statement or the module is executed as the dbo (database owner) user.
  • The owner of the database is a domain user or a SQL Server authorization login.

    Notes
    • If a domain user owns the database, the computer on which you restore the database cannot access the domain.
    • If a SQL Server authorization login owns the database, the login does not exist in the new instance of SQL Server 2005.

↑ Back to the top

Cause

This problem occurs because SQL Server 2005 cannot obtain the information about the context when you try to impersonate a database user to run a statement or a module.

SQL Server cannot obtain the information about the context that you are trying to impersonate under the conditions that are listed in the "Symptoms" section. If you impersonate a SQL Server authorization login, SQL Server cannot find a login that matches the security identifier (SID) of the impersonated user. If you impersonate a domain user, the domain controller cannot find the information about the specific user who matches the SID of the impersonated user.

↑ Back to the top

Workaround

To work around this problem, change the database owner to a valid login or domain user. To do this, run the following statements: 
USE <DatabaseName>
GO
sp_changedbowner '<NewLogin>'
Note <DatabaseName> represents the name of the database. <NewLogin> represents the name of the login that you want to set.

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top

Keywords: kb, kbexpertiseadvanced, kbprb, kbsql2005engine

↑ Back to the top

Article Info
Article ID : 913423
Revision : 7
Created on : 8/19/2020
Published on : 8/20/2020
Exists online : False
Views : 73