MS SQL - Change login Account to View Table Value Function as Read-Only

I have a login account called 'DBProcessMe'.

I want to change the permissions of this login account to open a table value function called
'FnProcessMyData' as read only.

Currently, users can open this function and

(1) alter the function, or (2) create a duplicate function by changing 'Alter to "Create".

I only want them to read the function only.

What is the best method to do this?

I am familiar with the system tables, database members, roles, etc.
powerdrivehpAsked:
Who is Participating?
 
lcohanDatabase AnalystCommented:
You should REVOKE the ALTER and CONTROL for that user and anyone else that should not be allowed to and add just VIEW DEFINITION rights - just right click the function in SSMS and under Properties - > Permissions you will see those on the right botom pane.

Using T-SQL is something like:

USE YourDBname
GO

GRANT VIEW DEFINITION ON DBProcessMe TO sql_user;
REVOKE ALTER,CONTROL,TAKE OWNERSHIP ON DBProcessMe FROM sql_user;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.