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

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.

Summary

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.

Acknowledgements

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.