February 28, 2009 at 1:48 am
I have created a SQL Srever login with privileges on few tables and views in a user database. Access on other objects in that database is denied to this user. To make the it able to create temporary tables, i also granted tempdb access to that user with DDLAdmin role. I noticed many times that after some days (user is rarely used) the DDLAdmin role is removed and user has no access to tempDB. What may be reason for it as i m confirm that no one removes the role manually.
Regards
DBDigger Microsoft Data Platform Consultancy.
February 28, 2009 at 6:00 am
- tempdb is re-created every time sqlserver gets started !
- there is no need to grant on tempdb !
everyone is allowed to create #-tables !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 28, 2009 at 6:26 am
AShehzad (2/28/2009)
But without adding the user in DDLAdmin role, it was not able to create temp table.
How is he trying to create them? A temp table's name starts with a #, that's what makes it a temp table rather than a permanent table. No DDLAdmin rights are required in tempDB to create Temp tables.
-- Will be deleted automatically when the connection that created it closes
CREATE TABLE #Temp (
...
)
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 28, 2009 at 6:33 am
What is the exact code that is running to create the temp table and what is the exact error that the person gets?
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
March 2, 2009 at 3:12 am
I have changed the flow little bit and modified the privileges. User is able to create temp table without any problem. Now, I have to get columns of temp table from information_schema.columns as
select * FROM tempDB.information_schema.columns
WHERE table_name='#temp'
With any other user i am able to get results for created temp table but with this user i get no results. Where does problem lie in this case that i can not get meta data through this user?
DBDigger Microsoft Data Platform Consultancy.
March 2, 2009 at 3:18 am
Keep in mind tempdb is on of SQLserver's working horses !
If you ensist on quering the catalog for temp objects,
keep in mind SQLserver names them as it pleases.
If there is more than one concurrent "#temp" object, sqlserver will add stuff to the object name to make it unique.
So you should at least use a like clause
where table_name like '#temp%'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 2, 2009 at 3:41 am
ALZDBA (3/2/2009)
If there is more than one concurrent "#temp" object, sqlserver will add stuff to the object name to make it unique.
SQL will always add a prefix, whether it's the first temp table with that name of the 100th.
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
March 2, 2009 at 4:09 am
GilaMonster (3/2/2009)
ALZDBA (3/2/2009)
If there is more than one concurrent "#temp" object, sqlserver will add stuff to the object name to make it unique.SQL will always add a prefix, whether it's the first temp table with that name of the 100th.
Prefix ?
I see names like this:
#Fournisseur_Client_BOC_____________________________________________________________________________________________00000000A50A
Seems more like a suffix.
Anyway ..... it will modify (uniquify) the actual name.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 2, 2009 at 4:21 am
ALZDBA (3/2/2009)
GilaMonster (3/2/2009)
ALZDBA (3/2/2009)
If there is more than one concurrent "#temp" object, sqlserver will add stuff to the object name to make it unique.SQL will always add a prefix, whether it's the first temp table with that name of the 100th.
Prefix ?
Sorry, meant suffix.
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
March 2, 2009 at 4:31 am
AShehzad (3/2/2009)
Ok, that was a valid reason. Name is changed for this table with addition of some suffixes in tempDB. Now please let me know if there are any privileges required to query the system meta data?
hmm, query system meta data on Tempdb .... volatile by nature ... re-created at sqlserver statup time ??
I would think ..... only sysadmin will get to see all objects, the rest will only get to see their own.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 2, 2009 at 5:01 am
AShehzad (3/2/2009)
Now, I have to get columns of temp table from information_schema.columns
Why? The only tables that should be in tempDB are temp tables that your session has created. Hence you should know what their structure is.
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