Click here to monitor SSC
  • Av rating:
  • Total votes: 14
  • Total comments: 2
András Belokosztolszki

Discovering Security Uses for SQL Compare

06 August 2007

This article deals specifically with Red Gate's schema comparison tool, SQL Compare, and how it might be used to help manage SQL Server security. It is written by András Belokosztolszki, a Red Gate employee and the architect of SQL Compare 4, 5, and 6.

Security in SQL Server is a complex subject. However, because many of the com­po­nents of the security system are part of the data­base schema, SQL com­pare can help with cer­tain security administration tasks even though it is not specifically designed to do so.

SQL com­pare can help in the follow­ing ways:

  • Review­ing and copy­ing data­base principals (users and roles). The data­base users can be reviewed, and their definitions inspected us­ing SQL com­pare. You can view data­base and object level per­miss­ions. It will quickly show you if a particular user is a windows user or whether it is a user that is associated with an asym­metric key. SQL com­pare can also assist in copying data­base users and logins be­tween servers.
  • Administer­ing per­miss­ions on data­base Objects. You can explore individual data­base objects and see their object level per­miss­ions. SQL com­pare can be used to copy these per­miss­ions be­tween data­bases.
  • Monitor­ing changes in principals and per­miss­ions. A copy of a data­base can be created at a point in time and subsequently com­pared with the current data­base. SQL com­pare can com­pare a live data­base with another live data­base, a SQL com­pare schema snapshot, or a set of SQL creation scripts. This is useful in identifying changes to the data­base principals and to their per­miss­ions since the creation or synchronisation of the reference data­base.
  • Support­ing role-based security. Role-based security assigns per­miss­ions to roles instead of users. If a user needs to be granted a per­miss­ion, rather than be­ing assigned the per­miss­ion directly, it is made a member of the relevant data­base role. SQL com­pare provides an option to facilitate explor­ing the differences in such data­bases.

Review­ing and copy­ing data­base principals

SQL com­pare displays the definition of each data­base principal, and shows all the data­base and object level per­miss­ions assigned to the user or role:

SQL com­pare can help you migrate these data­base users to a different data­base. If the SQL Server login does not exist on the target data­base, SQL com­pare will create it and will set its password a default value - "p@ssw0rd". This allows the synchronization of the schemas to be comp­let­ed, but it is cer­tainly not ideal from the point of view of security. User logins should be created separately, and their passwords set to a non-default password.

It is common practice to create users in a development data­base with names that match the users in the pro­duct­ion data­base, but which are of different types. In a pro­duct­ion data­base users are likely to be based on Windows and SQL Server logins, or on cer­tifi­cates and asym­metric keys. In a development data­base, it is often sufficient to create users us­ing "WITHOUT LOGIN". This can cause problems when com­par­ing the data­base users and their properties be­tween pro­duct­ion and development.

In SQL com­pare, the "Ignore User Properties" option is available to help you with this situation. When com­par­ing data­bases, select this option to specify that users should be com­pared only on their names rather than all of their properties. This still allows you to identify users that exist in only one of the data­bases, or that are present in both of the data­bases, but have different per­miss­ions or extended properties assigned to them. You can also use the "Ignore extended properties" option to force SQL com­pare to omit extended properties, and, in this way, to concentrate solely on the differences in per­miss­ions.

Sometimes, a data­base may contain 'orphaned' users, who do not have an associated security identifier (SID) in the master data­base. This may happen when a data­base is restored from backup on a different server. In this situation, SQL com­pare displays a warn­ing to help you identify the problem prior to synchronization.

Users that are based on cer­tifi­cates or asym­metric keys are more difficult to com­pare, as these objects cannot be written as SQL statements without access­ing the file system on the server. For objects that use cer­tifi­cates and asym­metric keys, SQL com­pare provides warnings, to help you resolve any migration issues manually. You can choose to ignore cer­tifi­cates, sym­metric and asym­metric keys when com­par­ing schemas.

Administer­ing per­miss­ions on data­base objects

By default, SQL com­pare shows the per­miss­ions that are associated with individual data­base objects. You can quickly filter objects in the data­base, or use the Find feature, to locate a particular object, for example, and then see the SQL differences relat­ing to per­miss­ions for that object in the two data­bases. You can then synchronize the per­miss­ions. For example, if a view "Expenses" in a development data­base has an additional per­miss­ion for a role called "Marketing", that extra per­miss­ion will be added to the view in the pro­duct­ion data­base when the schemas are synchronized.

SQL com­pare allows you to explore only a subset of the data­base. One can filter out particular object types, as well as narrow down the displayed objects to ones that have a particular word present in their names. So, for example, it is easy to explore only views and stored procedures that belong to the "HumanResources" schema, or have "Empl_" present in their names.

The follow­ing screenshot shows SQL com­pare with the AdventureWorks data­base. The objects have been filtered to only tables and views that contain "HumanR" in their full name:

Monitor­ing changes in principals and per­miss­ions

per­miss­ions in a data­base will change, either because privileges are granted or revoked, or due to malicious activity. Not all these changes may be detected immediately, and it can be a laborious, difficult task to identify them by scann­ing all the data­base principals. To help locate changes in authorization, you can use SQL com­pare to create a reference, or baseline, data­base, and then com­pare that data­base with the pro­duct­ion system at regular intervals. This will help you to quickly detect many security-related changes.

