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
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 ...
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.