I frequently get requests from our developers to create new databases. This is no biggie, and using the Standardising new database creation using Powershell and SMO scripts takes no time at all to implement. However, something that usually isn’t considered at the point of a request is: why do we need a new database?
Databases
SQL Server – Security: Schemas, Ownership Chaining and the dreaded TRUSTWORTHY flag – Part 1
Warning: this post involves me standing on a soapbox: it’s going to be a bit of a rant/preach about why everyone should care about security! Starting with….
SCHEMAS
When I talk to my developer colleagues, very few of them realise that database schemas are actually a security feature of SQL Server. They also don’t appreciate that security in a database needs to be designed, it doesn’t just happen. If it’s considered from an early point in the design of the database and application, then it will be much easier to implement and cause them less headaches in the long run when they ask me for some elevated permission or other and I say “no, because….” and start explaining about ownership chaining and a whole bunch of other security related stuff that makes them roll their eyes at me…. ahem, I digress.
Continue reading
When to create Indexes on Temp Tables
Creating indexes on Temp Tables sometimes appear to be a contentious issue. Amongst a number of things, they prevent Temporary Table caching, as described by Paul White in his blog post on Temp Table Caching Explained.
However, if we have a Temp Table with a large volume of data, and are then going to be manipulating subsets of this data, indexes are going to be beneficial in the same way as a “normal” table: they allow SQL Server to perform seek operations instead of just table scans, which can drastically affect the performance of your queries.
Continue reading
Natural Selection DBA 