Is it possible to change owner of SP in Crystal reports at runtime?

Question

Hi, I'd like to change the owner of stored procedure (i.e. dbo) for smth else in crystal reports at runtime. Is it possible? TIA

UDP May be I need to clarify. I have a reports with lots of subreport within. I have to use this report with the other database. So I need to change the scheme of stored procedure at runtime without changing anything at DB.

Should I use SetDataSource() method or is there smth else more corresponding changing scheme task?

Answer 1

You can achieve the desired effect without changing the owner using the EXECUTE AS Clause.

CREATE PROCEDURE dbo.TestProcedure
WITH EXECUTE AS OWNER

There are basically five types of impersonation that can be used:

• SELF - the specified user is the person creating or altering the module
• CALLER - this will take on the permissions of the current user
• OWNER - this will take on the permissions of the owner of the module being called
• 'user_name' - a specific user
• 'login_name' - a specific login

See Sommarskog's canonical reference: Giving Permissions through Stored Procedures

Answer 2

To answer the original question, you can use the sp_changeobjectowner system sproc to change the owner of an object in the database.