DDM can be used to hide or obfuscate sensitive data, by controlling how the data appears in the output of database queries. It is implemented within the database itself, so the logic is centralized and always applies when the sensitive data is queried. Best of all, it is incredibly simple to configure DDM rules on sensitive fields, which can be done on an existing database without affecting database operations or requiring changes in application code.
This looks like a nice way of getting some data masking on the cheap. It also looks like there are a couple of built-in functions for defining data types, as well as the UNMASK permission so that you don’t need to modify application code to call some type of unmasking function.
Sometimes when using SSMS you will see a redline under a table or object name in your T-SQL. This means SSMS thinks the object doesn’t exist in the current database. Usually it’s right, but if you have just created the object, the query editor wont know as it’s local cache is not regularly refreshed. To force a refresh you can hit Ctrl + Shift + R but I always forget keyboard shortcuts. For this I like to add a button to the toolbar.
This is a good intro-level article on SSMS basics and some configuration options.
A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. SQL Server principals come in more than flavor. (This is where a lot of confusion gets introduced.) The different types of principals are database and server. A database principal is also called a database user (sometimes just user). A server principal is also called a server login, or login for short.
Server and database principals are quite a bit different in function and come with different permission sets. The two are sometimes used, in reference, interchangeably, but that is done incorrectly. Today I hope to unravel some of what each is and how to see permissions for each type of principal.
Read the whole thing.
Let’s take a closer look at the format file 1 There is a single <RECORD>element with multiple <FIELD> elements that correspond to the fields in the data file. There is also a single <ROW> element with multiple <COLUMN> elements that correspond to table columns. Note the xsi:type attributes that specify the SQL data types for the columns of the returned rowset.
I’ve never had great luck with OPENROWSET reading files and tend to reach for SSIS, but I think part of that is I’d never seen as clear an example as Dave’s.
Whenever you set up a script to create or drop an index, you want a safety check in there. Drop the index if it exists. Create the index if it doesn’t.
Or do something else programmatically. Checking if an index exists is a pretty frequent task. But there’s no simple function to test if an index exists in SQL Server.
Good article, and in the comments, Kendra talks about the next logical step: consolidating indexes.
If the check constraint is trustable, it can be used by the query optimizer. For example, if the check constraint avoid values below 100 in a field and a query for 50 arrives, the query optimizer uses the check constraint to stop the query.
The query optimizer can only use the check constraint if it’s trustable, otherwise it could exist in the table records with values below 100, according to our example, and the query would loose these records.
Dennes then goes on to show how you can have non-trustworthy constraints and how to fix the issue.