471,073 Members | 1,466 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

SPROC won't execute - insufficient permissions

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

Jun 30 '06 #1
3 1514
> 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

<te********@hotmail.com> wrote in message
news:11*********************@x69g2000cwx.googlegro ups.com...
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

Jun 30 '06 #2

Dan Guzman wrote:
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.
The SPROC executes in a Job, which is owned by SA, which is a member of
the sysadmin role. Where am I going wrong?!?

Unfortunately, SQL2005 is not an option.

Edward

Jul 3 '06 #3
(te********@hotmail.com) writes:
The SPROC executes in a Job, which is owned by SA, which is a member of
the sysadmin role. Where am I going wrong?!?
Probably in the assumption in the ownership of the job.

Could help if you posted the complete error message.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by scott | last post: by
7 posts views Thread by Mike L. | last post: by
6 posts views Thread by =?Utf-8?B?RGF2ZQ==?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.