Users creating there own schemas

Question

Post reply

Users creating there own schemas

sqlnaive

SSCoach

Points: 17437
More actions
August 9, 2011 at 2:26 am

#390150

I've created group G1 and added users U1 and U2 to that group. Created role R1 in database DB1 and added G1 to the role R1. Now when user U1 logged in and created any object, it was created in schema u1 and not in dbo. Did i missed somehting ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

When you created the user what did you specify as the default schema

e.g

CREATE USER [ABCD] FOR LOGIN [ABCD] WITH DEFAULT_SCHEMA=[notdbo]

Jayanth Kurup[/url]

sqlnaive SSCoach Points: 17437 More actions · Answer 2

My problem is with users within NT group. SQl does not allow assigning default schema to NT groups. But I want all the users inside that group to have default dbo schema. How should I scheive this ?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

From Remarks section http://technet.microsoft.com/en-us/library/ms173463.aspx:

DEFAULT_SCHEMA cannot be specified when you are creating a user mapped to a Windows group, a certificate, or an asymmetric key.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlnaive SSCoach Points: 17437 More actions · Answer 4

Then how can i manage to enforce dbo schema across my environment where hundreds of users are going to hit database sooner or later, each having created their default schema ?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

sqlnaive (8/10/2011)
Then how can i manage to enforce dbo schema across my environment where hundreds of users are going to hit database sooner or later, each having created their default schema ?

I sat for a Denali presentation and I am not sure I heard this correctly but I think support for this is added in Denali because it is a common request.

For now however, you should be able to manage this with Policy-Based Management to prevent users from creating a table in any schema besides dbo.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlnaive SSCoach Points: 17437 More actions · Answer 6

Thanks opc. I was going through Policy based management and found it pretty interesting. To solve this default schema issue, is it fine to create a condition with field @DefaultSchema = dbo for Facet = Database and finally creating a policy based on that condition ? Will that prevent all the users to create db objects other than dbo schema ?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

sqlnaive (8/11/2011)
Thanks opc. I was going through Policy based management and found it pretty interesting. To solve this default schema issue, is it fine to create a condition with field @DefaultSchema = dbo for Facet = Database and finally creating a policy based on that condition ? Will that prevent all the users to create db objects other than dbo schema ?

I do not see a Schema property on the Database Facet. I think you may need to do this on each Facet relating to the objects you want to restrict, e.g. Facet = Table & Facet = Stored Procedure & Facet = View, etc. Please post back once you settle on a solution.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato