A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Elevation of Privileges

This editorial was originally published on July 20, 2014. It is being republished as Steve is out of the office.

At SQL Bits this year I attended a security presentation from Andreas Wolter. The session examined some attack methodologies, showing the flow that an attacker might go through to gain information about your database instance with SQL Injection. It's a scary and eye-opening talk, and one that I might recommend to all DBAs and developers so that they can understand the dangers involved with poorly coded applications.

One of the most scary attacks was the elevation of privileges from a web user to a sysadmin on an instance, mainly because of the Trustworthy setting being enabled. I had never imagined this as an attack vector, but it was disconcerting to say the least. However it got me wondering about instances I've managed.

Would I detect if a new sysadmin were added? Or an existing user added to the role? I'm not sure I would, though that's certainly something I plan on setting up with some sort of monitoring to detect. I would guess that most DBAs, whether professional or accidental, might not catch this either, at least until some audit was performed. At that time it might be too late to protect your data, and certainly too late to protect your reputation.

Security is a tough topic, and it's an ongoing process to protect your systems. I hope to see more presentations like this at future events, and I'd encourage you to request them for any events you plan on attending. You can certainly do this for all SQL Saturday events (there's a suggest a session on the schedule page).

Security requires vigilance and vigilance requires monitoring. Both of those also need knowledge, so be sure that you don't neglect the security of your SQL Servers and continue to educate yourself over time as well as implementing technical solutions.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps

Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Featured Contents

Staying Current on Power BI Dashboards

Angel Abundez from SQLServerCentral.com

Power BI Dashboards can be kept current using simple DAX formulas in your data models. Save yourself time and energy by understanding how dashboard tiles work on PowerBI.com and what can be done to set your dashboards to a current period. More »

Object Security in Analysis Services Tabular 2017

Additional Articles from MSSQLTips.com

Koen Verbeeck looks at how to hide tables or columns in Analysis Services Tabular 2017 for certain users so they are not able to use them in reports or query them. More »

PASS Marathon GDPR in partnership with Redgate

This special edition of the PASS Marathon series will focus on the General Data Protection Regulation (GDPR). You’ll learn how the legislation impacts SQL Server data professionals around the globe and practical steps you can take to help ensure you’re ready for when GDPR enforcement takes effect on May 25th. More »

From the SQLServerCentral Blogs - TDE and backup compression – still not working?

Matthew McGiffen from SQLServerCentral Blogs

Until SQL 2016 if you used TDE (Transparent Data Encryption) you couldn’t use backup compression. In 2016 Microsoft changed this, but... More »

From the SQLServerCentral Blogs - Backup & Restore Script with a Move

SQLRUs from SQLServerCentral Blogs

Ok, I’ll admit it. I like scripts that are handy and do things. Especially if the scripts make my life... More »

Question of the Day

Today's Question (by Steve Jones):

I've got some data that contains US postal codes. This data is stored in a numeric field. If I use the FORMAT() function, what format string should I use to ensure that any leading zeros are replaced with a real 0 and the rest of the numeric values are returned correctly? What string is the second parameter of the FORMAT() function.

Same data:

 WITH myvalues AS ( SELECT n FROM ( VALUES (123) , (80138) , (2910) ) AS a (n) ) SELECT TOP 10 FORMAT(myvalues.n, ?) FROM myvalues;

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: FORMAT().

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Securing SQL Server: DBAs Defending the Database

Protect your data from attack by using SQL Server technologies to implement a defense-in-depth strategy, performing threat analysis, and encrypting sensitive data as a last line of defense against compromise. The multi-layered approach in this book helps ensure that a single breach doesn't lead to loss or compromise of your data that is confidential and important to the business. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I want to use the mathematical function cosine function in a Python script inside of SQL Server. I know that this function isn't in the base Python interpreter, but is in the math module. What do I run to get access to this function in my script?

Answer: import math

Explanation:

In Python, to access code inside of a module from your script, you use the import command with the name of the module.

Ref: Modules - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Administration

How would you lock down a SQL DBA to have local admin on the SQL box - I would like to know if we can create a separate group or what permissions on the server should be...

SQL Server 2017 : SQL Server 2017 - Development

CTE Crazy: Sums, Counts and Pivots I'm missing something here - I've not had to use SQL for a while and I'm having trouble getting to grips with aggregates again. In the...

SQL Server 2016 : SQL Server 2016 - Administration

Need Tutorial on Differential Backup - I need to backup a SQL Server 2016 database weekly - and take incremental backups daily I found this snippet: -- Create a...

AlwaysOn Availability Groups Cluster Name vs. Listener Name - Hi, I've been working on setting up AOAG for the first time. I see a lot of benefits to having this...

Can I Restore with Device to Database on another Server? - my client wants me to set up an incremental database backup, which entails restoring to a device, not a file I...

SQL Server 2016 cluster issue - Hi, We have installed SQL server 2016 Standard (SP1) in two-node Windows 2016 Fail-over cluster successfully.. <by mistake> When we restart SQL...

Index Framentation - Hi, I have tables in databases with avg_fragmentation of 86% and a page count of 26 and even less page count...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Writing variable length records to a flat file destination in SSIS - I have a 132, 180, 18, and 34 column records and I need to write them to a flat file...

SQL insert auto insert with stop value - Experts, Sql need to add automatically the numbers with sequence . DDL is, USE GO /****** Object: Table . Script Date: 2/27/2018...

DTSX Pkg From Stored Procedure (completely frustrated) - Help please!! I am bruised and bloodied from mashing my head, for two weeks now, against stuff I do not...

SQL Server 2014 : Administration - SQL Server 2014

Application starts throwing SQL error after database failover. - I have a situation and looking for some guidance. I have a two node SQL server always on on setup...

SQL Server 2014 : Development - SQL Server 2014

How does SQL Server handle concurrent operations? - Hello, We are having an issue which I suspect is database related. We have a web application on which we can...

Add Group ID value for set of sequential rows - Hi To be honest I wasn't sure how to phrase this subject, I hope it makes sense after I describes my...

SSRS Report: Any way to copy or download either an RDS file or the text needed to create one? - It's been a while since I dealt with SSRS. Am hoping to find an easy way to either download a...

OpenRowSet No Field Terminators - Hi, To start I have minimal experience with OpenRowSet and BCP. I usually do processing with SSIS but this new project...

SQL Server 2012 : SQL Server 2012 - T-SQL

How to handle very large dataset - I need to find the most recent post date for all the invoices in my table. There are millions of...

SQL Server 2008 : SQL Server 2008 - General

Transaction logs - We had an issue where our transaction log backup job became disabled over the weekend and didn't find out until...

SQL Server 2008 : T-SQL (SS2K8)

union with constant values - Hello, I'm doing this: ... union select 'EVY', 'Everyone', ... from ... where ... and @Grouping = 'false' That @Grouping = false still returns the row if the value is true....

Splitting a Full Name - I'm trying to split a full name column up into First / Middle / Last....but some of the names are like "Joe...

SQL Server 2005 : T-SQL (SS2K5)

checking for data exists in a multiple tables in single query - Before deleting a record, i have to check whether the record exists in someother tables or not.. need help in doing

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.