Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

Grant read access to SQL Management Server

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
AXISHK
Asked:
AXISHK
  • 3
  • 2
  • 2
2 Solutions
 
Haris DjulicCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
AXISHKAuthor Commented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Haris DjulicCommented:
use  GRANT EXECUTE ON SCHEMA::dbo TO USER
0
 
AXISHKAuthor Commented:
Tks, in case only need to explicitly grant few stored procedure to a user. How to achieve it ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to run the following command for each SP and user:
GRANT EXECUTE ON StoredProcedureName TO Username

Open in new window

0
 
AXISHKAuthor Commented:
Tks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now