{"id":97969,"date":"2023-09-03T20:00:45","date_gmt":"2023-09-03T20:00:45","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97969"},"modified":"2026-04-17T12:33:18","modified_gmt":"2026-04-17T12:33:18","slug":"sql-server-rls-setup","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-server-rls-setup\/","title":{"rendered":"SQL Server Row Level Security: Setup, Access Predicates, and Examples"},"content":{"rendered":"\n

SQL Server Row Level Security (RLS) restricts which rows a user can see or modify in a table, without any changes to the application layer. Implementation requires two database objects per table: an access predicate (an inline table-valued function that defines the row filter logic) and a security policy (which binds the access predicate to the table and column being filtered). <\/strong><\/p>\n\n\n\n

Part 2 of this series covers the complete setup: defining the strategy, writing access predicates, creating security policies, and choosing an authorization method. Six authorization methods are covered – SESSION_CONTEXT, Active Directory group membership, SQL role membership, user name lookup, local lookup tables, and staging table patterns – with working T-SQL examples using the WideWorldImporters sample database.<\/strong><\/p>\n\n\n

This is part of a series on Row Level Security by Ben Johnston<\/a>. For the rest of the series, click here<\/a><\/strong><\/p>\n\n\n\n\n

The previous section in this series was an introduction to Row Level Security (RLS) and some use cases. This section focuses on basic setup of RLS, methods for implementing RLS and performance considerations with those implementations. The RLS access predicate is applied to every row returned to a client making performance a big factor in any design. Expensive lookups or other bad designs can hurt not only performance on that table but overall system performance if CPU or IO is heavily impacted.<\/p>\n\n\n\n

Basics<\/h2>\n\n\n\n

Define the strategy based on business requirements<\/h3>\n\n\n\n

Implementing RLS is as much a business endeavour as technical. Without a business case to implement RLS, there is no reason for the extra effort and testing involved. This is where driving out business requirements and making sure the solution fits the problem is important. Non-technical members of the team or business partners likely won\u2019t know what RLS is or if it should be used. Differential access to the same data, replacing systems (or proposed systems) with multiple reports based on user groups, and multi-tenant access are possible indicators that RLS may be a useful tool. There are always multiple ways to solve a problem. If RLS would simplify the design and make it more robust, that\u2019s when I start to seriously consider it for a design. It does help if the business is aware of RLS and have used it in other projects or databases, but having the business essentially design the system is dangerous too. Use all of the information available during planning sessions and design the system that best fits the need of the business and the skills of the technical team.<\/p>\n\n\n\n

The business requirements should include a method to determine the rows allowed based on groups of users. It can be a very broad definition, such as country or business division assigned to a user, or it can much more specific and elaborate. Whatever method is decided, there should be an automated method to enable that strategy in the database for each user. The method can be active directory groups or properties, SQL roles, application database tables, a front-end designed and coded for this purpose (in conjunction with configuration tables), or any other method determined by the business. The strategy needs to be translated to something accessible to the database engine. Several methods are listed in the official documentation and are discussed below, but the actual authorization groups used by the RLS access predicate will likely be stored in the SESSION_CONTEXT, a lookup table, or a SQL role.<\/p>\n\n\n\n

An access predicate is defined for each table<\/h3>\n\n\n\n

The actual implementation of RLS happens with two database objects, an access predicate and a security policy. The access predicate is an inline table-valued function (TVF) and defines the authorization and lookup process. This table-valued function is applied to each row that would be returned to the user. It is evaluated and only allows the row to return if the defined criteria are met. This sounds like a very general description, but that\u2019s because it is very general. The rows returned are based on the business requirements defined for your project. Each RLS implementation is a custom solution and requires the rules to be implemented in the access predicate.<\/p>\n\n\n\n

The access predicate defines the security mechanism for each row. Lookup queries and joins can be used, but they have a performance cost. The column used for security is passed in as a parameter and must be available in the table or the system (i.e., @CountryCode). Rows are then filtered (for read operations) or blocked (for write operations), or both. The filtering process is defined by the security policy.<\/p>\n\n\n\n

A security policy is defined for each table<\/h3>\n\n\n\n

The security policy is what ties the access predicate to the table, specifies the column to be used as the parameter in the predicate, indicates how data should be restricted (filter, block, or both), and if RLS is enabled for the table or not via the STATE value. It is the mechanism that enables or disables RLS at a table level. Remember that RLS is table specific and only applies to tables where it is applied. That\u2019s the job of the security policy. Each table can have a maximum of 1 security policy assigned. It can be enabled for both options, filter and block, but only one policy can be on a table.<\/p>\n\n\n\n

Examples<\/h3>\n\n\n\n

The following examples use the WideWorldImporters sample database. They show basic access predicates and security policies.<\/p>\n\n\n\n

Example 1 \u2013 lookup table<\/h4>\n\n\n\n

The following example shows a lookup table getting used to perform the authorization process. This example uses a local table in a separate schema, RLS. This is more secure from a procedural standpoint since you can be sure that regular users don\u2019t have access to the table and aren\u2019t able to modify it as long as you don\u2019t use db_datareader or db_datawriter.<\/p>\n\n\n\n

CREATE SCHEMA RLS\nAUTHORIZATION dbo\nGO\n--Create the table to hold the RLS rules\nCREATE TABLE RLS.UsersSuppliers (\n\tUsersSuppliersID\t\tint\t\t\t\tNOT NULL CONSTRAINT PK_RLSUsersSuppliers PRIMARY KEY CLUSTERED\t\tidentity\n\t,UserID\t\t\t\t\tnvarchar(255)\tNOT NULL\n\t,SupplierID\t\t\t\tint\t\t\t\tNOT NULL\n)\nGO\nCREATE UNIQUE NONCLUSTERED INDEX UNQ_RLSUsersSuppliers_NaturalKey\nON RLS.UsersSuppliers (\n\tUserID\n\t,SupplierID\n)\nGO\n--Test user without a login\nCREATE USER RLSLookupUser WITHOUT LOGIN\nGO\n--Grant SELECT access to the entire Purchasing schema\n--Deny SELECT on the RLS schema\nGRANT SELECT ON SCHEMA::Purchasing TO RLSLookupUser\nDENY SELECT ON SCHEMA::RLS TO RLSLookupUser\nGO\n--Grant the test user access to a single supplier ID\nINSERT INTO RLS.UsersSuppliers (\n\tUserID\n\t,SupplierID\n)\nVALUES (\n\t'RLSLookupUser'\n\t,4\n)\nGO\n--Simple access predicate with a lookup\nCREATE FUNCTION RLS.AccessPredicate_SupplierID_PurchasingSuppliers(@SupplierID\tint)\nRETURNS TABLE\nWITH SCHEMABINDING\nAS\nRETURN\n\tSELECT \n\t\t1 AccessResult\n\tFROM RLS.UsersSuppliers US\n\t\tINNER JOIN Purchasing.Suppliers PS\n\t\t\tON US.SupplierID\t= PS.SupplierID\n\tWHERE US.SupplierID\t\t\t= @SupplierID\n\t\tAND US.UserID\t\t\t= USER_NAME()\nGO\n--Enforce the access predicate defined above\n--Note that the STATE = ON\nCREATE SECURITY POLICY RLS.SecurityPolicy_SupplierID_PurchasingSuppliers\nADD FILTER PREDICATE RLS.AccessPredicate_SupplierID_PurchasingSuppliers(SupplierID) ON Purchasing.Suppliers\n,ADD BLOCK PREDICATE RLS.AccessPredicate_SupplierID_PurchasingSuppliers(SupplierID) ON Purchasing.Suppliers AFTER UPDATE\nWITH (STATE = ON, SCHEMABINDING = ON)\nGO<\/pre>\n\n\n\n
Be sure to validate your RLS configuration. The test user above, RLSLookupUser, is shown doing this below. Note that it was executed in the context of a dbo account and no rows are returned.<\/p>\n\n\n\n
--Show the current user name\nSELECT USER_NAME()\n--Rows returned with RLS applied\nSELECT *\nFROM Purchasing.Suppliers<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
--Change the user running the SELECT\nEXECUTE AS USER = 'RLSLookupUser'\nSELECT *\nFROM Purchasing.Suppliers\n--Revert back to the logged in user\nREVERT<\/pre>\n\n\n\n
Notice that the SupplierID matches the ID in the script populating the RLS.UsersSuppliers table, 4.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Example 2 \u2013 lookup table with dbo override<\/h4>\n\n\n\n
This example differs in that it checks for the db_owner role. If found, all rows are returned. This is useful if it fits your use case. It makes it easier for DBAs to perform maintenance on the RLS tables and run ad-hoc statements to fix issues.<\/p>\n\n\n\n
--Drop the security policy so the old access predicate has no references\nDROP SECURITY POLICY RLS.SecurityPolicy_SupplierID_PurchasingSuppliers\n--WITH (SCHEMABINDING=OFF)\nGO\n--Drop the old access predicate so the new one can be created\nDROP FUNCTION  RLS.AccessPredicate_SupplierID_PurchasingSuppliers\nGO\nCREATE FUNCTION RLS.AccessPredicate_SupplierID_PurchasingSuppliers(@SupplierID\tint)\nRETURNS TABLE\nWITH SCHEMABINDING\nAS\nRETURN\n\tSELECT 1 AccessResult\n\tWHERE IS_MEMBER('db_owner') = 1 \n\tUNION\n\tSELECT \n\t\t1 AccessResult\n\tFROM RLS.UsersSuppliers US\n\t\tINNER JOIN Purchasing.Suppliers PS\n\t\t\tON US.SupplierID\t= PS.SupplierID\n\tWHERE US.SupplierID\t\t\t= @SupplierID\n\t\tAND US.UserID\t\t\t= USER_NAME()\nGO\n--Enforce the access predicate defined above\n--The same security policy is used\nCREATE SECURITY POLICY RLS.SecurityPolicy_SupplierID_PurchasingSuppliers\nADD FILTER PREDICATE RLS.AccessPredicate_SupplierID_PurchasingSuppliers(SupplierID) ON Purchasing.Suppliers\n,ADD BLOCK PREDICATE RLS.AccessPredicate_SupplierID_PurchasingSuppliers(SupplierID) ON Purchasing.Suppliers AFTER UPDATE\nWITH (STATE = ON, SCHEMABINDING = ON)\nGO<\/pre>\n\n\n\n
Note that a UNION is used rather than an OR in the WHERE clause. This is a general tuning guideline and usually works better for all queries, not just RLS access predicates. Notice how running the SELECT as a dbo returns all rows, but the first user is still restricted.<\/p>\n\n\n\n
--Show the current user name\nSELECT USER_NAME()\n--Rows returned with RLS applied\nSELECT *\nFROM Purchasing.Suppliers\n--Change the user running the SELECT\nEXECUTE AS USER = 'RLSLookupUser'\nSELECT USER_NAME()\nSELECT *\nFROM Purchasing.Suppliers\n--Revert back to the logged in user\nREVERT<\/pre>\n\n\n\n
In contrast to the first example, all rows are returned for the dbo account.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Example 3 \u2013 Role member lookup<\/h4>\n\n\n\n
The next example is similar to the method used in the base implementation of the WideWorldImporters sample database. It has a dbo override and also checks for role membership.<\/p>\n\n\n\n
--Create a role for each customer type\nCREATE ROLE [CustomerType_Agent]\nCREATE ROLE [CustomerType_Wholesaler]\nCREATE ROLE [CustomerType_Novelty Shop]\nCREATE ROLE [CustomerType_Supermarket]\nCREATE ROLE [CustomerType_Computer Store]\nCREATE ROLE [CustomerType_Gift Store]\nCREATE ROLE [CustomerType_Corporate]\nCREATE ROLE [CustomerType_General Retailer]\n--The Gift Store type has associated records so it is used for this example\n--All other types would have an associated user or you could\n--assign a group or specific users to the various roles\nCREATE USER RLSGiftStore WITHOUT LOGIN\nGO\nALTER ROLE [CustomerType_Gift Store] ADD MEMBER RLSGiftStore\nGO\nALTER ROLE db_datareader ADD MEMBER RLSGiftStore\nGO\n--Add the access predicate. \n--This shows two things\n--1. The Lookup based on role member using the IS_ROLEMEMBER function\n--2. The JOIN to the CustomerCategories table. This is not a direct lookup for the base RLS table\nCREATE FUNCTION RLS.AccessPredicate_CustomerID_SalesCustomers(@CustomerID\tint)\nRETURNS TABLE\nWITH SCHEMABINDING\nAS\nRETURN\n\tSELECT 1 AccessResult\n\tWHERE IS_MEMBER('db_owner') = 1 \n\tUNION\n\tSELECT ISNULL(AccessResult,0)\n\tFROM (\n\t\t\tSELECT \n\t\t1 AS AccessResult\n\tFROM Sales.Customers C\n\t\tINNER JOIN Sales.CustomerCategories CC\n\t\t\tON C.CustomerCategoryID\t\t= CC.CustomerCategoryID\n\tWHERE C.CustomerID\t\t\t\t\t= @CustomerID\n\t\tAND IS_ROLEMEMBER(N'CustomerType_' + CC.CustomerCategoryName,USER_NAME())\t\t= 1) AccessPredicate\nGO\n--Add the security policy\n--This is straight forward, but also note that it uses the CustomerID\n--and not the CustomerCategoryName. The name is a lookup in CustomerCategories\nCREATE SECURITY POLICY RLS.SecurityPolicy_CustomerID_SalesCustomers\nADD FILTER PREDICATE RLS.AccessPredicate_CustomerID_SalesCustomers(CustomerID) ON Sales.Customers\n,ADD BLOCK PREDICATE RLS.AccessPredicate_CustomerID_SalesCustomers(CustomerID) ON Sales.Customers AFTER UPDATE\nWITH (STATE = ON, SCHEMABINDING = ON)\nGO<\/pre>\n\n\n\n
This only returns 50 rows out of the 663, which is correct based on RLS rules.<\/p>\n\n\n\n
EXECUTE AS USER = 'RLSGiftStore'\nSELECT *\nFROM Sales.Customers\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
As you can see, implementing RLS is relatively simple once you understand how everything is connected. Keeping it simple is important for maintenance and performance.<\/p>\n\n\n\n
Authorization methods in RLS<\/h2>\n\n\n\n
The authorization method for RLS involves storing user information in a table or using another lookup strategy. The examples above show a lookup table and role membership for granting access, but any lookup can be used. This needs to defined after the business rules are determined and it is directly tied to that analysis. This storage or lookup strategy will determine how the access predicate functions. Complex methods will likely benefit with being stored in a table. This is also useful when users are allowed to directly query data themselves. When all access is managed through an application, SESSION_CONTEXT is a viable solution. It all depends on the business need, the current technical solutions in place, and the method that will be used to access the RLS protected data.<\/p>\n\n\n\n
CONTEXT_INFO()<\/h3>\n\n\n\n
It is possible to use CONTEXT_INFO for RLS, but this is definitely not recommended. It is a clear security risk since any user can override their own information. It is also not possible to lock these values when they are set. In simple terms, it means a user can change or set RLS security if it uses CONTEXT_INFO. Do not use CONTEXT_INFO for RLS.<\/p>\n\n\n\n
SESSION_CONTEXT()<\/h3>\n\n\n\n
SESSION_CONTEXT can be used for RLS and it is a secure solution when implemented correctly. SESSION_CONTEXT works with all versions of SQL, including Azure SQL Database and with Microsoft Fabric. The key to making SESSION_CONTEXT secure is setting the value to read only when it is created.<\/p>\n\n\n\n
The following has a potential security gap:<\/p>\n\n\n\n
EXEC sp_set_session_context 'RLSRegion', 'Central'<\/pre>\n\n\n\n
That gap is closed by using the syntax below. The last parameter, 1, is for the @read_only parameter:<\/p>\n\n\n\n
EXEC sp_set_session_context 'RLSRegion', 'Central', 1<\/pre>\n\n\n\n
If a user tries to override the value after it has been set, they will get the following error. If the user opens a new session, SESSION_CONTEXT is not set, meaning a user could set it to any value.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The other crucial part is ensuring that the SESSION_CONTEXT is set for each user. If access is only allowed through an application, it is relatively easy to be sure it is set. If the SESSION_CONTEXT is not set, the user won\u2019t have access. This is also a security gap if the user can run commands since they will be able to set it themselves to any value.<\/p>\n\n\n\n
A logon trigger can be used for full instances of SQL Server, on-prem and managed instances in Azure. If implemented, it needs to be very lightweight without a complicated lookup. This will be run for every connection to the server, so it may not work well for servers supporting multiple applications and databases. Thorough testing is recommended.<\/p>\n\n\n\n
Local lookup table \/ Synapse lookup table<\/h3>\n\n\n\n
Storing authorization groups in a table works well for very complex authorization groups, such as multiple tiers of access (i.e., city, state, country, region). Complex RLS schemes don\u2019t work well during the query process unless they are simplified. In my example of city, state, country, region, it works better to store each city allowed by the user, even if they are assigned access at a higher level (i.e., country). This might result in thousands of rows for a single user in the RLS table, but a very fast lookup process and it eliminates multiple joins in the access predicate. This assumes proper indexes on both the tables protected by RLS and the authorization table. Even with this general recommendation, it is always good to test your specific scenario. It is always possible to take things to an extreme level that doesn\u2019t work well. If it is difficult or not possible to expand a hierarchy, care must be taken in the access predicate to ensure performance is acceptable. Normal query performance guidelines should be followed. It is usually better to include a UNION statement, or multiple UNION statements, rather than using OR statements in a JOIN.<\/p>\n\n\n\n
Storing the authorization groups in a table also works well when users are allowed to query data directly or when multiple tools or applications are allowed to hit a warehouse and it isn\u2019t feasible to create separate views and stored procedures for each group. In other words, ad-hoc queries from the perspective of the query engine. It is much more difficult to force the SESSION_CONTEXT to be set for every connection in an ad-hoc scenario. Setting the SESSION_CONTEXT can be done with SQL Server or in an Azure managed instance with a logon trigger, but this is not supported in Azure SQL Database. If that is your environment or in your upgrade path, it isn\u2019t an option and you will want to look at a table-based solution instead.<\/p>\n\n\n\n
This will be revisited during the section dedicated to security mitigations, but there are a few things to keep in mind if storing RLS authorization groups in tables. Put the lookup table and any tables used to support the lookup table in a separate scheme inaccessible to regular users (the RLS schema in my examples above). Update rights should be very limited. This also means that built-in database roles shouldn\u2019t be used for regular users either, including the usual db_datareader and db_datawriter.<\/p>\n\n\n\n
SQL Roles<\/h3>\n\n\n\n
SQL Roles can be used as a lookup mechanism in access predicates. Simple lookups would work well with this pattern. Since active directory groups can also be members of roles, that makes a good pattern and is easy to maintain. The sample in WideWorldImporters uses this as one of several methods for lookups.<\/p>\n\n\n\n
SELECT 1 AS AccessResult\n        WHERE IS_ROLEMEMBER(N'db_owner') <> 0<\/pre>\n\n\n\n
Specific users<\/h3>\n\n\n\n
Specific users can also be targeted by access predicates. This may be a good method to ensure that your administrator account always has access. It can also be used in conjunction with other lookups. This method is also demonstrated in the WideWorldImporters sample.<\/p>\n\n\n\n
ORIGINAL_LOGIN() = N'Website'<\/pre>\n\n\n\n
Staging tables<\/h3>\n\n\n\n
A common pattern for warehouses is to create staging tables where raw or partially processed data can be placed before it is fully curated and made generally available to end users. If this pattern is used, be sure to put the staging tables in a different database or separate schema. This segregated space should not be accessible by regular users, even for SELECT access. Consider using the DENY permission on the ETL schema in addition to not granting permission to the schema if the tables are in the same database as the processed tables. In large organizations it is easy for a security request to be granted inadvertently if it follows patterns for standard databases but shouldn\u2019t be followed in your RLS enabled database.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
Creating and setting up RLS is relatively easy once the business rules are clearly understood. It may be necessary to create and regularly populate configuration tables for this process or add other structures to the database, such as user roles. A single access point and security predicate is then added to a table for RLS to be enabled. There are a number of methods to perform the lookup, including local tables, SESSION_CONTEXT() or any other method that meets the business need. Keep security and performance in mind at each stage of design. Each method implementing RLS has potential security holes and potential performance issues. Testing and validation of each method is key to any RLS setup.<\/p>\n\n\n\n
RLS section Links:<\/h2>\n\n\n\n
Part 1 \u2013 Introduction and Use Cases<\/a><\/p>\n\n\n\n
Part 2 \u2013 Setup and Examples<\/p>\n\n\n\n
Part 3 \u2013 Performance and Troubleshooting<\/a><\/p>\n\n\n\n
Part 4 \u2013 Integration, Antipatterns, and Alternatives to RLS<\/a><\/p>\n\n\n\n
Part 5 \u2013 RLS Attacks<\/a><\/p>\n\n\n\n
Part 6 \u2013 Mitigations to RLS Attacks<\/a><\/p>\n\n\n\n
Reference<\/h2>\n\n\n\n
\nhttps:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/session-context-transact-sql?view=sql-server-ver16\n<\/div><\/figure>\n\n\n\n
\nhttps:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/triggers\/logon-triggers?view=sql-server-ver16\n<\/div><\/figure>\n\n\n\n
\nhttps:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-set-session-context-transact-sql?view=sql-server-ver16\n<\/div><\/figure>\n\n\n\n
\nhttps:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/row-level-security?view=sql-server-ver16\n<\/div><\/figure>\n\n\n\n
\n    
FAQs: SQL Server Row Level Security Deep Dive. Part 2 \u2013 Setup and Examples<\/h2>\n\n                        
1. How do I set up Row Level Security in SQL Server?<\/h3>\n            
\n                
Two steps: (1) Create an access predicate – an inline table-valued function (iTVF) that takes the filter column value as a parameter and returns a table with rows matching the current user’s authorization. The function typically queries a lookup table or uses SESSION_CONTEXT to determine what data the current user is authorised to see. (2) Create a security policy – using CREATE SECURITY POLICY, bind the access predicate to the table specifying the column that will be passed as the parameter. The security policy can be set as FILTER (for SELECT restrictions) or BLOCK (for INSERT\/UPDATE\/DELETE restrictions).<\/p>\n            <\/div>\n                    
2. What is an access predicate in SQL Server Row Level Security?<\/h3>\n            
\n                
An access predicate is an inline table-valued function (iTVF) that implements the row filter logic for RLS. It accepts the value of the row’s filter column as a parameter and returns a one-row table if the current user is authorised to see that row, or an empty table if they are not. SQL Server evaluates the predicate as a JOIN condition on every query against the protected table – rows for which the predicate returns empty are invisible to the user. The predicate function should be kept simple and well-indexed because it executes for every row accessed.<\/p>\n            <\/div>\n                    
3. What authorization methods work with SQL Server Row Level Security?<\/h3>\n            
\n                
Six primary methods: SESSION_CONTEXT (store user context in the session using sp_set_session_context – lightweight and flexible, but requires the application to set context on every connection); SQL Server role membership (IS_MEMBER function – simple and easy to manage, limited scalability); Active Directory group membership (external lookup, adds cross-boundary dependency); user name lookup (USER_NAME() – minimal overhead, limited flexibility); local lookup table (configurable, handles complex tier-based access); and staging tables (set access to NULL for unprocessed rows). The right choice depends on scalability requirements and application architecture.<\/p>\n            <\/div>\n                    
4. Does Row Level Security in SQL Server affect ETL and admin processes?<\/h3>\n            
\n                
Yes – RLS applies to all users including service accounts used by ETL processes, SQL Agent jobs, and admin scripts. A service account with only partial row access may produce incorrect results: INSERT and UPDATE operations may succeed but SELECT queries return filtered data, potentially causing ‘ghost record’ issues (rows that exist but are invisible to the ETL account) or excess deletes in anti-join patterns. ETL service accounts should be granted UNMASK-equivalent access or excluded from RLS policies using a dbo_override check in the access predicate.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Set up SQL Server Row Level Security with access predicates (inline TVFs) and security policies. Part 2 covers three setup patterns, six authorization methods (SESSION_CONTEXT, SQL roles, lookup tables), and working examples using WideWorldImporters.…<\/p>\n","protected":false},"author":19670,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[159027,159032,159012,159026],"coauthors":[98702],"class_list":["post-97969","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-access-predicate","tag-benjohnston_rls","tag-rls","tag-security-policy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97969","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/19670"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=97969"}],"version-history":[{"count":13,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97969\/revisions"}],"predecessor-version":[{"id":110006,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97969\/revisions\/110006"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=97969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=97969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=97969"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=97969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}