Sysadmin can't create tables
-
I've got a new SQL server test instance (SQL Server 2019 Enterprise). When trying to run a create table script against tempdb as a sysadmin I get an error. I'm able to run this same script on another server that I manage without error. I've also tried as a second sysadmin user with no different result. The reason we need a permanent table created in tempdb is for commercial monitoring software.
Script: CREATE TABLE [tempdb]..[newtablename](...columns...)
Error: Msg 15151, Level 16, State 1, Line 4
Cannot find the object 'newtablename', because it does not exist or you do not have permission.
The statement has been terminated.
Anyone with an idea of what might cause a sysadmin to be denied table create on tempdb?
Edit: So, a bit more information. The table name actually contains a sort of GUID at then end to ensure uniqueness. I thought maybe length was an issue, but I'm well under the SQL maximum table name length. Missing some attention to detail at first, I finally noticed the error message contained a truncated version of the table name:
Table Name: newtablename_ui84kQYrCAbhK6kvLrUyG0WXEfA
Error table name: newtablename_ui84kQYrCAbhK6kvLr
I am able to manually create the table name in the error message. Once I do so, I am able to create the table name in my script (and the software is able to create the table as it expects to.
Any ideas why SQL would need the latter (truncated) table to exist in order to create the former (full table name)?
Edit 2: Upon further research it appears to allow table creation so long as the name has 50 or fewer characters, well under the stated maximum for SQL Server. I attempted to create the same tables in the model DB and had the same error prior to reducing the names down to 50 chars.
Anyone know why SQL might limiting table creation to 50 chars for system DBs?
-
it is a bad practice, but that doesn't mean it cannot be done.
Did you try "create table tempdb.dbo.tablename ..."
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
-
Thanks for your reply. Yes, I understand it's not a good practice. The 3rd party product is attempting to create this table running under a service account and in troubleshooting that, I realized that I could not create the table as myself either, pointing to different/bigger issue with SQL. I tried it with specifying the schema explicitly (dbo) as you suggested and the result is the same.
-
did you upgrade your SQLServer to its latest cumulative update? ( please do ! )
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
-
Hi, I updated the instance from CU17 to CU18 with no change in behavior. Thanks for the suggestion.
-
So, a bit more information. The table name actually contains a sort of GUID at then end to ensure uniqueness. I thought maybe length was an issue, but I'm well under the SQL maximum table name length. Missing some attention to detail at first, I finally noticed the error message contained a truncated version of the table name:
Table Name: newtablename_ui84kQYrCAbhK6kvLrUyG0WXEfA
Error table name: newtablename_ui84kQYrCAbhK6kvLr
I am able to manually create the table name in the error message. Once I do so, I am able to create the table name in my script (and the software is able to create the table as it expects to. Any ideas why SQL would need the latter (truncated) table to exist in order to create the former (full table name)?
-
Not a clue on your last question but I'd be loath to let anything create a permanent table in TempDB. What do you suppose might happen on your next server bounce?
This sounds like someone saying that an application that faces the public must have "sysadmin" privs... not on my watch.
And, it's bad enough that the people that make monitoring software don't know that there's a WITH TABLE RESULTS setting for most DBCC commands and so the SQL Server Log is littered with them enabling a trace flag and disabling it.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Agreed. The application appears to recreate those objects in the event of their non-existence. Therefore, since I apparently have to manually recreate the truncated versions of the tables in order for the "real" tables to be created, it's even worse. I am certain the process will again fail once the instance restarts next and I have not created the tables... I'd just like to get to the bottom of what's wrong so I can ensure this instance doesn't have any major configuration/corruption issues before it is really in service.
In the end, the process service account wouldn't have sysadmin, however to troubleshoot sometimes we end up in dark places. Fortunately, this is a test environment and not public facing.
Thanks for the advice and expertise.
- DorDBA wrote:
...
Table Name: newtablename_ui84kQYrCAbhK6kvLrUyG0WXEfA
Error table name: newtablename_ui84kQYrCAbhK6kvLr
..
This indicates a bug in the marvelous software.
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
-
Perhaps it is a bug, I'm still not sure. Just added some additional information to original post, but appears to limit to 50 chars in the table name, also not just tempdb, also model. Doesn't appear to be an issue in user DBs.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply