October 3, 2011 at 10:00 am
I have an application that uses a common stored procedure to report application errors. The common stored proc uses xp_LogEvent to log the error to the SQL Server log file but is hasn't ever worked because of the permission (I'm sorting out the few outstanding snags). The documentation on xp_LogEvent says 'Requires membership in the db_owner fixed database role in the master database, or membership in the sysadmin fixed server role'
I've simplified my logging stored procedure to this for this post
CREATE Procedure up_Error (
@vchMessagevarchar(500)
) AS
DECLARE @vchSeverity varchar(15)
DECLARE@intErrorNumber int
SELECT @intErrorNumber = 50000 + 1
,@vchSeverity = 'INFORMATIONAL'
EXECUTE master..xp_LogEvent @intErrorNumber,@vchMessage,@vchSeverity
RETURN 0
I have 2 questions
1. How do I set up the permissions so that any user calling up_Error will have permissions to run xp_LogEvent? I'm looking for best practice rather than make everyone db_owner of Master! I'm thinking create a new user that can execute xp_LogEvent, but can't figure how to join the dots and get the call to up_Error using that login.
2. Is this the best way of logging application errors? How do others manage this?
The logging is required so that any application errors in live are reported.
October 3, 2011 at 2:06 pm
RichardBo (10/3/2011)
I have an application that uses a common stored procedure to report application errors. The common stored proc uses xp_LogEvent to log the error to the SQL Server log file but is hasn't ever worked because of the permission (I'm sorting out the few outstanding snags). The documentation on xp_LogEvent says 'Requires membership in the db_owner fixed database role in the master database, or membership in the sysadmin fixed server role'I've simplified my logging stored procedure to this for this post
CREATE Procedure up_Error (@vchMessagevarchar(500)
) AS
DECLARE @vchSeverity varchar(15)
DECLARE@intErrorNumber int
SELECT @intErrorNumber = 50000 + 1
,@vchSeverity = 'INFORMATIONAL'
EXECUTE master..xp_LogEvent @intErrorNumber,@vchMessage,@vchSeverity
RETURN 0
I have 2 questions
1. How do I set up the permissions so that any user calling up_Error will have permissions to run xp_LogEvent? I'm looking for best practice rather than make everyone db_owner of Master! I'm thinking create a new user that can execute xp_LogEvent, but can't figure how to join the dots and get the call to up_Error using that login.
For this you would want to use a signed procedure using a certificate. This article[/url] by Jonathan Kehayias should help get you started with this.
2. Is this the best way of logging application errors? How do others manage this?
The logging is required so that any application errors in live are reported.
I normally log to a database table. I don't like to rely on anything outside the database. Another option now, is to stick the errors in a Service Broker Queue and then you could process it asynchronously however you want.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 5, 2011 at 4:12 am
Thank you Jack.
I have got the up_Error procedure working, but have decided that logging errors to a table fits what I actual want to happen.
To get up_Error working I created a login LoggingUser, mapped it to the user LoggingUser in the master database and gave it the db_owner database role. Logging into master using that account I created the stored proc in master with EXECUTE AS OWNER. Then dropping the up_error sp in my database means the copy in master is called when you do an EXEC up_error. So that works, original problem solved!
I then got to thinking I could have my local sp call master..up_error sp, but permissions stopped that from running. It was then that I decided that logging errors to a table is what I actually want (couldn't figure out why the permissions wouldn't let me).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply