Dynamic data masking is a neat new feature in recent SQL Server versions that allows you to protect sensitive information from non-privileged users by masking it. But using a brute-force guessing attack, even a non-privileged user can guess the contents of a masked column. And if you’re on SQL Server 2014 or earlier, you won’t have the option of using data masking at all.
Inspired by an actual customer scenario: what if you have a legacy app that doesn’t schema-prefix its database objects, but you want it to work with a specific assigned schema? There’s a quick and easy solution.
SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database.
This is the third installment in a series on assigning SQL Server permissions. In the previous posts, we’ve looked at security principals (the users and groups that carry the permissions) as well as the securables (the objects you want to control access to). Building on this understanding, we’re getting ready to look at the fabric that connects principals with securables: Permissions.
When you create (or grow) the size of a database file, SQL Server will initialize the allocated space on the disk, i.e. fill it with zeroes. If you’re adding a large amount of space to your database file, this operation can take quite some time to complete. But just like there’s a “quick format” in most operating systems, you can allocate large chunks of database file space without initializing it. This is called “Instant file initialization” in the world of SQL Server.