?
Solved

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

Posted on 2013-05-13
1
Medium Priority
?
298 Views
Last Modified: 2013-05-14
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.
0
Comment
Question by:powerdrivehp
1 Comment
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 39163016
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question