Default schema

Question

Default schema

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic. Login to reply

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 1

mkeustermans (4/1/2014)
Since king_login has default schema of king_schema and has been assigend the db_owner role and no other, the table will be created as king_schema.wonders.
Points back please.

I think the idea is that the server user has already been created and may be a member of sysadmins.

Toreador SSChampion Points: 11365 More actions · Answer 2

mkeustermans (4/1/2014)
Since king_login has default schema of king_schema and has been assigend the db_owner role and no other, the table will be created as king_schema

Like many, you are getting confused between king_login and king_user

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 3

batgirl

SSCarpal Tunnel

Points: 4979

April 1, 2014 at 5:44 am

#1701978

//headdesk

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

I think this part of the explanation is enough to end this debate.

'If 'king_login' is a member of sysadmin fixed server role table would be created under schema dbo, other wise table would be created under schema king_schema.'

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 5

Uwe Ricken (3/30/2014)
Hi,
the question and it's given answer is nonsence. You talk in your "solution" about the sysadmin role!
But in your script you add the user to the db_owner role.
If the user is member of the db_owner role the default schema will be used!

@Uwe - Even after going through the question and explanation, Do you still believe that

If the user is member of the db_owner role the default schema will be used!

?

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 6

Uwe Ricken (3/31/2014)
James Lean (3/31/2014)The script doesn't mention anything about the creation of king_login, so it is implied that this login already exists. In that case, we cannot assume that it either is or isn't already a member of any server roles, including sysadmin.
The fact that the user was created and added to the db_owner database role is just a red herring.
Thanks for the question Anoo.
Hmm - in a real scenario I have the chance to check an account. If I don't get the correct information my "reaction" suffers because of missing IMPORTANT information. That's not the way a dba should do his work and that's not the way the "correct" answer for this question should be!
This question is by the given reason for right or wrong absolute nonsence because the most important information (the login is sysadmin) is missing.
If I would ask you whether a INSERT INTO ... WITH (TABLOCK) is bulk logged or not you need to know what recovery model is your database running. If you don't have this information you have a chance of 33% for the correct doing 😛

A few questions to Uwe

"Hmm - in a real scenario I have the chance to check an account. " - Did you ever think of checking the account while answering the question?

"If I don't get the correct information my "reaction" suffers because of missing IMPORTANT information." -

In your opinion, if an information is missing in a specification what should be the best course of action? Make an assumption OR check for missing info / check for impact of missing info?

"If I would ask you whether a INSERT INTO ... WITH (TABLOCK) is bulk logged or not you need to know what recovery model is your database running" - If the recovery model is not known, do you think is it agreeable to assume the recovery model?

Still with all due respect to an MCM..

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 7

Hugo Kornelis (3/31/2014)
I have to agree with the others.
**comments removed *
(To my surprise, the Books Online CREATE TABLE page does not mention anything on how the default schema for the new table is resolvedO))
PS: Best practise = never rely on default rules for schema, always explicitly include it.

BOL explains this behavior under ALTER USER -

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 8

victoria_e_wood (3/31/2014)
In my opinion if the user was in fact a sysadmin why would you assign him or her rights over a database, they would automatically have rights over all databases...

Valid point.

Remind me of a scenario. The DBA Manager ( about 20+ years of experience ) was requested to refresh a financial application database to model and development. Plan was prepared, reviewed and executed.There was a step in the plan to assign rights over database ( similar to something given in the OP). When database was restored to model, everything was perfect. The application could connect to the database and everyone happy !!! On the following week, when database was restored to development, everything was a mess!! Errors like "Table xxx does not exists or you do not have privilege" were popped.

You could guess the reason by now, 🙂 , the login used at development was a sysadmin.

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 9

Hugo Kornelis (3/31/2014)
I have to agree with the others.
** Comments removed***
PS: Best practise = never rely on default rules for schema, always explicitly include it.

+1

One of the lessons from the scenario that I posted on the previous comment was nothing but the best practice that you mentioned! Always explicitly include schema name.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 10

