Msg15151, Level 16, State 1,Line 1

Question

Post reply

Msg15151, Level 16, State 1,Line 1

Scott1336

SSC Eights!

Points: 964
More actions
September 4, 2012 at 8:53 am

#267252

Hi,
This script is acutally running as a job and its failing because of error
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'UserLOBUpdate', because it does not exist or you do not have permission.
And I checked the Individual scripts its running fine. I can't find a solution please Help
USE [HealthPlaNET_Dev_Team1_Stg]
DECLARE @sprocCount INT
DECLARE @loop INT
DECLARE @TSQL NVARCHAR(max)
SET @sprocCount = 0
SET @loop = 0
IF EXISTS (SELECT * FROM sys.tables WHERE name like 'sprocPermissionHandleExecute%')
BEGIN
DROP TABLE sprocPermissionHandleExecute
END
CREATE TABLE sprocPermissionHandleExecute (F1 INT IDENTITY, F2 varchar(MAX))
INSERT INTO sprocPermissionHandleExecute
SELECT '[' + s.name + '].[' + p.name + ']' FROM sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)
SELECT @sprocCount = COUNT(*) from sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)
WHILE @loop <= @sprocCount
BEGIN
SELECT @TSQL = 'GRANT EXECUTE ON ' + F2 + ' TO [1UPMC-ACCT\Application Services (68731) - HealthPlaNET Application Users];' FROM sprocPermissionHandleExecute
WHERE F1 = @loop
SET @loop =@loop + 1
EXEC sp_executesql @TSQL
END
USE [HealthPlaNET_Dev_Team2_Stg]
--DECLARE @sprocCount INT
--DECLARE @loop INT
--DECLARE @TSQL NVARCHAR(max)
SET @sprocCount = 0
SET @loop = 0
IF EXISTS (SELECT * FROM sys.tables WHERE name like 'sprocPermissionHandleExecute%')
BEGIN
DROP TABLE sprocPermissionHandleExecute
END
CREATE TABLE sprocPermissionHandleExecute (F1 INT IDENTITY, F2 varchar(MAX))
INSERT INTO sprocPermissionHandleExecute
SELECT '[' + s.name + '].[' + p.name + ']' FROM sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)
SELECT @sprocCount = COUNT(*) from sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)
WHILE @loop <= @sprocCount
BEGIN
SELECT @TSQL = 'GRANT EXECUTE ON ' + F2 + ' TO [1UPMC-ACCT\Application Services (68731) - HealthPlaNET Application Users];' FROM sprocPermissionHandleExecute
WHERE F1 = @loop
SET @loop =@loop + 1
EXEC sp_executesql @TSQL
END
USE [HealthPlaNET_Dev_Team3_Stg]
--DECLARE @sprocCount INT
--DECLARE @loop INT
--DECLARE @TSQL NVARCHAR(max)
SET @sprocCount = 0
SET @loop = 0
IF EXISTS (SELECT * FROM sys.tables WHERE name like 'sprocPermissionHandleExecute%')
BEGIN
DROP TABLE sprocPermissionHandleExecute
END
CREATE TABLE sprocPermissionHandleExecute (F1 INT IDENTITY, F2 varchar(MAX))
INSERT INTO sprocPermissionHandleExecute
SELECT '[' + s.name + '].[' + p.name + ']' FROM sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)
SELECT @sprocCount = COUNT(*) from sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)
WHILE @loop <= @sprocCount
BEGIN
SELECT @TSQL = 'GRANT EXECUTE ON ' + F2 + ' TO [1UPMC-ACCT\Application Services (68731) - HealthPlaNET Application Users];' FROM sprocPermissionHandleExecute
WHERE F1 = @loop
SET @loop =@loop + 1
EXEC sp_executesql @TSQL
END
USE [MessageBroker]
--DECLARE @sprocCount INT
--DECLARE @loop INT
--DECLARE @TSQL NVARCHAR(max)
SET @sprocCount = 0
SET @loop = 0
IF EXISTS (SELECT * FROM sys.tables WHERE name like 'sprocPermissionHandleExecute%')
BEGIN
DROP TABLE sprocPermissionHandleExecute
END
CREATE TABLE sprocPermissionHandleExecute (F1 INT IDENTITY, F2 varchar(MAX))
INSERT INTO sprocPermissionHandleExecute
SELECT '[' + s.name + '].[' + p.name + ']' FROM sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)
SELECT @sprocCount = COUNT(*) from sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)
WHILE @loop <= @sprocCount
BEGIN
SELECT @TSQL = 'GRANT EXECUTE ON ' + F2 + ' TO [1UPMC-ACCT\Application Services (68731) - HealthPlaNET Application Users];'
FROM sprocPermissionHandleExecute
WHERE F1 = @loop
SET @loop =@loop + 1
EXEC sp_executesql @TSQL
END

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

not sure about the error; typically that might be caused my the wrong schema, or an object name not being quoted;

you seem to be doing both of those things;

only thing i can suggest is to remove the cursors;

no need for them at all, and you can build one whopper string and execute it, and see the results;

USE [HealthPlaNET_Dev_Team1_Stg]

DECLARE @TSQL varchar(max);

SET @TSQL = '';

SELECT @TSQL = @TSQL + 'GRANT EXECUTE ON ' + '[' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ' TO [1UPMC-ACCT\Application Services (68731) - HealthPlaNET Application Users];' + CHAR(13) + CHAR(10)

FROM sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)

PRINT @TSQL

EXEC (@TSQL)

USE [HealthPlaNET_Dev_Team2_Stg]

SET @TSQL = '';

SELECT @TSQL = @TSQL + 'GRANT EXECUTE ON ' + '[' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ' TO [1UPMC-ACCT\Application Services (68731) - HealthPlaNET Application Users];'

FROM sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)

PRINT @TSQL

EXEC (@TSQL)

USE [HealthPlaNET_Dev_Team3_Stg]

SET @TSQL = '';

SELECT @TSQL = @TSQL + 'GRANT EXECUTE ON ' + '[' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ' TO [1UPMC-ACCT\Application Services (68731) - HealthPlaNET Application Users];'

FROM sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)

PRINT @TSQL

EXEC (@TSQL)

USE [MessageBroker]

SET @TSQL = '';

SELECT @TSQL = @TSQL + 'GRANT EXECUTE ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ' TO [1UPMC-ACCT\Application Services (68731) - HealthPlaNET Application Users];'

FROM sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where p.schema_id not in (2, 3, 4)

PRINT @TSQL

EXEC (@TSQL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Scott1336 SSC Eights! Points: 964 More actions · Answer 2

Well Typically One of the Developers team wrote the code. I don't have permission to change the code. The error which I showed occurs only when I use MessageBrokerDB. Other wise the script runs fine. And the part which is added with the MessageBroker also runs Individually, but I want it to run with all the Four DB's.

That's My Problem.

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

Try printing out the sql code before you execute it. You may find the problem there. Nothing in the code provided can help as it looks like it is a data (ore in this case metadata) problem.

Lowell SSC Guru Points: 323518 More actions · Answer 4

well if you cannot change the code, kick it back to the developer and make them fix it, since it didn't work as expected, i guess.

My shop is different; no code hits the database unless I reviewed it myself, which of course involves testing on my dev server, and i'm free to fix/rewrite whatever wasn't correct;

I make sure the original writer knows the changes i made, of course, and why i changed it to make that person better int he future.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!