News & Opinion
Brent’s Bad Idea Jeans: Stop People From Changing Tables with This One Trick
Developers hate his bad idea jeans:
|
1 2 3 4 5 6 |
CREATE VIEW dbo.StopThemDead WITH SCHEMABINDING AS SELECT COUNT(*) FROM dbo.Table1 UNION ALL SELECT COUNT(*) FROM dbo.Table2 UNION ALL.... |
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.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
This is evil. I like it 😀
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!!
Brent you are a genius, I love this trick and can’t wait to use it…. 🙂
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
HAHAHA, I love it!
Except don’t name it trAlterTable. Use only Cyrillic characters that look like Latin characters.
I did something like @Denis Gobo said on a development machine, using extended events to block anyone besides me from mucking with some tables. My boss made me remove it..
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. 🙂
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;
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?