Is there row level security in MS SQL?

Interesting post on the LinkedIn SQLDBA user group about row level security in MS SQL.  Raju Pillai asked the following question:

I have a column with agency number , each agents under agency will have a userid to connect to the database. I want the agents to see only the rows that belong to their agency. I hate to create views for each agency against the table . What else can I do ?

James Hawkins response:

CREATE VIEW MyResults
AS
SELECT MyRows.*
FROM MyRows
INNER JOIN MyUsers ON MyRow.Agency = MyUsers.Agency
WHERE MyUser.Logon = SYSTEM_USER

Similarly, you might also want to explore the CURRENT_USER function.

 

Advertisements

About datalossguru

I am a data recovery engineer by trade, attorney by license, husband, father and coach by choice.
This entry was posted in DBA, SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s