Brent’s Bad Idea Jeans: Stop People From Changing Tables with This One Trick

Developers hate his bad idea jeans:

Presto, the schemabinding option means no one can change the underlying tables. (At least, until they figure out about your view.)

Obligatory disclaimer: seriously, this is a really bad idea. But it’s hilarious.

Previous Post
Why is SQL Server Slow Sometimes – But Only Sometimes?
Next Post
SQL Server 2012 SP3 Adds Memory Grant and Performance Features

21 Comments. Leave new

  • This is evil. I like it 😀

  • Kris Gruttemeyer
    November 19, 2015 8:28 am

    This reminded me of an article that had a trigger that wouldn’t allow you to use NOLOCK:

    http://sqlstudies.com/2015/02/16/tales-of-a-dba-fed-up-with-nolock-part-1/

    So happy I bookmarked that one!

  • I think I see an April fools day joke in this…

  • My co-workers are all now wondering why I busted out with maniacal “evil scientist” laughter.

    I would have loved to have this at my last job. Muahahahaha!!

  • James Youkhanis
    November 19, 2015 11:09 am

    Brent you are a genius, I love this trick and can’t wait to use it…. 🙂

  • Michael Engelby
    November 19, 2015 11:31 am

    Bah. You DBA types are all alike. 😉 LOL
    That said, I have view with schema binding for the joining of 4 tables. And then I indexed it….very handy indeed.

  • Developers HATE him!
    Check out this one weird trick to keep developers from mucking up your tables.
    Very cute Brent.

  • In true clickbait fashion, you need a stock image of something entirely unrelated and obviously photoshopped.

  • Besides revoking the db_ddladmin permissions, here is another way……

    CREATE TRIGGER trALterTable
    ON DATABASE — A DB level trigger
    FOR ALTER_TABLE –Event we want to capture
    AS
    PRINT ‘no way jose’
    ROLLBACK

  • Small remark on the NOLOCK hint, see
    https://msdn.microsoft.com/en-us/library/ms187373.aspx

    “Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.”

  • – Right-click locked table/view
    – View Dependencies
    – Kill person who made SCHEMABINDING view

    (bonus points if you don’t kill them, but create a new job that KILLs all their SPIDs every minute…)

  • Btw, this will require Enterprise edition…. as it should be. Should also require Enterprise Data Architect title to create a schema bind views. 🙂

  • Yakov Shlafman
    November 24, 2015 2:11 pm

    here is the correct definition from MSDN
    https://msdn.microsoft.com/en-us/library/ms187956.aspx?tduid=(1f25e4b7759ca16dbacd458776955d65)(256380)(2459594)(TnL5HPStwNw-MMJsKjWaLu8uTf3j8TVo1w)()
    When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.
    Here is an example:
    –1 create a table
    create table dbo.TableWithBound (
    recSeqId int identity(1,1) not null,
    EmpName varchar(100) not null
    )

    –2 create a view WITH SCHEMABINDING
    create view dbo.vw_TableWithBound WITH SCHEMABINDING
    as
    select EmpName
    from dbo.TableWithBound;

    –3 successfully change table definition that do not affect view definition
    alter table dbo.TableWithBound
    add DOB date not null;

    alter table dbo.TableWithBound
    add Address varchar(100) not null;

    –4 try to change a column definition that is part of the view with schemabinding
    alter table dbo.TableWithBound
    alter column EmpName varchar(300) not null;

    Msg 5074, Level 16, State 1, Line 1
    The object ‘vw_TableWithBound’ is dependent on column ‘EmpName’.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN EmpName failed because one or more objects access this column.

    –5 try to drop a column that is part of the view with schemabinding
    alter table dbo.TableWithBound
    drop column EmpName;
    Msg 5074, Level 16, State 1, Line 1
    The object ‘vw_TableWithBound’ is dependent on column ‘EmpName’.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE DROP COLUMN EmpName failed because one or more objects access this column.

    –6
    –if you need to drop the table, you should first drop the view
    drop view dbo.vw_TableWithBound;
    drop table dbo.TableWithBound;

  • Christoph Wegener
    November 30, 2015 5:11 am

    This is hilarious. I just wish I had a good enough relationship with any developer to actually implement that for real.

  • Not sure if anyone actually tried. I got error when creating view with count(*)…..

    Msg 4511, Level 16, State 1, Procedure vw_change_archive_table_first, Line 30
    Create View or Function failed because no column name was specified for column 1.

    • Pei – yes, when I post a bad idea, I tend to leave a bug in there on purpose. People who are smart enough to use this trick wisely will also be smart enough to fix the bug. 😀

      • Thanks Brent. I could not think of any trick to prevent adding new column in table other than ddl trigger or maybe policy based management .

  • Finally got a chance to read this…

    Dude you’re too soft. Give users SELECT permissions and deny all else. Or better, delete their accounts. No users, no security issues…

    I like it though. I know a few folks that want to be ummm curious in SQL?

Menu
{"cart_token":"","hash":"","cart_data":""}