December 3, 2013 at 11:21 pm
Hi,
with reference to http://technet.microsoft.com/en-us/library/ms188354.aspx
USE AdventureWorks2012;
GO
CREATE PROCEDURE HumanResources.uspEmployeesInDepartment
@DeptValue int
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;
SELECT e.BusinessEntityID, c.LastName, c.FirstName, e.JobTitle
FROM Person.Person AS c
INNER JOIN HumanResources.Employee AS e
ON c.BusinessEntityID = e.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE edh.DepartmentID = @DeptValue
ORDER BY c.LastName, c.FirstName;
GO
-- Execute the stored procedure by specifying department 5.
EXECUTE HumanResources.uspEmployeesInDepartment 5;
GO
Msg 15517, Level 16, State 1, Procedure uspEmployeesInDepartment, Line 0
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.
I've google around for the solution most solution mentioned to change owner ship to sa.
I don't believe in such solution since it does not make sense
exec sp_helpdb
name db_size owner dbid created status compatibility_level
AdventureWorks2012 205.75 MB User-PC\User 5 Dec 3 2013 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled 110
practise 5.08 MB User-PC\User 6 Dec 3 2013 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110
it clearly state that the owner is user-pc\user, I've try creating my own database, creating an table and execute the procedure it does not have this type of problem.
CREATE DATABASE practise
USE practise
CREATE TABLE dbo.SUPPLY1 (
supplyID INT CONSTRAINT SUPPLY1_pk PRIMARY KEY CONSTRAINT SUPPLY1_chk CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
);
INSERT dbo.SUPPLY1 VALUES ('1', 'CaliforniaCorp'), ('5', 'BraziliaLtd');
SELECT * FROM dbo.SUPPLY1
supplyID supplier
1 CaliforniaCorp
5 BraziliaLtd
CREATE PROCEDURE getSUPPLY1
@supplyID int
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT * FROM dbo.SUPPLY1 WHERE supplyID=@supplyID
END
GO
EXEC getSUPPLY1 @supplyID=5
supplyID supplier
5 BraziliaLtd
Is there a way to run migrate the owner of attached database?
I suspect it has some thing to do with the principal of an attached database are actually orphaned? Am I right? Am I missing something?
thanks a lot!
December 3, 2013 at 11:41 pm
The problem occurs when the owner of the database does not map to a login on that instance. The solution is, as per those google results, change the owner to 'sa' or, in fact, to any other valid login on that instance. I prefer 'sa' as then I know I'm not accidentally escalating someone's permissions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply