Schema Qualified object Name

  • 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

  • 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

  • You could use PBM to limit objects that don't fall into certain schemas. A little complex, but it could work.

  • Yes, i meant Database users based on Windows group.

    Thank you

  • Can you help me out with how to use PBM to enforce the use of schema qualified object

  • 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://www.sqlservercentral.com/articles/64789/

    Standardization Article: http://www.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.

  • 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,

  • 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

  • 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'".

  • 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

  • Disregard my last reply. IT WORKED!! I had to enable the policy and after that it worked.

    Thank you guys for helping out.

  • 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

  • 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.

  • 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