Koen Verbeeck (3/31/2014)
WTH?
The script doesn't mention sysadmin but database owner.
The created user doesn't have sysadmin permissions, so the table will be created using the default schema.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 11

Koen Verbeeck (3/31/2014)
WTH?
The script doesn't mention sysadmin but database owner.
The created user doesn't have sysadmin permissions, so the table will be created using the default schema.
Points back please!

If interested, please have a look on the data refresh scenario mentioned 3 posts back.

Please note, king_login <> king_user ( as already mentioned by SQLRNNR ).

Comments posted by James Lean , Toreador and sestell1 clarifies WTH(:-)) it is, Hope no need to comment on that further.

Uwe Ricken Hall of Fame Points: 3169 More actions · Answer 12

Good morning Anoo,

Anoo S Pillai (4/1/2014)

@Uwe - Even after going through the question and explanation, Do you still believe that

If the user is member of the db_owner role the default schema will be used!

Of course 🙂

I've tested it on a SQL 2012 as follows:

[font="Courier New"]-- Create a login for demo

CREATE LOGIN uwe_login WITH PASSWORD = 'abc', CHECK_POLICY = OFF;

GO

USE demo_db;

CREATE USER uwe_user FROM LOGIN uwe_login;

GO

-- Add the user to db_owner role

ALTER ROLE db_owner ADD MEMBER uwe_user;

GO

-- Create the schema where Uwe should drop objects

CREATE schema uwe_schema AUTHORIZATION dbo;

GO

ALTER USER uwe_user WITH DEFAULT_SCHEMA = uwe_schema;

GO[/font]

Now I have the scenario as you have described in your question

The login uwe_login has privileges to the database as user uwe_user with the default schema uwe_schema.

When the account has been created i now create a table in the context of the uwe_user

[font="Courier New"]-- Now switch the context and create a new table

EXEC AS Login = 'uwe_login';

GO

SELECTIS_SRVROLEMEMBER('sysadmin') AS I_M_SYSADMIN,

IS_ROLEMEMBER('db_owner') AS I_M_DBOWNER;

GO

CREATE TABLE foo (id int);

GO

SELECTQUOTENAME(s.name) + '.' + QUOTENAME(t.name)ASobject

FROMsys.schemas s INNER JOIN sys.tables t

ON (s.schema_id = t.schema_id)

WHEREt.name = 'foo';

GO

REVERT;

GO[/font]

The result will be [uwe_schema].[foo]. The object has been created in the default schema of the user even he is member of db_owner.

The table will be created in the dbo-schema when the login is member of the sysadmin role.

My criticism wasn't your explanation but the way the question has been asked - the explanation by you was absolutely o.k. - so the question was a pit fall 🙂

Whatever - new day, new question. So what

Concerning the other questions by you:

Did you ever think of checking the account while answering the question?

Yes - if the information is mandatory I would check that information.

If the recovery model is not known, do you think is it agreeable to assume the recovery model?

NO - because it may be (concerning my example) careless. If an information may change the behaviour of the planned action I would like to make sure that all information are available to make a decision. Concerning my example it is mandatory whether the operation is bulk logged or fully logged for several reasons:

- Performance

- Bloating the log

- Backup of log

- ...

That's the way I'm working since 1992 when I started my business career 🙂

Still with all due respect to an MCM.

Even MCM are only people which are allowed to fail 🙂

Solely beeing a MCM does not mean I'm right - as you have seen from my answer 🙂

Best and a nice day...

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 13

@Uwe - Your previous post is among the one that I like most in this thread.

sestell1 SSChampion Points: 10230 More actions · Answer 14

Nevermind...

(miss-read the post I was commenting on)

Toreador SSChampion Points: 11365 More actions · Answer 15

Uwe Ricken (4/2/2014)
Now I have the scenario as you have described in your question

No - you have a scenario, not the scenario. You have made an assumption about how the login was set up.