You can use SQL com­pare to save a data­base schema as a SQL com­pare snapshot. This file does not contain any table data, only the data­base schema - which contains information about users and roles and their per­miss­ions. If no changes are expected in the pro­duct­ion data­base, you can use this snapshot in a com­parison project to spot any inconsistencies, which may include places in the schema where security has been compromised.

SQL com­pare Pro can, in addition, com­pare live data­base with SQL creation scripts. This means that, if you have a set of scripts for a particular data­base, you can then com­pare the live data­base with its scripts. Since the scripts include authorization information for the data­base objects and the data­base principal definitions, you will immediately see any differences. If no SQL scripts exist for the data­base, SQL com­pare can generate them. The scripts can then be stored on some read-only media or in source control for future com­parisons.

Support­ing role-based security

It is good practice to assign per­miss­ions to roles rather than directly to users. People come and go in any organization, and this has to be reflected in the users who are authorized to access the data­base. Assign­ing per­miss­ions, perhaps hundreds of different per­miss­ions, to new users and then remov­ing those per­miss­ions when a user's access is revoked can be a tiresome task, and one that is prone to errors. Assign­ing the per­miss­ions to a role, and add­ing or remov­ing users from this role, is a much faster and safer way to manage data­base per­miss­ions. If you follow this security practice, it may be necessary to com­pare the roles that exist within two data­bases, and view any differences in per­miss­ions assigned to them.

SQL com­pare allows you to focus on role per­miss­ions and role memberships in other roles only. You can control this behaviour with the "Ignore users' per­miss­ions and role membership" option . This option will allow you to ignore any differences in user per­miss­ions, and thereby com­pare and synchronize only role per­miss­ions. It will com­pare the two roles based on their role members, and the per­miss­ions directly assigned to the role.

The follow­ing example shows a role, and what is considered when it is com­pared with another role. The full creation script for the role "role1" is shown on the left hand side. All the information displayed is considered for the purpose of the com­parison. However, if the "Ignore users' per­miss­ions, and role membership" option is set, the user membership is ignored, and the role is com­pared as if it has been created with the SQL on the right hand side.

Similarly, all user per­miss­ions are omitted, so for the table "invoices" in the next figure only the role per­miss­ions are considered. Without the "Ignore users' per­miss­ions, and role membership" option is set the per­miss­ions that are used for com­parison are on the left hand side, the per­miss­ions that are considered if the option is set are on the right hand side.


SQL com­pare can assist in many security-related tasks. It allows you to com­pare and synchronize data­base objects, includ­ing the data­base principals and their per­miss­ions. It can also help you create a baseline version of the data­base that can be used to monitor any changes to security in a pro­duct­ion data­base. SQL com­pare facilitates role-based security by identify­ing per­miss­ion inconsistencies at two levels of abstraction - individual principal per­miss­ions, and role per­miss­ions, ignor­ing users.


András would like to thank Brian Harris for providing the motivation to write this article and for his helpful comments and corrections on his first draft.

András Belokosztolszki

Author profile:

András Belokosztolszki is a software architect at Red Gate Software Ltd. He is a frequent speaker at many UK user groups and events (VBUG, NxtGen, Developer’s Group, SQLBits). He is primarily interested in database internals and database change management. At Red Gate he has designed and led the development of many database tools that compare database schemata and enable source control for databases (SQL Compare versions 4 to 7), refactor databases (SQL Refactor) and show the history of databases by analyzing the transaction log (SQL Log Rescue). András has a PhD from Cambridge and an MSc and BSc from ELTE, Hungary. He is also a MCSD and MCPD Enterprise. See my blogs on simple-talk.

Search for other articles by András Belokosztolszki

Rate this article:   Avg rating: from a total of 14 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: Orphaned users/passwords
Posted by: Matt H (view profile)
Posted on: Tuesday, August 7, 2007 at 1:36 PM
Message: Why not have SQLCompare synchronize users with the same method as sp_help_revlogin? (MS KB 246133)

That way the passwords could be copied over to be exactly the same, and so could the sids, preventing the orphaned user problem?

Subject: Re: Orphaned users/passwords
Posted by: András (view profile)
Posted on: Wednesday, August 8, 2007 at 4:56 AM
Message: Hi Matt,

SQL Compare concentrates more on the database side of the schema. Logins on the other hand are a bit more part of the whole SQL Server instance. So we had to draw a line between the two, and when it comes to logins, SQL Compare does the minimum to make the database synchronization script work. However your suggestion to preserve at least the passwords would improve the product even further, so we are certainly considering it in future releases. Many thanks,

Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Routine SQL DML Testing for the Unenthusiastic Tester

There are more exciting things in life than unit testing SQL Statements, checking the results, timings, and... Read more...

 View the blog

Top Rated

Jodie Beay and the Production Database Drift
 You make an example database, like NorthWind or WidgetDev in order to test out your deployment system... Read more...

SQL Server Data Tools (SSDT) and Database References
 SQL Server Data Tools (SSDT) provides, via the DacPac, interesting support for verifying not only... Read more...

Writing Build vNext tasks for Visual Studio Online
 Hosted TFS, now called Visual Studio Online (VSO), has a new way of writing build processes called... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.