472,958 Members | 2,184 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 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 1582
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: gdp | last post by:
hi...this is no doubt easy but I cant do it below is a sproc i am calling from an ASP page ------------------------------ CREATE PROCEDURE dbo.populate_MAIN_visitors @IP nvarchar(20),...
0
by: Stephen Witter | last post by:
I am getting the error: Parameter 4: '@IDENTITY' of type: String, the property Size has an invalid size: 0 When trying to execute a sproc with an varchar output. Here is the asp.net code: ...
12
by: scott | last post by:
In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm...
3
by: Radu | last post by:
Hi. I have lots of processing to do on the server - from the client (Access) I call a sproc which returns a recordset (the sproc is essentially a big "select"). With the obtained data , I need to...
2
by: Chris Fink | last post by:
I am using the System.IO.File class to determine if a file exists on a network share. The File.Exists method keeps returning false, even though the file does exist. The MSDN documentation...
7
by: Mike L. | last post by:
Hi, I got this 'EXECUTE permission denied on object <mySproc>' error message everytime I try executing my SQL server Sproc. What's this and how to fix this err? many thnaks in advance, mike
6
by: teddysnips | last post by:
In a system I'm maintaining there is a Stored Procedure called dbo.MyStoredProcedure. I didn't create this - it was created by a developer who has now left. I don't know how the object came by...
6
by: =?Utf-8?B?RGF2ZQ==?= | last post by:
I'm running VS 2003/ASP.NET 1.1.4322.0 on XP SP2 All of a sudden I started to getting... "Error while trying to run project: Unable to start debugging on the web server. Server side-error...
2
by: teddysnips | last post by:
My client has recently upsized to a SQL Server back end. All is fine except there is an UPDATE query that is very slow. I've tested it in SQL Server and it runs fine, so I'd like to execute a SQL...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...

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.