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
bibi92Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
0
 
x-menIT super heroCommented:
WITH EXECUTE AS OWNER
0
 
bibi92Author Commented:
I have tried this but it doesn't work. Thanks bibi
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Start This Course
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
 
bibi92Author 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry:
AS DBO

sa is the login, dbo is the user.
0
 
bibi92Author 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
permissions requirments for sys.database_mirroring
http://msdn.microsoft.com/en-us/library/ms178655.aspx
0
 
bibi92Author Commented:
Yes, thanks, I know that but I search a workaround.
0
 
bibi92Author Commented:
Thanks bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here