Solved

Grant read access to SQL Management Server

Posted on 2014-11-14
7
84 Views
Last Modified: 2014-11-17
How to grant a user to have read access to all object in a MS SQL 2008 R2, including the stored procedure created by other ?

Tks
0
Comment
Question by:AXISHK
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 250 total points
ID: 40442127
You can create role for that specific case and then just add remove users from that role, i.e. use this code:

CREATE ROLE NEW_ROLE ;

GRANT EXECUTE SELECT ON SCHEMA::dbo TO NEW_ROLE 

EXEC sp_addrolemember 'NEW_ROLE ', 'NEW_USER'

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40442128
You can give to an user the db_datareader role permission but this is only to read data (table and views). For stored procedure and functions you need to grant execute permission for each created object.
0
 

Author Comment

by:AXISHK
ID: 40442551
Can we use TSQL to loop through all the user defined SQL Procedure and grant it to a user ? There are over a hundred of procedures ...

Tks
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40442587
use  GRANT EXECUTE ON SCHEMA::dbo TO USER
0
 

Author Comment

by:AXISHK
ID: 40444356
Tks, in case only need to explicitly grant few stored procedure to a user. How to achieve it ?
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40446709
You need to run the following command for each SP and user:
GRANT EXECUTE ON StoredProcedureName TO Username

Open in new window

0
 

Author Closing Comment

by:AXISHK
ID: 40446726
Tks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 54
SQL Recursion schedule 13 44
SQL server client app 3 71
How do I partition this table on date? 5 47
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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