March 9, 2016 at 12:16 pm
Hi Everyone,
I have problem in accessing a table in tempdb and would appreciate if someone assist me on that.
Our database is on SQL SERVER 2014. We have a table(table1) that has been created by user1 and with the schema of user1. So the full name of table is tempdb.user1.table1. Now I want to access to this table by User2. I have created a role(role1) in tempdb & model databases and give select permission to User1 schema. If I connect to SSMS by using User2 , I am able to select from tempdb.user1.table1. But if I try to call a sp in other database in which it has reference to tempdb.user1.table1, I face with below error message.
The SELECT permission was denied on the object 'table1', database 'tempdb', schema 'user1'.
March 9, 2016 at 12:23 pm
It's probably not a good idea to create a permanent table in tempdb. This table will not be there the next time the server re-boots.
If you are creating a temporary table in tempdb, and it needs to be accessed by another connection, you would need to create the table with a double# in the create table statement.
##TableName
And, this is likely not the best approach either.
What are you trying to accomplish??
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 9, 2016 at 12:26 pm
I agree with, but I can not change that piece of code.
I also tried and made the User2 as a sysadmin and still I received that permission denied error message. But if I try to access that table through SSMS there is no issue.
March 9, 2016 at 12:42 pm
Is this failing through SSMS or an application?
It sounds as if the credentials being used to run the the application have been denied access to this schema.
Try this
GRANT EXECUTE ON SCHEMA::User1 TO public
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 9, 2016 at 3:05 pm
Here is another thing that I have to add access to that table is happening through stored procedure called
SP1. This sp is being called by User1, but in sp definition I have the below
alter procedure SP1
with execute as User2
...
so User1 is calling SP1, but in reality User2 is running the SP1. when I removed the 'with execute as User2' and tried again by User2, it is working. Is there anything special that I should do with 'with execute as User2'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply