February 2, 2018 at 10:19 am
Hi,
SQL user account lost its stored procedure execution access .
how to find what caused it to lose the access and when .
There is no information in sqlserver errorlog .
Thank you.
February 2, 2018 at 12:29 pm
Was the user account for some reason dropped and recreated? Or someone or some process changed the account's access either way that's not an error.
February 2, 2018 at 12:33 pm
The only way you're going to track down that information is if you already had a SQL Audit or a security-related Trace running at the time the access was lost.
February 2, 2018 at 12:39 pm
If you've got the default trace running and it hasn't rolled off yet, it might give you what you're looking for:
SELECT i.*
FROM sys.traces T
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I
Join sys.trace_events E On I.eventclass = E.trace_event_id
February 2, 2018 at 3:45 pm
Bert-701015 - Friday, February 2, 2018 12:39 PMIf you've got the default trace running and it hasn't rolled off yet, it might give you what you're looking for:SELECT i.*
FROM sys.traces T
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I
Join sys.trace_events E On I.eventclass = E.trace_event_id
Yes . Default trace is running and hasn't rolled off yet.
but there is no info .
February 2, 2018 at 3:59 pm
sp is running well from under any of my team members Windows SQL logins.
while executing the sp some temp tables are created on the fly as needed.
But the sp execution is failed with below error when running with this sqlserver user account ,Apparently something is preventing them from being created, and I’m guessing it is due to removal of some access rights from this User.
Msg 208, Level 16, State 1, Procedure sp_XXX, Line 72 [Batch Start Line 2]
Invalid object name 'XXX..wrkTempTableFor_sp_XXXXX'.
Msg 208, Level 16, State 1, Procedure sp_XXX, Line 78 [Batch Start Line 2]
Invalid object name 'XXX..wrkTempTableFor_sp_XXXXX'.
February 2, 2018 at 4:26 pm
adisql - Friday, February 2, 2018 3:59 PMsp is running well from under any of my team members Windows SQL logins.
while executing the sp some temp tables are created on the fly as needed.
But the sp execution is failed with below error when running with this sqlserver user account ,Apparently something is preventing them from being created, and I’m guessing it is due to removal of some access rights from this User.Msg 208, Level 16, State 1, Procedure sp_XXX, Line 72 [Batch Start Line 2]
Invalid object name 'XXX..wrkTempTableFor_sp_XXXXX'.
Msg 208, Level 16, State 1, Procedure sp_XXX, Line 78 [Batch Start Line 2]
Invalid object name 'XXX..wrkTempTableFor_sp_XXXXX'.
That wouldn't be permissions on the stored procedure but the permission to objects referenced in the stored procedure.
It looks like it's referencing some temp table in another database - is that a tempdb temp table (doesn't look like it) or a table generated in the XXX database?
If XXX is some other database, have the permissions been checked for that database?
If the user could try to do something similar manually in SSMS you might be able to get more information - such as if XXX is another database can the user create a stored procedure from where ever the original stored procedure is running in XXX?
Sue
February 3, 2018 at 8:31 am
Sue_H - Friday, February 2, 2018 4:26 PMadisql - Friday, February 2, 2018 3:59 PMsp is running well from under any of my team members Windows SQL logins.
while executing the sp some temp tables are created on the fly as needed.
But the sp execution is failed with below error when running with this sqlserver user account ,Apparently something is preventing them from being created, and I’m guessing it is due to removal of some access rights from this User.Msg 208, Level 16, State 1, Procedure sp_XXX, Line 72 [Batch Start Line 2]
Invalid object name 'XXX..wrkTempTableFor_sp_XXXXX'.
Msg 208, Level 16, State 1, Procedure sp_XXX, Line 78 [Batch Start Line 2]
Invalid object name 'XXX..wrkTempTableFor_sp_XXXXX'.That wouldn't be permissions on the stored procedure but the permission to objects referenced in the stored procedure.
It looks like it's referencing some temp table in another database - is that a tempdb temp table (doesn't look like it) or a table generated in the XXX database?
If XXX is some other database, have the permissions been checked for that database?
If the user could try to do something similar manually in SSMS you might be able to get more information - such as if XXX is another database can the user create a stored procedure from where ever the original stored procedure is running in XXX?Sue
XXX is same database.
February 3, 2018 at 8:38 am
adisql - Saturday, February 3, 2018 8:31 AMSue_H - Friday, February 2, 2018 4:26 PMadisql - Friday, February 2, 2018 3:59 PMsp is running well from under any of my team members Windows SQL logins.
while executing the sp some temp tables are created on the fly as needed.
But the sp execution is failed with below error when running with this sqlserver user account ,Apparently something is preventing them from being created, and I’m guessing it is due to removal of some access rights from this User.Msg 208, Level 16, State 1, Procedure sp_XXX, Line 72 [Batch Start Line 2]
Invalid object name 'XXX..wrkTempTableFor_sp_XXXXX'.
Msg 208, Level 16, State 1, Procedure sp_XXX, Line 78 [Batch Start Line 2]
Invalid object name 'XXX..wrkTempTableFor_sp_XXXXX'.That wouldn't be permissions on the stored procedure but the permission to objects referenced in the stored procedure.
It looks like it's referencing some temp table in another database - is that a tempdb temp table (doesn't look like it) or a table generated in the XXX database?
If XXX is some other database, have the permissions been checked for that database?
If the user could try to do something similar manually in SSMS you might be able to get more information - such as if XXX is another database can the user create a stored procedure from where ever the original stored procedure is running in XXX?Sue
XXX is same database.
it's referencing some temp table in same database.Everything in same database(XXX) .
February 3, 2018 at 9:55 am
If everything is in the same database - then why are you using 3-part naming? There is no need - and by referencing XXX..object you are missing the key part - which is the actual schema of the object.
Referencing the schema is much more important - and without it can cause unusual issues. For example, if the object exists in a schema that the user does not have access - it is possible to see this error for that user but not other users.
And - if you need a temp table then use an actual temp table and not a 'permanent' temp/worktable. Temp tables are scoped to the user session so multiple users can have the same temp table built but they are not sharing the data. Using a permanent work table like this requires more management to insure that each user has the appropriate level of access and is only accessing the data specific to their instance of the process. Now, if this is actually generated outside of this process then it really isn't a work/temp table so it shouldn't be named as one....
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 3, 2018 at 10:20 am
Jeffrey Williams 3188 - Saturday, February 3, 2018 9:55 AMIf everything is in the same database - then why are you using 3-part naming? There is no need - and by referencing XXX..object you are missing the key part - which is the actual schema of the object.Referencing the schema is much more important - and without it can cause unusual issues. For example, if the object exists in a schema that the user does not have access - it is possible to see this error for that user but not other users.
And - if you need a temp table then use an actual temp table and not a 'permanent' temp/worktable. Temp tables are scoped to the user session so multiple users can have the same temp table built but they are not sharing the data. Using a permanent work table like this requires more management to insure that each user has the appropriate level of access and is only accessing the data specific to their instance of the process. Now, if this is actually generated outside of this process then it really isn't a work/temp table so it shouldn't be named as one....
here is the actual error.
February 5, 2018 at 12:11 am
Either that object does not exist in the user's default schema or dbo, or the user does not have permission on that table. Please check which it is.
As Jeffery said, real temp tables are often a better idea than a 'temp' table in the user DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2018 at 2:48 pm
GilaMonster - Monday, February 5, 2018 12:11 AMEither that object does not exist in the user's default schema or dbo, or the user does not have permission on that table. Please check which it is.As Jeffery said, real temp tables are often a better idea than a 'temp' table in the user DB.
This is resolved by granting the viewserverstate permission to the user.
Some time back we have enabled DDL trigger to track the DDL changes in this database using below . is this creating any issue ?
February 6, 2018 at 1:00 am
adisql - Monday, February 5, 2018 2:48 PMGilaMonster - Monday, February 5, 2018 12:11 AMEither that object does not exist in the user's default schema or dbo, or the user does not have permission on that table. Please check which it is.As Jeffery said, real temp tables are often a better idea than a 'temp' table in the user DB.
This is resolved by granting the viewserverstate permission to the user.
View Server State won't allow someone to access a table that they couldn't before. It gives permission to see DMV contents and stuff like that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply