Solved

Cannot find the object "tst.TestMatrix" because it does not exist or you do not have permissions.

Posted on 2013-11-21
18
2,165 Views
Last Modified: 2013-11-22
Hi, I got a strange error with an Sql Server stored procedure, it worked ok for almost 1 year, and now, without any change:

The SP is compiled successfully, execution permissions ok, but at run time the following error is displayed:
"Cannot find the object "tst.TestMatrix" because it does not exist or you do not have permissions."

I have checked, the table exists, the only difference is that its schema bound dependency with this SP, compared to the other tables this SP refers to, but don't know if this is the error source.

Below the SP code:

USE [mdlzprd]
GO

/****** Object:  StoredProcedure [tst].[prcSetTestMatrix]    Script Date: 11/21/2013 10:40:43 PM ******/
DROP PROCEDURE [tst].[prcSetTestMatrix]
GO

/****** Object:  StoredProcedure [tst].[prcSetTestMatrix]    Script Date: 11/21/2013 10:40:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [tst].[prcSetTestMatrix]
	-- Add the parameters for the stored procedure here
	@TestId int,
	@TestMatrix TestMatrixType READONLY,
	@RowsInserted int OUT
AS
BEGIN 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
	DELETE FROM tst.TestMatrix
	WHERE TestId=@TestId

	DECLARE @Groups TABLE
	(
		GroupId int,
		Group1 varchar(255),
		Group2 varchar(255),
		Group3 varchar(255)
	)

	INSERT INTO @Groups
	SELECT
		g.GroupId,
		g1.GroupDesc,
		g2.GroupDesc,
		g3.GroupDesc
	FROM 
		tst.TestQuestionGroups g
		INNER JOIN tst.TestQuestionGroup1 g1 ON g.GroupId1=g1.GroupId
		INNER JOIN tst.TestQuestionGroup2 g2 ON g.GroupId2=g2.GroupId
		INNER JOIN tst.TestQuestionGroup3 g3 ON g.GroupId3=g3.GroupId
	WHERE
		g.TestId=@TestId
		AND g1.TestId=@TestId
		AND g2.TestId=@TestId
		AND g3.TestId=@TestId

	DECLARE @InsertTable TABLE
	(
		TestId int,
		PositionId int,
		GroupId int,
		Level tinyint
	)
	
	--INSERT INTO [tst].[TestMatrix]
	INSERT INTO @InsertTable
	(
		TestId,
		PositionId,
		GroupId,
		Level
	)
	SELECT
		@TestId,
		p.PositionId,
		g.GroupId,
		t.Level
	FROM 
		@TestMatrix t
		INNER JOIN usr.Positions p ON LOWER(RTRIM(t.PositionName))=LOWER(RTRIM(p.PositionName))
		INNER JOIN @Groups g ON 
			(
			LOWER(RTRIM(t.Group1))=LOWER(RTRIM(g.Group1))
				AND LOWER(RTRIM(t.Group2))=LOWER(RTRIM(g.Group2))
				AND LOWER(RTRIM(t.Group3))=LOWER(RTRIM(g.Group3))
			)

	SELECT @RowsInserted=COUNT(*)
	FROM @InsertTable

	INSERT INTO tst.TestMatrix
	(
		TestId,
		PositionId,
		GroupId,
		Level
	)
	SELECT
		TestId,
		PositionId,
		GroupId,
		Level
	FROM 
		@InsertTable

	ALTER INDEX [idx_PositionId] ON tst.TestMatrix REBUILD
	ALTER INDEX [idx_TestId] ON tst.TestMatrix REBUILD

END

GRANT EXECUTE ON [tst].[prcSetTestMatrix] TO mdlzgenuser
GO

Open in new window


Thanks!
0
Comment
Question by:danielivanov2
  • 9
  • 8
18 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Seems someone changed the permissions, make sure that the user with which you run the sp has enough permissions; could be someone accidently run the 'DENY DELETE' for that table
0
 

Author Comment

by:danielivanov2
Comment Utility
I have attached the current permissions from testing environment, which is identical with production environment (except for data). The picture is the same for production.SP properties
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
how abt the permission on tst.TestMatrix
0
 

Author Comment

by:danielivanov2
Comment Utility
there are no explicit permissions, as for the other tables:
TestMatrix
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
>there are no explicit permissions, as for the other tables:
 there shouldn't be;
can you specify explicit DELETE for the sql user on this table, just to test;
0
 

Author Comment

by:danielivanov2
Comment Utility
delete  works, same error when executing SP
USE mdlzprd
GO

GRANT DELETE ON tst.TestMatrix TO mdlzgenuser
GO


use mdlzprd
go

delete from tst.TestMatrix
where testid=1000
go

(0 row(s) affected)
0
 
LVL 1

Expert Comment

by:BradySQL
Comment Utility
Dumb question, but have you run a DBCC CHECKDB to make sure there is no corruption.

Also, what do the schema permissions look like?
0
 

Author Comment

by:danielivanov2
Comment Utility
CHECKDB found 0 allocation errors and 0 consistency errors in database 'mdlzprd'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

No explicit permissions defined for schema tst
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
try this way

use mdlzprd
go

EXECUTE AS USER = 'mdlzgenuser';
delete from tst.TestMatrix
where testid=1000
Revert;

also try to execute the sp in the same way using EXECUTE AS an revert and let us know which line you are getting the error
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
if this is run as part of the sql server agent , make sure that the source database is mdlzprd , otherwise use a 'use mdlzprd'  before executing the sp
0
 

Author Comment

by:danielivanov2
Comment Utility
the stored procedure is called from C# code, not executed as such in SSMS

I need an explanation of why this table is not recognized as a database object, despite that it exists in DB and there is no obvious security issue
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Either this is a permission issue or the application is looking at a different database.
0
 

Author Comment

by:danielivanov2
Comment Utility
The application cannot look at different database, since the method called in C# is running 4 different stored procedures (3 of them are running ok), and the only connection string is that from web.config.

What is also strange is that if executing the stored procedure under mdlzgenuser, it runs ok under admin user, but not mdlzgenuser, so this is pointing me to a permission issue for the latter. Where should I look for?

USE [mdlzprd]
GO

DECLARE	@return_value int,
		@RowsInserted int

EXEC	@return_value = [tst].[prcSetTestMatrix]
		@TestId = 1111,
		@RowsInserted = @RowsInserted OUTPUT

SELECT	@RowsInserted as N'@RowsInserted'

SELECT	'Return Value' = @return_value

GO


Msg 1088, Level 16, State 9, Procedure prcSetTestMatrix, Line 94
Cannot find the object "tst.TestMatrix" because it does not exist or you do not have permissions.

Open in new window

0
 

Author Comment

by:danielivanov2
Comment Utility
Finally solved by granting "Control" permissions to mdlzgenuser to tst.TestMatrix, but this is not an elegant solution.
And still dont understand why it happend, and for other tables and stored procedure there is no need to do this....
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
Comment Utility
I think the user doent have the permission to REINDEX. You can test it commenting out those two statements in your sp
0
 

Author Closing Comment

by:danielivanov2
Comment Utility
Tested and indeed this was the root cause. Thanks! :)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
I should have asked you the line number where you were getting that error  before : )
0
 

Author Comment

by:danielivanov2
Comment Utility
I had no specific line number. The error was displayed in browser and is the exception message detail as part of a C# try catch finally block.
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now