June 21, 2012 at 8:25 am
Hi all,
Trying to understand ownership here and if theres a way around an issue we are having.
If a user has db_owner rights they create a table but it has there login name before it. If they create with SysAdmin rights it creates the table with the correct dbo. The only link i can find from MS is the below. But its talking about SQL 2000.
Is there still no way around this issue? except to give them sysadmin... It means handing over the keys to a group of very scary kids if you know what i mean and id like to advoid it at all costs...
http://msdn.microsoft.com/en-us/library/aa905163(v=sql.80).aspx
Thanks
June 21, 2012 at 8:35 am
Whats the default schema of the user?
If its the username that would be why as it creates everything under that context unless you tell it otherwise. A simple change to DBO will resolve the issue.
June 21, 2012 at 8:42 am
Hey,
Currently blank (GUI -> Security -> Users -> User -> right click properties -> User Mapping -> default Schema)
Im trying to replicate it on my box using execute as or sqlcmd and db_owner user just creates a table under dbo.
thanks
June 21, 2012 at 8:45 am
So the user object is a group and not an individual user? By any chance do they have a secondary login which is just their user account.
June 21, 2012 at 8:56 am
hey, yes we use groups.
for some reason they are in a 2. (1 with sysadmin, 1 with db_owner) someone put them into sysadmin which stopped it happening
no individual logins are used.
both have blank default schemas.
I just tried to test creating a new sql user and gave them db_owner rights. created a table without default schema and it went to dbo.mytable and with dbo as the defauly schema, again created a dbo.table2.
Do groups change how things are done i take it?
June 21, 2012 at 9:10 am
You cant specify a default schema on a group (2012 fixes that) so it should default to dbo unless something is changing that somewhere.
If you query sys.database_principles does it show a default schema for that group? Unsure if you could hack at that level of table or issue a set default schema on the login via script instead of the GUI.
June 21, 2012 at 9:15 am
ohhh...
so
SELECT * FROM sys.database_principals
There is the users accounts in there for some reason... And its got the default schema name of there own name..
Apart from those users.. only a few accounts have a default schema which are all dbo
update..
It looks like the create_date was when they tried to create a table which went to there scheme.
so when they tried to create a table it created a a user account with a default schema of themselves..
June 22, 2012 at 8:33 am
Ok so playing around it looks like ..
If you use a AD Groups for users. When they create a table with any rights less than SysAdmin (ill say what ive tried so far below) It always creates the table in a schema of the user.
so user domain/User3 creates a table called myTable it would create [domain/user3].myTable
Ive so far tried:
Database Rights = T-SQL =
Tables name
db_owner = create table mytable1 (id int) =
[domain/user3].mytable1
db_owner = create table dbo.table1 (id int) =
dbo.mytable1
db_datareader + GRANT CREATE TABLE = create table mytable1 (id int) = [domain/user3].mytable1
db_datareader + GRANT CREATE TABLE + GRANT ALTER SCHEMA ::dbo =
= create table mytable1 (id int) = [domain/user3].mytable1
db_datareader + GRANT CREATE TABLE + GRANT ALTER SCHEMA ::dbo =
= create table dbo.mytable1 (id int) = dbo.mytable1
So as far as i can tell apart from a quick upgrade to 2012. or getting the 3rd party to use proper schema.table syntax its impossible to resolve the problem...
Could anyone confirm for me..
June 22, 2012 at 9:27 am
You could use a DDL CREATE_TABLE trigger to:
1) rollback the CREATE TABLE if the schema is not dbo (this should greatly help in forcing others to explicitly change their code to dbo.table_name :-))
OR
2) w/i the trigger, gen the code to ALTER AUTHORIZATION on the table to dbo, then construct a mechanism to run that code after the CREATE TABLE commits
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply