1

I have a stored procedure that connects to 2 databases. This stored procedure will be used in one of the SSRS reports (SSRS has permission for both databases).

But I only have access to the database where the stored procedure reside.

When I try to alter the stored procedure, I get an error that I don't have permission for the 2nd database.

Is there any way to alter the stored procedure without validating the script inside it?

P.S. dynamic SQL is not acceptable where I work

ALTER PROCEDURE DB1.[DB1].[SP1]
AS
BEGIN
    SELECT *
    FROM Server2.DB2.dbo.Table2 AS T2
    INNER JOIN DB1.dbo.Table1 AS T1 ON T1.ID = T2.ID 
END

Error

Msg 18456, Level 14, State 1, Line 8
Login failed for user 'CompanyName\MyUserID'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
asmgx
  • 7,328
  • 15
  • 82
  • 143
  • Possible duplicate of [Is there a way to prevent SQL Server from Validating the SQL in a stored procedure during CREATE / ALTER](https://stackoverflow.com/questions/7558184/is-there-a-way-to-prevent-sql-server-from-validating-the-sql-in-a-stored-procedu) – Ken White Mar 05 '19 at 01:08
  • 1
    @Ken, that answer says to use dynamic SQL, while OP's question here indicates they need a solution that doesn't use dynamic SQL. – Richard II Mar 05 '19 at 01:10
  • see if something here could help: https://dba.stackexchange.com/questions/81566/create-alter-stored-procedure-without-compiling – Richard II Mar 05 '19 at 01:10
  • @RichardII: The linked duplicate asks the same question, and does not specify with or without dynamic SQL. The fact that the accepted answer uses dynamic SQL doesn't change the question. It's also telling that the question did not get alternative solutions, and that the link you provided says it's not possible otherwise. – Ken White Mar 05 '19 at 01:24
  • Not possible: https://dba.stackexchange.com/questions/148047/deferred-name-resolution-for-linked-servers – Alex Mar 05 '19 at 04:33
  • Can I update records manually in sys.sql_modules table? – asmgx Mar 05 '19 at 04:37
  • No, you can't... – Alex Mar 05 '19 at 04:41
  • @Alex just found this https://dba.stackexchange.com/questions/73557/script-that-alters-all-stored-procedure-on-the-database – asmgx Mar 05 '19 at 04:42
  • The link above has nothing to do with updating sys.sql_modules table. – Alex Mar 05 '19 at 04:45
  • @Alex I think the table I need is sys.Procedures not sys.sql_modules – asmgx Mar 05 '19 at 04:47
  • You better invest your efforts into getting access to the other DB, as you cannot (and should not) update system DBs directly. – Alex Mar 05 '19 at 04:49
  • @Alex when you say "should not", that means it is possible.. even if in my case this is not the best way to update SP, but this could help other people in their case to update SP from system table is the only way. – asmgx Mar 05 '19 at 04:51
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189426/discussion-between-alex-and-asmgx). – Alex Mar 05 '19 at 04:53
  • @KenWhite - So he can't ask a question because there's a similar question that has an answer that the OP **can't use**? Just goes to show how LAME these close votes are. I guess if I ask about the Meaning of Life, I'll get a close vote because there's a question about 6 times 7? – Hannover Fist Mar 06 '19 at 00:51
  • @HannoverFist: I didn't say anyone couldn't ask whatever they like. I said there was an existing duplicate. If you disagree, don't vote to close as a duplicate. – Ken White Mar 06 '19 at 00:53

0 Answers0