January 28, 2019 at 1:43 pm
Hi,
Having some trouble seeing where the issue is here:
alter PROCEDURE pec.jpwPermissionTest1_20190128
with
EXECUTE AS owner
AS
BEGIN
-- this one fails because it is set to execute as owner, which becomes the owner of the DBO schema, which is SA
SELECT * FROM pecmaster.dbo.tGeoCountyEnum
END
go
alter PROCEDURE pec.jpwPermissionTest2_20190128
with
EXECUTE AS 'PREMIER\reportaccountrunner'
AS
BEGIN
-- even though reports is set up as a DB OWNER user on pecMaster, this fails (probably) because i'm emulating the *evips_prod* reports user, which has no cross-database trust to pecMaster
SELECT * FROM pecmaster.dbo.tGeoCountyEnum
END
GO
alter PROCEDURE pec.jpwPermissionTest3_20190128
with
EXECUTE AS 'PREMIER\jophsmith'
AS
BEGIN
-- even a super user account in the same example as #2 does not work, since its the cross-database trust that is the issue
SELECT * FROM pecmaster.dbo.tGeoCountyEnum
END
January 28, 2019 at 3:15 pm
krypto69 - Monday, January 28, 2019 1:43 PMHi,Having some trouble seeing where the issue is here:
alter PROCEDURE pec.jpwPermissionTest1_20190128
with
EXECUTE AS owner
AS
BEGIN
-- this one fails because it is set to execute as owner, which becomes the owner of the DBO schema, which is SASELECT * FROM pecmaster.dbo.tGeoCountyEnum
END
go
alter PROCEDURE pec.jpwPermissionTest2_20190128
with
EXECUTE AS 'PREMIER\reportaccountrunner'
AS
BEGIN
-- even though reports is set up as a DB OWNER user on pecMaster, this fails (probably) because i'm emulating the *evips_prod* reports user, which has no cross-database trust to pecMaster
SELECT * FROM pecmaster.dbo.tGeoCountyEnumEND
GO
alter PROCEDURE pec.jpwPermissionTest3_20190128
with
EXECUTE AS 'PREMIER\jophsmith'
AS
BEGIN
-- even a super user account in the same example as #2 does not work, since its the cross-database trust that is the issue
SELECT * FROM pecmaster.dbo.tGeoCountyEnumEND
With the cross database issues, you need to open things up more unless you go the more secure route of signing the stored procedure. I'd go that route:
Tutorial: Signing Stored Procedures with a Certificate
For a very thorough read on the subject you are dealing with, I think this is the one of the best articles on the subject:
Packaging Permissions in Stored Procedures
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply