Show Desktop in Windows 10

I am always flooding my desktop with open windows while developing.  Showing the desktop is a must have in a situation where you need to switch gears for a few minutes.  Windows 10 has the show desktop built in right out of the box.  Simply click the small button in the lower right hand corner.

Auto Fix Orphaned User

When restoring a database to a new or different instance, logins at the instance level are not mapped to the database users.  SQL SERVER has a built in stored procedure to help us identify orphaned users, sp_change_users_login.  We can also use this stored procedure to fix the mapping if the login exists at the instance level.  Finally, if we need to create a new login for the existing user, we can create a login and password.  See the below examples.

Select from all tables on an Instance

We have all inherited less than desirable SQL Server Instances.  DBAs and Developers will both need to go through the countless databases, tables, SPs, etc. to get a handle of the situation.  In such a fragile situation, we need to ensure we keep our data integrity and prevent our developers from accident updates while trying to trouble shoot an issue.   Here is a nice Server Level Permission set to allow Developers to select from any table in any database on the instance.

Restore Master Database

Recently I was tasked with migrating a SQL Server Instance to a new infrastructure.   Along with the migration, I also had to create a DEV, QA, UAT  environments as they were not part of the original infrastructure.  Instead of trying to move all the pieces separately, I migrated the databases, along with restoring the Master database.

To being we must make sure the new SQL Server Instance matches the existing instance.  Here is how we make sure the Instances match.

db_executer

SQL Server comes with several predefined database roles which fit the needs of most day to day user needs.  A big miss that I have found is the ability to execute stored procedures.  Developers need the right to execute the SPs they are creating in the DEV and QA environment.  Many service accounts also need execute rights.  All that needs done is to create the Role, grant execute rights, and add members to a role.

CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
EXEC sp_addrolemember 'db_executor','User or Group'

 

TDE - Transparent Data Encryption

Transparent Data Encryption (TDE) is a method of encrypting the database, data files and log files on the physical disk.  TDE is also known as encrypting data at rest.  TDE does real time encryption and decryption between the disk and memory.  We will be using the same Key Hierarchy and process as discussed in SQL SERVER Column Encryption.  We have one additional step, the encryption of the database itself, shown below.

SQL SERVER Column Encryption

Encryption has become a necessity when storing data.  There are many ways to encrypt data, here we are going to encrypt a column within the database using a key within the database.

Encryption Hierarchy - This is the encryption hierarchy within SQL SERVER

AES - starting with SQL Server 2016, we can only use AES_128, AES_192, and AES_256 encryption.

Looking at the standard encryption process, each layer of the hierarchy is encrypted by the layer above.  In this example we will use a symmetric key stored within our database.  We will encrypt our key with a certificate also within our database.  Our Certificate will be encrypted by the database master key DMK.  Continuing with standard SQL Server architecture, we also have a Service Master key SMK which is created when SQL SERVER is installed.  Our top level of encryption is Windows Data Protection API (DPAPI).  We could add additional layers.

Screen Capture Blurs

When I first started writing technical blog posts, I found an immediate need to be able to blur parts of my screen captures.  I have a habit of using the built in windows snipping tool (actually anchored to my task bar) and wanted to use a tool in addition to my snipping tool.  I quickly found awesome screenshot https://www.awesomescreenshot.com/

Awesome Screenshot is a browser extension.  I can, however, take my snips from day to day work, simply select Awesome Screenshot, click Select a local Image, drop it in my browser, and blur my work from there.

This is a great tool, simple to use, and great for blogging!

VisualCV

Every professional must have a resume.  Most of the resumes I have reviewed in the IT world have been lack luster at best, and these are the ones which make it through HR.  One of the problems I had at the beginning of my career was that my resume would be spun up one night after work when I decided I wanted something more.  Each time would be a new resume, new format, complete rewrite from the ground up.  This method was error prone, time consuming and generally demoralizing.  Over time I have learned to continuously update my resume every few months with the latest projects I have been working on.  My resume is now ready to go at a moments notice.  Even though I am continuously tweaking my resume in its current format, I am always looking for ways to

Temporal Tables

Temporal tables, or system-versioned tables are new to SQL SERVER 2016 and are a way to have access to all of the history of a table.  In small databases or for tables where rows are rarely updated this feature is a beautiful thing and will soon find its way into many best practices.  To get started, simply right mouse on tables > New > Temporal Table > System-Versioned Table...

When creating a new Temporal Table, you are provided a template to generate the new table.  The first section of the given template is for dropping the table if it already exists.  Since this is a new table we can skip this part and go to the create table section.  *Remember, System versioning must be set to off in order to drop the table.
The second section of the template is for creating the system versioned temporal table. In order for a table to become a system versioned temporal table, we must have a Primary Key and 2 datetime2 colums.   The with clause of the create table statement turns system versioning on and defines the history table.