Avatar of bibi92
bibi92
Flag for France asked on

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

Avatar of undefined
Last Comment
bibi92

8/22/2022 - Mon
x-men

WITH EXECUTE AS OWNER
bibi92

ASKER
I have tried this but it doesn't work. Thanks bibi
Guy Hengel [angelIII / a3]

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

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]

sorry:
AS DBO

sa is the login, dbo is the user.
bibi92

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]

permissions requirments for sys.database_mirroring
http://msdn.microsoft.com/en-us/library/ms178655.aspx
bibi92

ASKER
Yes, thanks, I know that but I search a workaround.
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bibi92

ASKER
Thanks bibi
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck