danielivanov2
asked on
Cannot find the object "tst.TestMatrix" because it does not exist or you do not have permissions.
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:
Thanks!
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
Thanks!
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
ASKER
how abt the permission on tst.TestMatrix
>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;
there shouldn't be;
can you specify explicit DELETE for the sql user on this table, just to test;
ASKER
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)
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)
Dumb question, but have you run a DBCC CHECKDB to make sure there is no corruption.
Also, what do the schema permissions look like?
Also, what do the schema permissions look like?
ASKER
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
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No explicit permissions defined for schema tst
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
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
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
ASKER
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
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
Either this is a permission issue or the application is looking at a different database.
ASKER
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?
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.
ASKER
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....
And still dont understand why it happend, and for other tables and stored procedure there is no need to do this....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tested and indeed this was the root cause. Thanks! :)
I should have asked you the line number where you were getting that error before : )
ASKER
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
Thanks