Cant modify sp_admessage - Internals Mystery!!! Please Help!!!

Due to legacy issues we want to modify sp_addmessage. I am able to open it it up change the code I want to add but it wont compile.

All I am doing is changing:

-- Must be ServerAdmin to manage messages
if is_srvrolemember('serveradmin') = 0
begin
raiserror(15247,-1,-1)
return (1)
end

To this code:

if (not (is_srvrolemember('sysadmin') = 1)) and ((@.severity > 18) or
(rtrim(upper(@.with_log)) = 'TRUE'))
begin
raiserror(15042,-1,-1)
return (1)
end

When we recompile I get:

Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 99
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 131
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 135
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 136
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 152
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 156
Incorrect syntax near '%'.

When we goto that line I see:

EXEC %%ErrorMessage(ID = @.msgnum).Lock(Exclusive = 1)

What is the "%%" doing - Does this make it impossible to modify?

Thanks,

Andy

In SQL SERVER 2005, majority of the system stored procedures including sp_addmessage exist in the read only resource database. You cannot modify them.

If you are creating your own procedure you cannot the "%%" syntax. This syntax is a hook to call functions inside the sqlservr.exe binary and is not available for user procedures.

Are you trying to restrict the roles that can call sp_addmessage ?

|||

Hi,

I have a similar problem, but want to solve it different.

Problem: sp_addmessage can only be called by sysadmin or serveradmin. But also normal users (with ddladmin/security/read/writer-role) should be able to call sp_addmessage.

Possible solution: I wrote a wrapper-procedure sp__addmessage which calls sp_addmessage with more rights:

create procedure sp__addmessage

(@.msgnum int, @.severity smallint, @.msgtext nvarchar(510),

@.lang sysname )

WITH execute as OWNER

AS

select 'I am ' + suser_name(), user_name()

exec sp_addmessage @.msgnum, @.severity, @.msgtext, @.lang

go

I create this procedure (sa__addmessage) as sa and grant execution to a user. When the user executes this function it is displayed, 'I am sa dbo' bit the execution of sp_addmessage is not permitted:

Msg 15247, Level 16, State 1, Procedure sp_addmessage, Line 18

User does not have permission to perform this action.

Has anybody an idea how to give the user permissions to execute sp_addmessage?

Cheers, Manuel