Restricting users from creating nvarchar columns

Question

Restricting users from creating nvarchar columns

er.mayankshukla

SSCarpal Tunnel

Points: 4190
More actions
January 22, 2016 at 12:50 am

#399606

Hi All,
In one of the application, we have to restrict the developers from creating a table with unicode columns.
I was thinking if I can use Policy based management for that, but can't find any facets and condition for that.
Any help as to how I can achieve this or if that's possible

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

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

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 1

er.mayankshukla (1/22/2016)
Hi All,
In one of the application, we have to restrict the developers from creating a table with unicode columns.
I was thinking if I can use Policy based management for that, but can't find any facets and condition for that.
Any help as to how I can achieve this or if that's possible

I think this is not the right approach, better to explain to them why not to do so if a reasonable explanation exists.

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You can use a DDL trigger, but if they've got dbo rights or above, they can disable the trigger.

This is something that probably needs to be taken up with the developers and addressed in code review.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 3

Thanks Gail, I just created a DDL trigger on Create_table/alter_table

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

Here is a tip that walks through the setup using PBM for column data types:

https://www.mssqltips.com/sqlservertip/1993/find-all-sql-server-columns-of-a-specific-data-type-using-policy-based-management/[/url]

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Eric M Russell SSC Guru Points: 125552 More actions · Answer 5

As the DBA, you may not be able to stop developers from not following design rules in development, but you at least should stand between development and production. You do stand between development and production, don't you?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

JustMarie SSCertifiable Points: 7771 More actions · Answer 6

I'm with the folks who suggest enforcing this through code review and developer involvement. And yes they WILL disable the trigger if it's annoying them.

One possible thing to do is create some kind of trigger/job/whatever that will notify the DBAs when a nvarchar/nchar column has been created. It won't stop them but it will get the DBAs involved close to when it happens which makes it easier to dig out.

Wayne West SSC-Insane Points: 22586 More actions · Answer 7

I'm curious why you have such a policy or why your developers would want them. Yes, they consume twice the space of a non-unicode string datatype and obviously you need them to support some foreign character sets. Just wondering.

Easy code to see what unicode fields are in a database:

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE LEFT(data_type,4) IN ('nvar','ncha', 'ntex');

Obviously it can't tell you who did the deed, but you could change the DDL trigger from blocking it to logging it for future reference.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]