October 1, 2014 at 10:34 am
I am using SQL server 2012.
An user tried to export tables from GIS application to the SQL database.
After export, and login to the SQL server, we see all the tables has his name as the schema but not dbo.
He was added as a login and user as in a windows group. Meaning he is a member of the windows group.
I assume when export, the default schema should be dbo. but apparently not.
I went to the setting and explicitly make the default schema for the windows group user to dbo. But he tried again, it still use his username as schema prefix to the tables.
just wondering why is this?
Thanks,
October 2, 2014 at 2:35 am
you dopn't set a default schema for a windows group, look at the default schema for the user
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 2, 2014 at 8:23 am
sqlfriends (10/1/2014)
I am using SQL server 2012.An user tried to export tables from GIS application to the SQL database.
After export, and login to the SQL server, we see all the tables has his name as the schema but not dbo.
He was added as a login and user as in a windows group. Meaning he is a member of the windows group.
I assume when export, the default schema should be dbo. but apparently not.
I went to the setting and explicitly make the default schema for the windows group user to dbo. But he tried again, it still use his username as schema prefix to the tables.
just wondering why is this?
Thanks,
It sounnds like the user has a separate login other than just being a member of the group. You either need to drop the login or fix the default schema of his separate login.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2014 at 8:49 am
thats what i said, check the user 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 2, 2014 at 10:24 am
You are right.
The single user login co-exists with the windows group login.
It looks that makes the difference.
They co-exist is because sometimes that specific user does need some more priviliges than those in the windows group.
I assume the default schema for both the user and the windows group should be dbo.But apparently not.
If they co-exist, is it a required step we have to go to the single user account and set it to dbo?
I early tried to set windows group default schema following this for we are using SQL 2012, it does not work.
http://sirsql.net/blog/2012/3/12/sql2012-its-the-small-thingsdefault-schemas-for-groups.html
October 2, 2014 at 10:42 am
Ok seems its changed in 2012 on, if there's a database user and a default schema is set then that is used. If not set then it's from the group, see here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 2, 2014 at 11:01 am
Thanks, I tried to reproduce this, I am in the windows dba group, I add the group to the database. No my specific single user account. and I did not set the default schema, just leave it blank as default. And I import from an excel sheet to the database without adding any prefix to the table.
It created the table with dbo schema.
It is strange not sure why the GIS person when he exported from GIS application to the db, he got his single user account as the schema for the tables.
At this time I will just assume it is something that from the GIS application export makes the difference.
Because never has this problem before.
October 2, 2014 at 12:28 pm
He may be a member of another group
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply