May 12, 2021 at 10:10 am
Hello,
In SQL server, when running select into statement for creating an object, the object is being creating with schema with my sql login ([Domain_Name]\[Sql_Login]) instead of dbo schema. When I use select * into dbo.table_name, the object is being creating with dbo schema, but it is a bit annoying to have to use dbo in every statement for creating objects.
Do I have to grant any special permissions for the sql login, or does it have to do with something else?
Thank you.
May 12, 2021 at 11:58 am
You will need to set your default schema to be dbo on the user mapping screen, your DBA should be able to do this for you.
May 12, 2021 at 12:25 pm
Thank you very much, I have already solved the issue.
May 12, 2021 at 3:14 pm
You can also create a table as:
create table dbo.mytable(myid int)
May 12, 2021 at 6:53 pm
You really should be schema qualifying all objects - it guarantees that the table/object being used is the correct object. For example, if you had a table called Person in the dbo schema and the xyz schema - and user A has the xyz schema as the default, then any query they run will select from the xyz schema and not the dbo schema.
Further - if you don't specify the schema then a different plan will be generated for every user with a different default schema. UserA has schema UserA - UserB has schema UserB - the same code will generate a plan for each schema.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply