Only System Administrator can specify WITH

Question

Post reply

Only System Administrator can specify WITH

sqlfriend

SSC Guru

Points: 52781
More actions
February 1, 2012 at 1:16 pm

#254609

We recently migrate a database from SQL server 2005 to 2008.
There are some stored procedures in it, when developers trying to udpate the stored procedure, she got errors like below:
dropping procedure [AllocationAdjustStaffSelect]...
creating procedure [AllocationAdjustStaffSelect]...
Msg 2778, Level 16, State 1, Procedure spot_AllocationAdjustStaffSelect, Line 107
Only System Administrator can specify WITH LOG option for RAISERROR command.
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'AllocationAdjustStaffSelect', because it does not exist or you do not have permission.
I checked in the stored procedure, there are some code like this:
-- Exit Handler
ExitHandler:
IF @iError <> 0
BEGIN
RAISERROR(@sMessage,11,1) WITH LOG, NOWAIT
RETURN -1
END
RETURN 0
END
SET NOCOUNT OFF
GO
I don't want to give the developer system admin permission, but how can we fix the error?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 1

Give the User

alter trace permissions

LOG

Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.

http://msdn.microsoft.com/en-us/library/ms178592.aspx

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

A user with ALTER TRACE permission can also use the WITH LOG.

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

sqlfriend

SSC Guru

Points: 52781

February 1, 2012 at 1:53 pm

#1441428

Thanks all

johnitech.itech Hall of Fame Points: 3252 More actions · Answer 4

:w00t:

Hello i suggest you to use Using TRY...CATCH in Transact-SQL to avoid any undesired results

show below link

http://msdn.microsoft.com/en-us/library/ms179296.aspx

:hehe:

Johan Bijnens SSC Guru Points: 135380 More actions · Answer 5

Take control over your proprietary error codes and declare your own messages ( and alerts if needed )

Have a look at sp_addmessage in BOL:

exec sp_addmessage [ @msgnum = ] msg_id , [ @severity = ] severity , [ @msgtext = ] 'msg'

[ , [ @lang = ] 'language' ]

[ , [ @with_log = ] { 'TRUE' | 'FALSE' } ]

[ , [ @replace = ] 'replace' ]

and then of course use :

RAISERROR(yourmsg_id [, paramx,..])

No more the need to specify "with log" because the message has been declared "with log"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 6

johnitech.itech (2/1/2012)
:w00t:
Hello i suggest you to use Using TRY...CATCH in Transact-SQL to avoid any undesired results
show below link
http://msdn.microsoft.com/en-us/library/ms179296.aspx
:hehe:

That's not likely to help with the privs necessary to write an error message to the SQL Server Log... especially if the error being caused is actually in the TRY/CATCH when they reraise the error that caused the problem in the first place.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 7

ALZDBA (2/1/2012)
Take control over your proprietary error codes and declare your own messages ( and alerts if needed )
Have a look at sp_addmessage in BOL:
exec sp_addmessage [ @msgnum = ] msg_id , [ @severity = ] severity , [ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] { 'TRUE' | 'FALSE' } ]
[ , [ @replace = ] 'replace' ]
and then of course use :
RAISERROR(yourmsg_id [, paramx,..])
No more the need to specify "with log" because the message has been declared "with log"

Absolutely outstanding, Johan. I was looking for a way to get around all of that. You've saved me a bit of a headache. Thanks, ol' friend.