SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022

[Note from Pinal]: This is a 22nd episode of Notes from the Field series. Security is very important and we all realize that. However, when it is about implementing the security, we all are not sure what is the right path to take. If we do not have enough knowledge, we can damage ourself only. DB Data Roles are very similar concept, when implemented poorly it can compromise your server security.

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of Brian in his own words.

I am prejudiced against two fixed database roles: db_datareader and db_datawriter. When I give presentations or talk to customers, some are surprised by my stance. I have two good reasons to recommend against these two roles (and their counterparts, db_denydatareader and db_denydatawriter).

A Violation of the Principle of Least Privilege

The first reason is they violate the Principle of Least Privilege. If you’re not familiar with this security principle, it’s really simple: give permissions to do the job – no more and no less. The db_datareader and db_datawriter roles give access to all tables and views in a given database. Most of the time, this is more access than what is needed. This is a violation of the Principle of Least Privilege.

There are some cases where a user needs such access, but there is always the possibility that a new table or view will be added which the user should not have access to. This creates a dilemma: do I create new roles and remove the user from db_datareader or db_datawriter or do I start using DENY permissions? The first involves additional work.The second means the security model is more complex. Neither is a good solution.

Failing the 3 AM Test

The second reason is the use of these roles violates what I call the “3 AM test.” The 3 AM test comes from being on call. When I am awakened at 3 AM because of a production problem, is this going to cause me unnecessary problems? If the answer is yes, the solution fails the test. I classify db_datareader and db_datawriter role usage as failing this test. Here’s why: the permissions granted are implicit. As a result, when I’m still trying to wake up I may miss that a particular account has permissions and is able to perform an operation that caused the problem. I’ve been burned by it in production before. That’s why it fails my test.

An Example

To see why this is an issue, create a user without a login in a sample database. Make it a member of the db_datareader role. Then create a role and give it explicit rights to a table in the database. This script does so in the AdventureWorks2012 database:
USE AdventureWorks2012;
EXEC sp_addrolemember @membername = 'TestDBRoleUser', @rolename = 'db_datareader';
CREATE ROLE ExplicitPermissions;
GRANT SELECT ON HumanResources.Employee TO ExplicitPermissions;

Pick any table or view at random and check the permissions on it. I’m using HumanResources.Employee:

We see the permissions for the role with explicit permissions. We don’t, however, see the user who is a member of db_datareader. When first troubleshooting it’s easy to make the assumption that the user doesn’t have permissions. This assumption means time is wasted trying to figure out how the user was able to cause the production problem. Only later, when someone things to check db_datareader, will the root cause be spotted. This is why I say these roles fail the 3 AM test.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

Developer – How To Market Yourself as a Software Developer

“Everybody wants to make money and build a successful environment around and it is a universal truth my friend”, John mentioned to me while we were talking on Skype earlier this year.

Conversation: John and Pinal

John is the founder of simpleprogrammer.com and a very close friend of mine. We discuss pretty much anything and everything. He is from USA and I am from India, so our conversation usually starts either in the morning or evening, but it keeps on running for hours, thanks to our family who allows us to do this crazy long but productive talk.

When John said he has discovered how famous software developers make so much money, I found it very intriguing. I know John for many years, he does not do cheesy talk or say things without substance. I was wondering what would John do.

What would John do?

John had two options 1) He keeps this secret with him forever or 2) Tell his secret to the world. If you know John, you can easily guess that he is not going to keep quiet about it. He is going to make it public and make it universally accessible to the world. It is very similar to the publishing the books like Alchemist or The Secret or any other similar books. The author of those books does not keep the secret of success to themselves only. They share and make the secret a true instrument in people’s life.

As expected, John built a fine program of “How to Market Yourself as a Software Developer”. This is indeed a very interesting program as it is not a video, book, tutorial or any exercise file. It is the matter of the fact everything. John has not restricted the success story to a simple medium. He has let the story build around a concept and has not limited the potential of the program.

What did I do to help John?

Nothing. We all have nothing to do to help John. This is not about John or Simple Programmer. It is about a program which tells and shares secrets of How to Market Yourself as a Software Developer. We all are a software developer and we all are successful in our own field. When I say We – this also includes YOU too. Think about it, are not you successful in what you do? You absolutely are. We all have done something right in our past or present. We all have secrets which are not shared with the world.

Just like that, John has compiled his own success story with many other’s success stories and build this program. I think it is a story build with the experience of the many real software developer who has achieved something in their life.

What NEXT?

Join the program. I am sure there are plenty of things which you know, I should know to be more efficient and successful. Similarly, there are plenty of things other knows, but you need to learn to be more successful in your career. Why not learn it from each other. I will be interested to know your thoughts about how can I be more successful.

Secret Code

If you want to join the program. Here is the secret code I have got from John. You can go to the link http://simpleprogrammer.com/market to join the program. The program cost USD 299 however, when you use secret code sqlauthority, you will get a discount of USD 100 (that is 33% to the MRP).  You will have instantly access to the entire package immediately as well as 30 Day Money Back Guarantee.

What are you waiting for?

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – List User Defined Tables – Two Methods

There are different methods to list out user defined tables in MySQL. We will see two methods in this post.

Let us create the following tables in the database named Test

Create a database named Test


Create the tables named items and sales in the database Test.

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Now to list out all user defined tables, use any one of the following methods

Method 1: Use SHOW TABLES command


If you execute the above statement, it will list out all the user defined tables in the database in which it is run (in this example the database name is Test)

The result is


You can also use database name along with this


Which will give you the same result


