Re: SPROC won't execute - insufficient permissions

How do I give the SPROC permission to execute?

Since DBCC OUTPUTBUFFER can only be executed by sysadmin role members, you
must be a sysadmin to use this technique to get the error message. I
believe you will find this disclaimer in Erland's article.

SQL 2005 has structured error handling that allows you can get error details
in a CATCH block without the kludge.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<teddysnips@xxxxxxxxxxx> wrote in message
news:1151658530.478948.38290@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm trying to use the SPROC below (courtesy of Erland!) to capture the
error message but it fails owing to insufficient permissions (I can't
reproduce it just now, but I think it's because it can't get access to
the DBCC OUTPUTBUFFER).

How do I give the SPROC permission to execute?

Many thanks

Edward


CREATE PROCEDURE stpShowErrorMessage @errmsg nvarchar(500) OUTPUT AS
DECLARE @dbccrow nchar(77),
@msglen int,
@lenstr nchar(2),
@sql nvarchar(2000),
@s tinyint

-- Catch the output buffer.
CREATE TABLE #DBCCOUT (col1 nchar(77) NOT NULL)
INSERT INTO #DBCCOUT
EXEC ('DBCC OUTPUTBUFFER(@@spid)')

-- Set up a cursor over the table. We skip the first
-- row, because there is nothing of interest.
DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FOR
SELECT col1
FROM #DBCCOUT
WHERE left(col1, 8) <> replicate('0', 8)
ORDER BY col1

-- Init variable, and open cursor.
SELECT @errmsg = ''
OPEN error_cursor
FETCH NEXT FROM error_cursor INTO @dbccrow

-- On this first row we find the length.
SELECT @lenstr = substring(@dbccrow, 15, 2)

-- Convert hexstring to int
SELECT @sql = 'SELECT @int = convert(int, 0x00' + @lenstr + ')'
EXEC sp_executesql @sql, N'@int int OUTPUT', @msglen OUTPUT

-- @s is where the text part of the buffer starts.
SELECT @s = 62

-- Now assemble rest of string.
WHILE @@FETCH_STATUS = 0 AND datalength(@errmsg) - 1 < 2 * @msglen
BEGIN
SELECT @errmsg = @errmsg + substring(@dbccrow, @s + 1, 1) +
substring(@dbccrow, @s + 3, 1) +
substring(@dbccrow, @s + 5, 1) +
substring(@dbccrow, @s + 7, 1) +
substring(@dbccrow, @s + 9, 1) +
substring(@dbccrow, @s + 11, 1) +
substring(@dbccrow, @s + 13, 1) +
substring(@dbccrow, @s + 15, 1)
FETCH NEXT FROM error_cursor INTO @dbccrow
END

CLOSE error_cursor
DEALLOCATE error_cursor

-- Now chop first character which is the length, and cut after end.
SELECT @errmsg = substring(@errmsg, 2, @msglen)
GO



.

Relevant Pages

  • Re: Memory and performance issues
    ... The DBA was getting an error message from the maintenance plan ... and so disabled the dbcc step reporting the error rather than fixing the ... Just wanted to find out depth of experience with changing memory ... Memory settings were changed ...
    (microsoft.public.sqlserver.setup)
  • error message
    ... I get this message again when I run this script: ... BACKUP LOG wslogdb62 WITH TRUNCATE_ONLY ... This is the error message ... DBCC execution completed. ...
    (microsoft.public.sqlserver.mseq)
  • Re: Removing and event from the html code.
    ... Like I said earlier I inherited this script with the page. ... Actually I don't get an error message, ... > This assigns the *result* of running countdown to window.onload. ... window.onload won't execute until the page has fully loaded. ...
    (comp.lang.javascript)
  • Re: LaTeX2rtf not running
    ... Cannot execute C:\PROGRA~1\LATEX2RTF\LATEX2RT.EXE ... Please cite exactly the complete error message ... probably related to window, not to latex2rtf. ...
    (comp.text.tex)
  • Re: Windows shutting down when booted up
    ... Enquire, plan and execute ... Faulting application services.exe, version 5.1.2600.2180, faulting ... Apart from that and the Error message on bootup, ... The following files will be included in this error report ...
    (microsoft.public.windowsxp.general)