IDERA SQL Secure allows you to view the permission settings of individual users, roles, and objects, at a particular point in time, for each SQL Server instance that has been added to SQL Secure for auditing.
The Explore Permissions view allows you to review the following security information:
- Enterprise level permissions
- SQL Server level permissions
- Individual user permissions
Assigned permissions are permissions that are explicitly granted or denied to a user, group, or role for a particular server or database object. A user, group, or role can have more than one assigned permission. Effective permissions are the net effect of assigned permissions, permissions inherited from the group or role membership, and covering permissions (SQL Server 2005 and later).
Analyzing permissions
It is important to understand that when analyzing a user's permissions, SQL Secure shows multiple permissions when users have inherited object permissions from a parent role on the server. For example; User A has been given explicit delete permissions at the server, database, schema, and table levels. Your company is now restricting the rights to a particular table and you need to revoke User A's right to delete. To accomplish this task, revoke the user's right to delete at the particular table level and also at the parent levels.
The following illustration depicts an example permissions scenario:
Experiencing irregularities when searching user and object permissions
There may be times when it seems as though the permissions for a user or table have changed drastically when no changes have actually occurred. The following table lists some of the possible causes:
| Cause | Solution |
|---|---|
| A user or table is deleted and then the same name is used again in the future. | Make sure that best practices are used when adding and deleting user and table names, or properly note the change to avoid confusion. |
| A user or table name is changed | Properly note the change to avoid confusion. |
| A user or table is deleted from the system | Properly note the change to avoid confusion. |
SQL Secure allows you to audit all users and object permissions on SQL Server instances that have been registered with SQL Secure. For more information about SQL Server permissions, see Microsoft Books Online.