You can make use of ANSI standard INFORMATION_SCHEMA.TABLES
view to do the same shown as below


The result is


This is a very small trick but works great when you want to know what are different tables exist in MySQL Database.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Detect Schema Change Across Two Servers

If you are database developer or database administrator, you will absolutely feel the story. You can download the software discussed in this blog post to play it along.

Story of My Early Career

A very interesting scenario, I have encountered in my earlier career. In earlier days, we used to develop everything on my local box. Our application and database both stayed in the same box. I was allowed to code only on the module or section which was assigned to me. I can’t go out and code or do anything else besides what I was assigned. When we code this modular way, it was easy to deploy the code upon completion of the project. This all worked fine till I went for vacation.

Once I went on vacation and the development of my project was done by my friend and colleague Romesh. Upon returning, Romesh wanted to hand over the entire project to me and he wanted to continue on his own project. The problems started here as his project and my project were totally different. Our database has been the same, but the table and stored procedure which we worked were mostly very different. In this situation the best practice for me was to take the schema changes he has done on the machine to my machine and continue the project. I had to be very careful as I should not take any other schema from his current project. If I have a part of his project, when I deploy my project that will go live and it should not happen.

The Epic Dilemma

Here was the problem – we were sitting together and discussing how to synchronize the schema from his machine to my machine selectively. I did not want the schema of the project he was earlier working on as that will further complicate my deployment, but I do want all the object which belonged to my projects. How do we achieve this complicated goal of selective schema difference detection and synchronization? 

Selective Schema Compare Detection and Synchronization

After some research online, we landed on Embarcadero’s DB Change Manager. We downloaded the trial version and tried to do a selective schema comparison. It is pretty easy to use tool which walks us through the wizard and instantly we were able to detect schema change between my database server and Romesh’s database server. We used a couple of filters to indicate which are my tables and which are his tables and quickly able to only see the selective changes.

Once we detected the changes between schema, we quickly generated the script to deploy on my database server with the help of Manual Synchronization features. I copied the script and I was instantly able to move on with the my project. I really feel that sometime tools like Embarcadero’s DB Change Manager makes quite a positive impact on efficiency of work life.

Here is the quick video which demonstrates how quickly one can use Embarcadero’s DB Change Manager compares schema.

Let me know what you think of this video. You can download Embarcadero’s DB Change Manager.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Q and A from Facebook Page

Social media has changed everything, there were times when we used to send emails but nowadays things have changed. We all have many different ways to connect with each other. Here are a few questions which I have received on the Facebook Page in the month of the march and I am sharing a few of them here.

Q: How to change collation of the our entire server? (link)
A: You will have to install your entire SQL Server to change collation of the server.

Q: How to recover system databases from suspect mode without having any backups? (link)
A: You need backup to recover a corrupt system database.

Q: How to retrieve last 5 inserted rows from a table? (link)
A: You can retrieve the data based on date field or identity field. If you do not have either, no luck. (blog)

Q: What does *= operator does for SQL Query? (link)
A: It is an old way of using left join, now it is deprecated so avoid using it.

Q: How to reduce CXPACKET and PAGEIOLATCH_XX waits in SQL Server database? (link)
A: CXPacket is the effect of parallelism, which may or may not be a problem. PageIOLatch indicates a problem with the slowness of the disk subsystem. This would very well cause CXPacket as well. (blog, blog)

 Well, these are the five questions which I have received on Facebook. If you have any question, let us connect on Facebook.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Server 2014 Developer Training Kit and Sample Databases

SQL Server 2014 RTM is around the corner (2014). There is plenty of the interest in the SQL Server 2014 in recent time. One of the most frequently asked questions is about learning resources of SQL Server 2014. Everybody wants to learn SQL Server 2014 but there is not enough learning material available currently in the outside world.

Microsoft has released two important resources around it.

SQL Server 2014 Developer Training Kit

The SQL Server 2014 Developer Training Kit includes technical content designed to help you learn how to develop SQL Server 2014 databases, BI Solutions, and gain insights into the new cloud capabilities to simplify cloud adoption for your SQL databases and help you unlock new hybrid scenarios.

SQL Server 2014 Developer Training Kit Sample Databases

This download contains several sample databases for the SQL Server 2014 Developer Training Kit which showcase many of the new and improved features of SQL Server 2014, including Clustered Columnstore, Online Operations, and Resource Governor IO.

Here are few of the key features in SQL Server 2014

  •  Resource Governor IO
  • Online Operations
  • Updatable Columnstore Index
  • In Memory OLTP Engine
  • Enhencements in SSDT and BI tools
  • Power BI Integration

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Generate Script for a Table Using SQL

In SQL Server, to generate the CREATE TABLE script for a table, you need to rely on the SQL Server Management Studio (SSMS) tool and there is no inbuilt function supported to do this using SQL. However, in MySQL you can generate the script for a table using SQL.

Let us create the following table

CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Now to view the script of the table sales, you can make use of SHOW CREATE TABLE statement. This statement accepts table name as parameter and returns the CREATE TABLE script for that table.

Run the following code


The resultset has two columns where the second column displays the following script.

CREATE TABLE 'sales' (
'item_id' INT(11) DEFAULT NULL,
'sales_amount' DECIMAL(12,2) DEFAULT NULL,
PRIMARY KEY ('sales_id')

Note : You can use the same SHOW CREATE TABLE statement to view the script for a VIEW although there is a seperate SHOW CREATE VIEW statement that accepts view name as a parameter.

Reference: Pinal Dave (http://blog.sqlauthority.com)