April 23, 2012 at 2:01 pm
Is there a code to force users to use a schema qualified object name. I need this because i am unable to create a default schema for windows user.
Thanks
April 23, 2012 at 3:25 pm
ouwanogho (4/23/2012)
Is there a code to force users to use a schema qualified object name. I need this because i am unable to create a default schema for windows user.Thanks
Database Users based on Windows Users can have default schemas. Did you mean Database Users based on Windows Groups? Those cannot have default schemas. Note that in SQL Server 2012 Database Users based on Windows Groups can have default schemas.
To answer your question though, no, there is no way to force anyone to schema-qualify their objects, outside of enforcing that through code reviews or possibly a custom TFS check-in policy.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 4:54 pm
You could use PBM to limit objects that don't fall into certain schemas. A little complex, but it could work.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 24, 2012 at 8:31 am
Yes, i meant Database users based on Windows group.
Thank you
April 24, 2012 at 8:33 am
Can you help me out with how to use PBM to enforce the use of schema qualified object
April 24, 2012 at 8:41 am
I don't have a reference that does this exactly, but here are a few things to get you started in PBM:
Video on policies: http://qa.sqlservercentral.com/articles/64789/
Standardization Article: http://qa.sqlservercentral.com/articles/Policy+Based+Management+%28PBM%29/88486/
Basically you'd want to target objects with your policy, and ensure that the schema name of the object matches (LIKE) certain values. I assume you have a domain (limited list) of schema names you want. If you choose the "on change prevent" option, you will rollback the changes and then users will learn to be sure they are schema qualifying objects.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 24, 2012 at 12:08 pm
So I used the schema facet and selected the @owner = 'dbo' in the expression box. When i tested it by creating a Stored procedure, it created my windows login (us\dhweeks) as the schema name. This results from us using windows group in SQL SErver which cannot have a default schema.
Thanks,
April 24, 2012 at 1:11 pm
I was going down the road of forcing schema-qualification in all queries, for example forcing all queries, say, within a stored procedure or script, to follow a form like this:
SELECT *
FROM SchemaName.TableName;
instead of like this:
SELECT *
FROM TableName;
If you want to prevent new object creation from happening in a schema other than dbo then I agree with Steve, PBM is the way to go.
For your Condition use the "Multipart Name" facet and set it so @Schema = 'dbo'. Then create your policy based on that Condition.
PS Don't forget to enable your policy after creation (UI has Enabled flag disabled in some builds) for real-time evaluation (right-click Policy > Enable).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 7:34 am
I am still able to create a stored procedure with a schema other than dbo after creating a policy that uses the 'Multipart Name' facet and "@Schema = 'dbo'".
April 25, 2012 at 7:43 am
Did you set it to "on change prevent"? Did you enable it after creation?
If you cannot get it, export the policy and attach it to this thread and I'll have a look.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 7:49 am
Disregard my last reply. IT WORKED!! I had to enable the policy and after that it worked.
Thank you guys for helping out.
April 25, 2012 at 8:08 am
You're welcome! I am happy you got a working solution 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 2, 2016 at 2:58 pm
Did you ever find a PBM way to do this.
I have been unable to find any facet that looks at code.
The facet Multipart Name only looks at the name of objects according to the properties dialog.
Don't think it can be done with PBM.
May 2, 2016 at 8:03 pm
tom.groszko (5/2/2016)
Did you ever find a PBM way to do this.I have been unable to find any facet that looks at code.
The facet Multipart Name only looks at the name of objects according to the properties dialog.
Don't think it can be done with PBM.
You can always use the ExecuteSql function against the facet that contains the database onjects you want to interrogate but not sure you can leverage "on change prevent" using it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply