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 components of the security system are part of the database schema, SQL compare can help with certain security administration tasks even though it is not specifically designed to do so.
SQL compare can help in the following ways:
- Reviewing and copying database principals (users and roles). The database users can be reviewed, and their definitions inspected using SQL compare. You can view database and object level permissions. It will quickly show you if a particular user is a windows user or whether it is a user that is associated with an asymmetric key. SQL compare can also assist in copying database users and logins between servers.
- Administering permissions on database Objects. You can explore individual database objects and see their object level permissions. SQL compare can be used to copy these permissions between databases.
- Monitoring changes in principals and permissions. A copy of a database can be created at a point in time and subsequently compared with the current database. SQL compare can compare a live database with another live database, a SQL compare schema snapshot, or a set of SQL creation scripts. This is useful in identifying changes to the database principals and to their permissions since the creation or synchronisation of the reference database.
- Supporting role-based security. Role-based security assigns permissions to roles instead of users. If a user needs to be granted a permission, rather than being assigned the permission directly, it is made a member of the relevant database role. SQL compare provides an option to facilitate exploring the differences in such databases.
Reviewing and copying database principals
SQL compare displays the definition of each database principal, and shows all the database and object level permissions assigned to the user or role:
SQL compare can help you migrate these database users to a different database. If the SQL Server login does not exist on the target database, SQL compare will create it and will set its password a default value - "p@ssw0rd". This allows the synchronization of the schemas to be completed, but it is certainly 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 database with names that match the users in the production database, but which are of different types. In a production database users are likely to be based on Windows and SQL Server logins, or on certificates and asymmetric keys. In a development database, it is often sufficient to create users using "WITHOUT LOGIN". This can cause problems when comparing the database users and their properties between production and development.
In SQL compare, the "Ignore User Properties" option is available to help you with this situation. When comparing databases, select this option to specify that users should be compared only on their names rather than all of their properties. This still allows you to identify users that exist in only one of the databases, or that are present in both of the databases, but have different permissions or extended properties assigned to them. You can also use the "Ignore extended properties" option to force SQL compare to omit extended properties, and, in this way, to concentrate solely on the differences in permissions.
Sometimes, a database may contain 'orphaned' users, who do not have an associated security identifier (SID) in the master database. This may happen when a database is restored from backup on a different server. In this situation, SQL compare displays a warning to help you identify the problem prior to synchronization.
Users that are based on certificates or asymmetric keys are more difficult to compare, as these objects cannot be written as SQL statements without accessing the file system on the server. For objects that use certificates and asymmetric keys, SQL compare provides warnings, to help you resolve any migration issues manually. You can choose to ignore certificates, symmetric and asymmetric keys when comparing schemas.
Administering permissions on database objects
By default, SQL compare shows the permissions that are associated with individual database objects. You can quickly filter objects in the database, or use the Find feature, to locate a particular object, for example, and then see the SQL differences relating to permissions for that object in the two databases. You can then synchronize the permissions. For example, if a view "Expenses" in a development database has an additional permission for a role called "Marketing", that extra permission will be added to the view in the production database when the schemas are synchronized.
SQL compare allows you to explore only a subset of the database. 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 following screenshot shows SQL compare with the AdventureWorks database. The objects have been filtered to only tables and views that contain "HumanR" in their full name:
Monitoring changes in principals and permissions
permissions in a database 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 scanning all the database principals. To help locate changes in authorization, you can use SQL compare to create a reference, or baseline, database, and then compare that database with the production system at regular intervals. This will help you to quickly detect many security-related changes.
You can use SQL compare to save a database schema as a SQL compare snapshot. This file does not contain any table data, only the database schema - which contains information about users and roles and their permissions. If no changes are expected in the production database, you can use this snapshot in a comparison project to spot any inconsistencies, which may include places in the schema where security has been compromised.
SQL compare Pro can, in addition, compare live database with SQL creation scripts. This means that, if you have a set of scripts for a particular database, you can then compare the live database with its scripts. Since the scripts include authorization information for the database objects and the database principal definitions, you will immediately see any differences. If no SQL scripts exist for the database, SQL compare can generate them. The scripts can then be stored on some read-only media or in source control for future comparisons.
Supporting role-based security
It is good practice to assign permissions 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 database. Assigning permissions, perhaps hundreds of different permissions, to new users and then removing those permissions when a user's access is revoked can be a tiresome task, and one that is prone to errors. Assigning the permissions to a role, and adding or removing users from this role, is a much faster and safer way to manage database permissions. If you follow this security practice, it may be necessary to compare the roles that exist within two databases, and view any differences in permissions assigned to them.
SQL compare allows you to focus on role permissions and role memberships in other roles only. You can control this behaviour with the "Ignore users' permissions and role membership" option . This option will allow you to ignore any differences in user permissions, and thereby compare and synchronize only role permissions. It will compare the two roles based on their role members, and the permissions directly assigned to the role.
The following example shows a role, and what is considered when it is compared 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 comparison. However, if the "Ignore users' permissions, and role membership" option is set, the user membership is ignored, and the role is compared as if it has been created with the SQL on the right hand side.
Similarly, all user permissions are omitted, so for the table "invoices" in the next figure only the role permissions are considered. Without the "Ignore users' permissions, and role membership" option is set the permissions that are used for comparison are on the left hand side, the permissions that are considered if the option is set are on the right hand side.
SQL compare can assist in many security-related tasks. It allows you to compare and synchronize database objects, including the database principals and their permissions. It can also help you create a baseline version of the database that can be used to monitor any changes to security in a production database. SQL compare facilitates role-based security by identifying permission inconsistencies at two levels of abstraction - individual principal permissions, and role permissions, ignoring 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.