Solved

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

Posted on 2013-05-13
1
288 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 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Download ms sql express. 2 28
SQL Server / Update DB? 22 38
Requesting help with creating an SQL query with 2 tables 6 27
TSQL XML Namespaces 7 24
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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