how to execute a stored procedure like sysadmin or workaround for checking mirroring

Hello,

I try to create a stored prod for checking db mirroring without be sysadmin :
create procedure check_mirror WITH EXECUTE AS ... AS

set nocount on;
SELECT a.name +'|'+ CAST(isnull(b.mirroring_role,'') AS varchar(15)) +'|'+ CAST(isnull(b.mirroring_partner_instance,'') AS varchar(15))from sys.databases a INNER JOIN sys.database_mirroring b ON a.database_id=b.database_id where mirroring_role is null or mirroring_role=1 or mirroring_role=2

Thanks
bibi

Microsoft SQL Server 2008

Last Comment

bibi92

8/22/2022 - Mon

x-men

12/24/2012

WITH EXECUTE AS OWNER

bibi92

12/24/2012

ASKER

I have tried this but it doesn't work. Thanks bibi

Guy Hengel [angelIII / a3]

12/24/2012

you need to create procedure being sysadmin

create procedure check_mirror WITH EXECUTE AS SA 
AS
 ...

Open in new window

and grant the permissions of the procedure to the user that needs to call this procedure.

if this does not work, what error do you get?

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

bibi92

12/26/2012

ASKER

Hello,

The error is Msg 15151, Level 16, State 1, Procedure test, Line 3
Cannot execute as the user 'SA', because it does not exist or you do not have permission.
But I try to create the procedure with sysadmin account.

Thanks

Regards

bibi

Guy Hengel [angelIII / a3]

12/26/2012

sorry:
AS DBO

sa is the login, dbo is the user.

bibi92

12/26/2012

ASKER

Thanks I have tried with dbo but it doesn't work. There is no error but I can't query the sys.database_mirroring.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Guy Hengel [angelIII / a3]

12/26/2012