Solved

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

Posted on 2013-05-13
1
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

717 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