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

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?

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.

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]