Auditing the usage of “EXECUTE AS” in a database

A question was recently asked in the MSDN Transact-SQL Forum about detecting the usage of “EXECUTE AS” in a specific database. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9a385b29-1181-47aa-9f80-062d1bed3cd4/remove-spaces-in-like-search?forum=transactsql The “EXECUTE AS” clause was introduced in SQL Server 2005 as a powerful execution context enhancement to grant permission to a function, stored procedure, or trigger, while maintaining strict control over permissions on…

0

Sorting MSDN Forums threads by number of page views

After answering questions in the MSDN forums for the past 10 years, I was curious about which of the threads I’ve contributed to have received the largest number of page views / hits and discovered a little trick to be able to accomplish this. It isn’t built-in through the UI, so you need to do…

4

Windows Server 2012 / 2012 R2 – Logon restricted to members of Local Administrators group

Within one of the SQL Server environments that I support, we recently experienced a Windows Server 2012 operating system crash followed by a situation where the SQL Server 2012 service was unable to start with the following error message: Error message 7038 – The MSSQLSERVER service was unable to log on as NTSERVICE\MSSQLSERVER with the currently configured password…

0

Transparent Data Encryption (TDE) stuck in State 2 – Encryption in Progress

Another day, another interesting troubleshooting scenario with SQL Server 2012.  In this case, a customer was attempting to enable Transparent Data Encryption (TDE) on a couple SQL Server 2012 databases.  The process went smoothly until they encountered an issue with a single database.  The problem database in this case was about 50 GB in size,…

4

SQL Server 2014 Service Pack 1 Unattended Setup Error – "The specified value ‘Upgrade’ is invalid"

SQL Server 2014 Service Pack 1 Unattended Setup Error – “The specified value ‘Upgrade’ is invalid”   I was recently sent a question regarding a failed unattended install of SQL Server 2014 Service Pack 1.  The customer had spent some time searching online for the error message, but found surprisingly little detailed information for unattended…

4

EOMONTH() Equivalent in SQL Server 2008 R2 and below

  SQL Server 2012 introduced several new system functions pertaining to the date and time data types.  In my opinion, I’ve found the EOMONTH(), “end of month”, function to be one of the most useful.  The EOMONTH() function returns the final day of the calendar month, thus greatly simplifying a common calculation, taking into account…

10

SSMA for Oracle – Error: No columns were returned from the database for table ‘Foo’

  Error:  SQL Server Migration Assistant for Oracle fails to load columns with error “Loading columns for table or view ‘Table_Name_1’ failed.”   Solution:  Connect to the Oracle instance using the OLE DB provider instead of the default .NET Oracle Client provider.   On a recent client project, we were tasked with upgrading a very…

2

Bulk Database Migration from On-Premise SQL Server to SQL Azure – Step 1: Bulk .bacpac Export

I recently had a task to perform a bulk migration of hundreds of databases to Windows Azure SQL Database (formerly known as SQL Azure) from an instance of SQL Server 2012.  If you need to migrate a very small number of databases, the easiest way is likely through the SSMS export Data-Tier Application wizard.  You can…

4

Creating Complex Test Databases – Generating One Table for each of the 2,397 supported Collations

As a follow up to my series on complex test databases, this article will cover one of the more interesting test databases that I recently created.    Goal:  Create a database that includes one table for each supported collation.  Each table contains a single column with the various column level collations supported by SQL Server 2012 (nearly…

0

TSQL – Solve it YOUR Way – Finding a Person’s Current Age Based on Birth Date

Introduction: As part of the blog series TSQL – Solve it YOUR Way, today’s topic will cover a question asked recently in the T-SQL MSDN forums where the solution, at first, seems extremely obvious.  However, as solutions were proposed and flaws were discovered with the proposals, the interesting nature of the problem was revealed.  As is the theme…

12