Determing if stored procedure can execute based on AD login

Question

I have a stored procedure that updates data in a table for a specific record. I have several users who will be making use of this stored procedure however I only want to them to be able to update records that are assigned to them.

Each record that is to be updated by the stored procedure has a field named "UserID" which define who has control over the record. I also have a mapping table that maps active directory logins to the UserID's.

I am using Active Directory so SQL Server knows who is attempting to execute the stored procedure. Is there a way within the stored procedure to look-up the users active directory login in another table and then determine if they have access to the records attempting to be updated?

Answer 1

You can find out who the current user is in the proc by calling SYSTEM_USER and incorporating that into the query that updates the rows.

Answer 2

I'm no application designer but on the surface, your solution sounds unnecessarily complicated to me.

That said, you can issue the following query to get the Windows AD Login name of the user currently executing the stored procedure. You can use this information to cross reference with your mapping table to determine if the AD account has the required privileges to perform the operation.

SELECT SYSTEM_USER

Do keep in mind that this returns the name of the currently executing context, so keep in mind that this can be explicitly changed using the "Execute As" statement.

EDIT:

I wonder if perhaps a view could be used to limit the data visible to a given AD account. The Stored Procedure logic could then focus on the data modification aspect, rather than security implementation. Controlling your data access using views would also ensure that a consistent security access method is used across multiple stored procedures if required, as opposed to having to implement security checking within each.

Since writing this, the link provided by Martin Smith, details how this solution can be implemented:

Granting Row-Level Permissions in SQL Server

In conclusion, a combination of both is how Microsoft suggest you implement the solution to your problem.

Answer 3

Does this article help? Granting Row-Level Permissions in SQL Server

It recommends the following steps

• Create the table, adding an additional column to store the name.
• Create a view that has a WHERE clause based on the user name column. This will restrict the rows returned to those with the specified value. Use one of the built-in functions to specify a database user or login name. This eliminates the need to create different views for different users.
• Create stored procedures to select, insert, update, and delete data based on the view, not the base tables. The view provides a filter that restricts the rows returned or modified.
• For stored procedures that insert data, capture the user name using the same function specified in the WHERE clause of the view and insert that value into the UserName column.
• Deny all permissions on the tables and views to the public role. Users will not be able to inherit permissions from other database roles, because the WHERE clause is based on user or login names, not on roles.
• Grant EXECUTE on the stored procedures to database roles. Users can only access data through the stored procedures provided.