question

You need to build your own type of row level security, and that can be accomplished by views with filter depending on your permissions. I have used something like this before. Feel free to use it and modify it as you like: -- table to handle windows logins CREATE TABLE [ADMIN].[LoginGroup]( [ID] [dbo].[DOMAIN_ID] IDENTITY(100000000,1) NOT NULL, [LoginGroup] [varchar](50) NOT NULL, CONSTRAINT [PK_LoginGroup] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE UNIQUE CLUSTERED INDEX [IX_LoginGroup_AK] ON [ADMIN].[LoginGroup] ( [LoginGroup] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] GO --table to handle different types of row level filter CREATE TABLE [ADMIN].[RowLevelFilter]( [ID] [dbo].[DOMAIN_ID] IDENTITY(100000000,1) NOT NULL, [FilterName] [varchar](50) NOT NULL, CONSTRAINT [PK_RowLevelFilter] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE UNIQUE CLUSTERED INDEX [IX_RowLevelFilter_FilterName] ON [ADMIN].[RowLevelFilter] ( [FilterName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] GO -- table to handle permission groups CREATE TABLE [ADMIN].[RowlevelPermissionGroup]( [ID] [dbo].[DOMAIN_ID] IDENTITY(100000000,1) NOT NULL, [DBName] [sysname] NOT NULL, [FilterGroup] [varchar](500) NOT NULL, CONSTRAINT [PK_RowlevelPermissionGroup] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [IX_RowlevelPermissionGroup_DBName_FilterGroup] ON [ADMIN].[RowlevelPermissionGroup] ( [DBName] ASC, [FilterGroup] ASC ) INCLUDE ( [ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] GO --table to handle the row level permission for every logingroup, permission group and row filter CREATE TABLE [ADMIN].[RowlevelPermissions]( [ID] [dbo].[DOMAIN_ID] IDENTITY(100000000,1) NOT NULL, [_rowLevelPermissionGroup_ID] [int] NOT NULL, [_loginGroup_ID] [int] NOT NULL, [_filterName_ID] [int] NOT NULL, [FilterValue] [varchar](500) NOT NULL, CONSTRAINT [PK_RowLevelPermissions] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_RowlevelPermissions_groupID_FilterName] ON [ADMIN].[RowlevelPermissions] ( [_rowLevelPermissionGroup_ID] ASC, [_filterName_ID] ASC ) INCLUDE ( [FilterValue], [_loginGroup_ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] GO ALTER TABLE [ADMIN].[RowlevelPermissions] WITH CHECK ADD CONSTRAINT [FK_RowlevelPermissions_LoginGroup] FOREIGN KEY([_loginGroup_ID]) REFERENCES [ADMIN].[LoginGroup] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [ADMIN].[RowlevelPermissions] CHECK CONSTRAINT [FK_RowlevelPermissions_LoginGroup] GO ALTER TABLE [ADMIN].[RowlevelPermissions] WITH CHECK ADD CONSTRAINT [FK_RowlevelPermissions_RowLevelFilter] FOREIGN KEY([_filterName_ID]) REFERENCES [ADMIN].[RowLevelFilter] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [ADMIN].[RowlevelPermissions] CHECK CONSTRAINT [FK_RowlevelPermissions_RowLevelFilter] GO ALTER TABLE [ADMIN].[RowlevelPermissions] WITH CHECK ADD CONSTRAINT [FK_RowlevelPermissions_RowlevelPermissionGroup] FOREIGN KEY([_rowLevelPermissionGroup_ID]) REFERENCES [ADMIN].[RowlevelPermissionGroup] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [ADMIN].[RowlevelPermissions] CHECK CONSTRAINT [FK_RowlevelPermissions_RowlevelPermissionGroup] GO --function to return the row filter for the row level permission CREATE FUNCTION [ADMIN].[fnGetRowPermissionByDB](@FilterName VARCHAR(50), @FilterGroup varchar(50), @dbname sysname) RETURNS TABLE AS RETURN ( SELECT FilterValue FROM ADMIN.RowLevelPermissions AS rlp INNER JOIN ADMIN.RowLevelPermissionGroup AS rlpgr ON rlp._rowLevelPermissionGroup_ID = rlpgr.ID INNER JOIN ADMIN.RowLevelFilter AS rlf ON rlp._filterName_ID = rlf.ID INNER JOIN ADMIN.LoginGroup AS lg ON rlp._loginGroup_ID = lg.ID WHERE rlpgr.DBName=@dbname AND rlpgr.FilterGroup = @FilterGroup AND rlf.FilterName=@FilterName AND IS_MEMBER(lg.LoginGroup)=1 ) GO --sample view with row level permission CREATE VIEW dbo.test AS SELECT * FROM dbo.myTable t1 WHERE --add your other conditions here t1.departmentID = (SELECT FilterValue FROM ADMIN.[fnGetRowPermissionByDB]('department', 'sales', 'adventureworks')) GO Notify me if it doesn't work and I'll take a look and see if I missed some code.

Another way to do it is to limite the selection criteria for individual users. We had a system that limited access by role and location. Depending on the role and location the user was provided with a list of accounts. Then, that list of accounts was always passed in to all stored procedure calls. Then you don't have to put in additional filtering through the use of views (not knocking @Hakan Winther solution, just offering more).