SQL Server Security & Permissions
7 questions found
What is the difference between a SQL Server login and a database user?
Beginner
A login is created at the server level and controls whether someone can connect to the SQL Server instance at all, while a database user exists within a specific database and is mapped to a login, controlling what that person is actually allowed to do once connected to that particular database.
CREATE LOGIN SalesUser WITH PASSWORD = 'SecurePassword123!';
CREATE USER SalesUser FOR LOGIN SalesUser;
Real-world example
A company creates a login for a new employee allowing them to connect to the SQL Server instance, and then creates a corresponding database user in the sales database specifically, granting them access to just that one database.
Common follow-ups: Can a single login be mapped to users in multiple different databases?;What happens if a login exists but has no corresponding database user in a specific database?
Dynamic Data Masking;SQL Server Architecture & Editions
What is the difference between Windows Authentication and SQL Server Authentication?
Beginner
Windows Authentication uses the user's existing Windows domain credentials to connect to SQL Server, relying on the security already managed by the organization's Windows environment, while SQL Server Authentication uses a separate username and password stored and managed directly within SQL Server itself, which is useful for applications or users outside the Windows domain.
-- Windows Authentication login
CREATE LOGIN [DOMAIN\username] FROM WINDOWS;
-- SQL Server Authentication login
CREATE LOGIN SqlUser WITH PASSWORD = 'SecurePassword123!';
Real-world example
A company primarily uses Windows Authentication for its internal staff since they are already part of the corporate domain, while using SQL Server Authentication for a specific external application that operates outside their Windows environment.
Common follow-ups: Which authentication method is generally considered more secure?;Can a SQL Server instance support both authentication methods at the same time?
Data Types & Schema Design;SQL Server Architecture & Editions
How do database roles help simplify managing permissions for multiple users with similar access needs?
Intermediate
A database role lets you group together a specific set of permissions once, and then simply add or remove individual users from that role to grant or revoke that entire set of permissions, rather than needing to manage granular permissions separately for every individual user, making permission management much more consistent and easier to audit.
CREATE ROLE ReportViewers;
GRANT SELECT ON SCHEMA::Reporting TO ReportViewers;
ALTER ROLE ReportViewers ADD MEMBER SalesUser;
Real-world example
A company creates a ReportViewers role granting read access to their reporting schema, then simply adds each new employee who needs reporting access to that role, rather than manually granting the same set of permissions to each person individually.
Common follow-ups: What is the difference between a fixed database role and a custom database role?;How do you see which users currently belong to a specific role?
Views;Row Level Security
What is the principle of least privilege, and how should it guide how you grant permissions in SQL Server?
Intermediate
The principle of least privilege means giving a user or application only the specific permissions they actually need to perform their job, and nothing more, reducing the potential damage that could occur from a mistake, a compromised account, or a security vulnerability, since an account with limited permissions can only cause limited harm even in the worst case.
-- Grant only what is specifically needed
GRANT SELECT ON Orders TO ReportingApp;
-- Avoid granting broad permissions like db_owner unless truly necessary
Real-world example
A company grants their reporting application account only SELECT permission on the specific tables it actually needs, rather than the much broader db_owner role, significantly limiting potential damage if that application's credentials were ever compromised.
Common follow-ups: How do you audit whether existing accounts have more permissions than they actually need?;What is a reasonable process for regularly reviewing and tightening permissions over time?
Dynamic Data Masking;Backup & Recovery
How do you implement row level security to restrict which specific rows a user can see within a shared table, beyond simply controlling access to entire tables or columns?
Advanced
You create a security policy using an inline table valued function that defines the logic for which rows a given user is allowed to access, then apply that policy to the relevant table using CREATE SECURITY POLICY, so that even though every user queries the same shared table, SQL Server automatically filters the results based on the defined access rules for each individual user.
CREATE FUNCTION SecurityPredicate(@SalesRegion VARCHAR(50))
RETURNS TABLE
AS RETURN SELECT 1 AS Result
WHERE @SalesRegion = USER_NAME() OR IS_MEMBER('db_owner') = 1;
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE dbo.SecurityPredicate(SalesRegion) ON dbo.Orders;
Real-world example
A multi region sales company implements row level security so that each regional sales representative querying the shared orders table automatically only sees orders belonging to their own specific region, without needing separate tables or views per region.
Common follow-ups: What is the performance impact of applying row level security to a heavily queried table?;How do database administrators typically bypass row level security when they need full visibility?
Views;Query Optimization & Plans
How would you design a comprehensive security auditing strategy to track who accessed or modified sensitive data in a SQL Server database?
Advanced
You would enable SQL Server Audit to capture specific security relevant events, such as successful and failed login attempts and access to particularly sensitive tables, direct that audit data to a secure location like a file or the Windows Event Log, and establish a regular review process to examine the captured audit data for suspicious patterns or compliance verification purposes.
CREATE SERVER AUDIT SecurityAudit
TO FILE (FILEPATH = 'C:\Audits\');
CREATE DATABASE AUDIT SPECIFICATION SensitiveDataAudit
FOR SERVER AUDIT SecurityAudit
ADD (SELECT ON dbo.Customers BY public);
ALTER SERVER AUDIT SecurityAudit WITH (STATE = ON);
Real-world example
A financial services company sets up comprehensive SQL Server Audit tracking on their customer data tables, satisfying regulatory compliance requirements by maintaining a detailed, tamper evident record of exactly who accessed sensitive customer information and when.
Common follow-ups: What compliance regulations commonly require this kind of detailed database auditing?;How do you ensure audit logs themselves cannot be tampered with by someone trying to hide their activity?
Dynamic Data Masking;Transparent Data Encryption
How do you grant and revoke specific permissions, such as SELECT or EXECUTE, on individual database objects rather than through broader roles?
Intermediate
You use the GRANT statement to give a specific permission on a specific object to a user or role, and the REVOKE statement to remove a previously granted permission, giving you fine grained control when you need to allow or restrict access to just one particular table, view, or stored procedure rather than an entire schema or database.
GRANT SELECT ON dbo.Orders TO SalesUser;
GRANT EXECUTE ON dbo.GetOrderSummary TO SalesUser;
REVOKE SELECT ON dbo.Orders FROM SalesUser;
Real-world example
A support team member is granted permission to execute a specific reporting stored procedure without being given broader access to directly query the underlying sensitive tables that procedure relies on internally.
Common follow-ups: What is the difference between REVOKE and DENY in SQL Server?;Can permissions granted directly to a user conflict with permissions granted through a role they belong to?
Views;Stored Procedures & Functions