September 4, 2012 at 8:53 am
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
September 4, 2012 at 9:31 am
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
September 4, 2012 at 9:52 am
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.
September 4, 2012 at 9:53 am
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.
September 4, 2012 at 9:57 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply