Microsoft SQL Server is a popular relational database management system (RDBMS). However, determining the effective access rights of users is difficult because in Active Directory (AD) environments, effective access is determined based on not only the user’s direct permissions but also their membership in SQL Server roles, AD groups and Local Windows groups. (AD groups and Local Windows groups are relevant only if Windows Authentication or Mixed Authentication is enabled and those groups are granted a SQL Server login.)
This article explains more about effective access in SQL Server, offers core best practices for managing permissions, and offers a solution that can mitigate the risk of murky effective access permissions through effective auditing and alerting.
How SQL Effective Access is Determined
Core Concepts
Effective access in SQL Server involves the following:
- Securable — Securables are objects to which permissions can be assigned. They can be SQL Server instances, databases, or specific objects within a database, such as tables, views or stored procedures.
- Permission — Permissions are actions that can be taken on a securable. For example, the actions for a table include CREATE, DROP, MODIFY, INSERT, UPDATE, DELETE, SELECT and TRUNCATE. A principal can be granted permission to carry out an action on a securable. SQL Server offers more than 200 permissions that can be granted to a user.
- Principal — Principals are entities that can be granted or denied permissions to access SQL Server resources. Examples include SQL Server logins, SQL Server roles, Local Windows users, Local Windows groups, AD users and AD groups.
- Authorization — Authorization is the process of either assigning, denying or revoking a permission to a principal on a securable. It is done using GRANT, REVOKE or DENY statements.
Building SQL Statements to Control Access Rights
The key concepts are put together into SQL statements using the following pattern:
AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;
For example, to authorize (GRANT) the SELECT permission on the “Employees” table securable to the principal jsmith, we would use the following statement:
GRANT SELECT ON [dbo].[Employees] TO jsmith;
Permission Inheritance
In SQL Server, securables are arranged in a strict hierarchy: A server owns all the instances, an instance owns all the databases defined within the instance, and all the objects defined within a database are owned by that database.
Effective access depends upon this hierarchy because SQL Server access permissions can be inherited. For example, if a user (principal) is granted the sysadmin role (permission) at the instance (securable) level, that user will inherit all the permissions on the securable from the instance level down — their effective access.
To make things even more complicated, permissions on downstream objects can explicitly be denied. In this case, DENY will override GRANT, so the effective permission will be DENY. One notable exception to this rule is the sysadmin role: If a user is granted a sysadmin role, permissions are not checked further. Therefore, any denials on downstream objects will not be enforced.
Permissions can be inherited another way as well. Database users, which are known as logins, can be defined locally within SQL Server — but they can also be Local Windows users, Local Windows groups, AD users or AD groups. When an AD group or a Local Windows group is granted login access, all users in that group will inherit the associated permissions. SQL Server becomes aware of group membership when a user tries to log in.
Best Practices for Managing Permissions in SQL Server
Managing SQL Server permissions carefully can help prevent data breaches and unauthorized data access. The following best practices can help you maintain a secure SQL Server environment:
- Adhere to the principle of least privilege. Ensure that users and applications have only the permissions necessary to perform their tasks.
- Regularly review permissions and roles. Conduct regular audits of user roles and permissions to ensure they remain in alignment with security policies and compliance requirements.
- Track changes and activity. Proactively watch for security issues by using tools like SQL Server’s built-in auditing features to track and log access activity and permission changes.
- Use role-based access control (RBAC). Utilize roles to group permissions and assign them to users based on their job functions. This approach simplifies management and supports administrators in ensuring that permissions are consistently applied across various users and departments. Roles in SQL Server are similar to database roles in other RDBMS platforms. There are three types:
- Fixed server roles — SQL Server includes fixed (preconfigured) server roles, which cannot be modified or deleted. The only exception is the public fixed server role: Every login in SQL Server is granted the public role; the role cannot be revoked, but its default permissions can be modified (though that is not recommended).
- Fixed database roles — SQL Server also includes fixed database roles that cannot be modified or deleted. Again, the exception is the public fixed database role, which is granted to all logins and cannot be revoked but can be customized (which is not recommended).
- User-defined roles — These roles are database-level securables. Permissions on database-level objects are granted to a user-defined role. User-defined roles are a convenient way to group multiple permissions on multiple securables, which are then granted to logins.
- Avoid granting additional permissions to public roles. If you do, every login will inherit those permissions. Always consider whether a user-defined role is more appropriate.
- Avoid granting permissions at the database level. Whenever possible, avoid granting permissions directly to users at the database level. Instead, manage permissions through roles and group memberships, which can be more easily controlled and audited. This approach not only simplifies security management but also enhances database performance by reducing the overhead associated with maintaining numerous individual user permissions.
- Use scripts. To manage permissions efficiently and consistently, use the SQL Server Management Studio (SSMS) interface or employ T-SQL scripts and PowerShell queries. Establishing reproducible methods for performing tasks like setting permissions in various environments or restoring permissions to a known state after changes will reduce the risk of errors and simplify administration.
- Secure the activity of system administrators. Administrative accounts should be used only for actions that absolutely require elevated privileges. Having system administrators use separate accounts for other tasks reduces the risk of accidental changes that could open the door to security breaches.
- Use schemas. In SQL Server, a schema is a collection of database objects that are logically grouped together. Schemas are used to organize and separate database objects, such as tables, views, procedures, and functions, within a database. This approach can greatly simplify security management and reduce the complexity of your permissions architecture.
Connecting Microsoft Access to SQL Server using ODBC Drivers
Many organizations struggle with performance issues and data handling limitations when using Microsoft Access for large datasets. These challenges can lead to slow query processing and difficulties in managing and securing data effectively. By connecting Microsoft Access to SQL Server using ODBC drivers, these problems can be significantly reduced, providing a more efficient, scalable, and secure solution for data management.
Here’s how you can connect Microsoft Access to SQL Server using ODBC drivers securely:
- Download the latest ODBC driver for SQL Server from the Microsoft website and install it.
- Configure the ODBC data source:
- From the Control Panel, open the ODBC Data Source Administrator tool.
- Add a new data source by clicking Add and selecting the SQL Server ODBC driver from the list.
- Enter the required connection details, such as the server name and database name. You will also need to choose the authentication method; using Windows authentication can leverage existing security protocols while SQL Server authentication may require more stringent password policies.
- Test the connection to ensure that all configurations are correct and the SQL Server is reachable.
- Link the tables in Microsoft Access:
- Open Microsoft Access and either create a new database or open an existing project where you intend to use the SQL Server data.
- On the External Data tab, select the option to link to an external data source.
- Follow the wizard to select your newly created ODBC connection and choose the tables from the SQL Server that you want to link to your Access database.
- Implement security measures. Connecting databases can expose sensitive data, so ensure that access controls are in place to limit who can view or manipulate the data. In addition, monitor the data flows between Microsoft Access and SQL Server for suspicious activity.
How Can Netwrix Help
Since SQL Server does not manage or receive automatic updates about Local Windows or AD group memberships, it is hard to reliably calculate the effective permission on securables. Netwrix Enterprise Auditor for SQL simplifies this process. This robust auditing and reporting tool enumerates effective permissions for all login types and users, breaking them down by individual Local Windows or AD Group users and SQL Server roles on all securables. By streamlining permission management, you can enhance security and ensure compliance with ease.
Furthermore, Netwrix Enterprise Auditor for Databases automates the discovery and management of all SQL Server instances and sensitive data. It provides comprehensive visibility into access permissions, monitors activities for suspicious behavior, performs risk assessments based on industry benchmarks like CIS and more. Detailed audit trails and pre-built compliance reports help you ensure your SQL databases remain secure, compliant, and well-managed. Discover more at netwrix.com/enterprise-auditor.
