Solved

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

Posted on 2013-05-13
1
283 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 39

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now