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

bibi92
bibi92 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
x-menIT super hero

Commented:
WITH EXECUTE AS OWNER
bibi92

Author

Commented:
I have tried this but it doesn't work. Thanks bibi
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Register
bibi92

Author

Commented:
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
sorry:
AS DBO

sa is the login, dbo is the user.
bibi92

Author

Commented:
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
Yes, thanks, I know that but I search a workaround.
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
the only workaround is this:
* create a sql agent job, unscheduled
* instead of your current procedure, you could request the above job to be started sp_start_job

that's what I do to give "permissions" to users that they should not have directly
bibi92

Author

Commented:
Thanks bibi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial