SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

The Voice of the DBA

The Problems with Gods

When I was learning how to work with computers early in life, I ran across various documentation and writings that would liken the root account to being a god on the system. Over the years, I've seen other articles that note will describe "God mode" in various software systems. There have even been science fiction books describing the god-like abilities of a person that obtains a privileged account on a system.

To me, this is one of those places where our industry is immature. Having an account that can perform any task on a system, with no limits, is indeed like a god. This account can do anything, which is a double edged sword. Someone can reconfigure, fix, patch, update anything to ensure the system runs well. Or they can "rm /rf" the system.

Recently Verelox had their entire system wiped out by a former administrator. There are a variety of problems with this story, not the least of which is leaving old credentials active. Mike Walsh wrote about some of the issues from a backup perspective. I would add from a security perspective that this is why an individual's credentials need to be disabled immediately, and any well known, long time passwords need to be changed. We do this in the physical world by changing locks. We need to do this in the digital work as well.

However, I see a overreaching account with unlimited privileges as fundamentally a bad idea. Sure, this makes installing software or reconfiguring our system easier, but perhaps we should be required to use separate accounts for all sorts of options. This is especially true when we build a distributed system across multiple machines. As the number of services and systems increases, the value from having one account able to accomplish every task outweighs the potential issues.

Humans make mistakes. We make inadvertent ones when we're tired or distracted. We make malicious mistakes we regret; we make emotional mistakes by overreacting to a situation. We make mistakes based on incorrect information. If we have all the power over a shared system, then we may easily make mistakes that could cause an extraordinary amount of damage.

Our modern systems should include the ability for a separation of all duties and more default accounts that we configure. At the very least we should separate administration from auditing, and perhaps security as well. A slight inconvenience during setup is worth accidental issues in the future. Having separate accounts for different functions will also help to slow down the potential problems in the future by ensuring no one user account can be used to perform every function on a platform if it's compromised.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

ADVERTISEMENT
SQL Source Control

How to track every change to your SQL Server database

See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more

Foundry

Using SQL Census to audit SQL Server permissions

Redgate have just released SQL Census, a prototype tool that makes auditing SQL Server user access permissions much easier. In this post, Santiago Arias talks us through the brand new Server View feature, tells us what's up next in the tool's development, and explains how you can download it for free. Try the free prototype

Featured Contents

 

Azure DWH part 16: BCP to import and export data

Daniel Calbimonte from SQLServerCentral.com

In Azure SQL Data Warehouse we can use BCP to export or import the data. In this article, we will show how to do it. More »


 

What If Week: What Would You Fix Or Change?

Additional Articles from Brent Ozar Unlimited Blog

Brent Ozar, Erik Darling, and Tara Kizer think about what they would change or fix if they had access to SQL Server’s source code. More »


 

From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 69 – Heatmap)

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the Heatmap Custom Visual.  The Heatmap is useful for show data... More »


 

From the SQLServerCentral Blogs - The Tabular Model Documenter is now a Power BI Template

meaganl from SQLServerCentral Blogs

A while back I created the Tabular Model Documenter Power BI model that can connect to your SSAS Tabular or... More »

Question of the Day

Today's Question (by Steve Jones):

I've got a table of integers only and I want to put one trillion rows in the table for some testing. However, I'm worried about space. Which table should I create to use the minimal space?

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: T-SQL.

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

ADVERTISEMENT

Pro Power BI Desktop

This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

Which types of query statements can be used with the EXPLAIN operator in Azure SQL Data Warehouse?

Answer: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE AS SELECT, CREATE REMOTE TABLE.

Explanation:

All of these types of operations can used:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CREATE TABLE AS SELECT
  • CREATE REMOTE TABLE.

This will return the execution plan for the query without running the query in an Azure SQL Data Warehouse.

Ref: EXPLAIN - 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 2016 : SQL Server 2016 - Administration

Can't Shrink TEMPDB - Hi All, I am getting an odd error when trying to shrink tempdb a little bit DBCC SHRINKFILE: Page 7:2275824 could not...

SQL 2016 link server - Our environment requires that I create a link server from SQL to Oracle. The current production system functions as expected....

Error moving log.ldf - Hi all, Tried moving log.ldf to another drive but hit error. Script and error as below... ALTER DATABASE dbName set offline ALTER DATABASE...

Upgrade to 2016 questions - All, Unfortunately we are still running a SQL server 2000 server. I've got provisional approval to upgrade it this year so...

ToolBox missing for Maintenance task - This morning I logged into SSMS SQL2016 to edit a maintenance plan. I would add a cleanup task. But I noticed...


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

SSIS: Case clause with sintax error - Dear all, I am creating a derivated column task. It has a case in the expression but I always get the...

The order of TRUNCATE and DROP / DISABLE NONCLUSTERED INDEXES - Hi folks... I'm hoping for some guidance on TRUNCATE and NONCLUSTERED INDEXES.  I've been told conflicting information from a couple...

Get the year and Qtr - Ia am trying to get the year and Qtr from my date column which has values like- YYYY-MM (2017-02). I...

Query running slow across linked server - Hi all Firstly, apologies for the lengthy post, but I'm trying to get everything in I thought would be useful. I've got...

.bat file fails in SSIS - I have created a Execute Process task that runs a .bat file. This bat file creates a very small .txt...


SQL Server 2014 : Development - SQL Server 2014

TSQL Query help plz.. - Hi All, Need tsql help please. Below is the sample schema/tables and description is written at the end(my requirement). use master go...

How to encrypt Credit Card numbers? - We have now finally moved all our customers to SQL 2014 from old or very old SQL versions and I...


SQL Server 2008 : SQL Server 2008 - General

Recover data from encrypted log file - I have a 2008 R2 database encrypted using TDE that crashed. The MDF was lost, but the LDF survived. I was...

Column Length - Dear All, Question regarding the length of a column . As its understandable it has to be in accordance with the data...

Occasional mysterious time outs on SQL Server db table - Hi there, We have users in the field who use a mobile web GIS application (using Java) which use forms to edit/update spatial...


SQL Server 2008 : T-SQL (SS2K8)

Concatenate sp_MSForeachdb results - How can I join all the results of the below query into a single result set? Thanks exec master..sp_MSForeachdb ' SELECT dbschemas.,db_name(), dbtables. , dbindexes. , indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM...


SQL Server 2008 : SQL Server 2008 Administration

Replication job error once server rebootes - Hello I am facing an issue after rebooting the replication server while executing the job of replication i.e  Started: 10:23:00 AM Error: 2017-09-25...


Reporting Services : Reporting Services

IIF with an OR statement getting #Error - Hello all, I for the life of me cannot figure out why this would receive a #Error when running the...

Add 3 blank rows/boxes based on first letter of last name - Hello, I'm new to this forum and it seems awesome, so here's my first post/topic to test the waters. ;-) I’m working...


Data Warehousing : Strategies and Ideas

Multiple Calendars Using Kimball Outrigger Technique - Has anyone successfully used Kimball's outrigger technique for multiple corporate calendars in SSAS?  If so, can you share the technical...

This email has been sent to {user_email}. 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.
This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com