CODEWITHSUNDEEP
sqlsql-serverpermissions
mothana
mothana

Reputation: 91

SQL Server select ONLY columns with permissions

Let's say that I have TableA that has 5 columns:

Col1
Col2
Col3
Col4
Col5

and I am connected to SQL Server via User1. This User1 has permissions to select col1, col2, col3 only

I can do this 

select * 
from information_schema.COLUMN_PRIVILEGES

and loop for all return columns and build my select statement, but I have more than 500 select statements in my project (BIG HR Management System), so I need another way with less code modification.

Now if I try

Select * from TableA

it will return an error.

So how can I select only those columns with permissions without change all my code?

Edited: I want a method to override SQL exception when selecting a column with no permission, just return null and show no exception

Edited 2:- the select * was just an example, to be more simple : if I have 2 SQL Server users, the first one has permission in col1 - col3 The second one has permission to col4-col6 what is the best method to select columns with permission to every user

Upvotes: 0

Views: 971

Answers (1)

Xedni
Xedni

Reputation: 4695

I strongly agree with the comments suggesting that different queries be written for different behaviors, rather than doing this dynamically for each user. While I understand the desire to do it dynamically (and you certainly can), it's probably going to be of equal effort, or more to alter all your existing queries to work dynamically.

By the way, you said queries instead of procedures. No clue if that's actually what you're doing, but if your app just constructs ad hoc sql, I'd recommend using stored procedures instead. If you already are, ignore this last comment.

That said, here is a bit of code which illustrates how you could do this dynamically. Whether you write this code in each of your procedures, or build a function which returns a string (representing the columns the user is entitled to), or build this behavior into a separate stored procedure which gets called by each proc, hopefully this should point you towards your solution.

I built a little mock setup here so this should be fully re-runnable. I just picked one of the existing service accounts (NT Service\SQLWriter) as an arbitrary user for illustrative purposes.

-- Assumes you have a dB called test
use test
go

-- Set up test table
if object_id('test.dbo.colpriv', 'U') is not null drop table test.dbo.colpriv
create table dbo.colpriv
(
    Col1 int,
    Col2 int,
    Col3 int
)
insert into dbo.colpriv values (1, 3, 5)

grant select on dbo.colpriv  (Col1, Col3) to [NT SERVICE\SQLWriter]

go

declare 
    @Principal varchar(100) = 'NT SERVICE\SQLWriter', -- Just picked one out of a hat from sys.server_principials
    @Sql nvarchar(max),
    @Columns nvarchar(max)

select 
    @Columns = stuff((select ',' + quotename(column_name)
                      from test.INFORMATION_SCHEMA.COLUMN_PRIVILEGES
                      where table_name = 'colpriv'
                           and grantee = @Principal
                      order by column_name
                      for xml path('')), 1, 1, ''),
    @Sql = '
        select ' + @Columns + '
        from dbo.colpriv'

exec sp_executesql @Sql

Upvotes: 1

Related Questions