Click here to Skip to main content
15,867,488 members
Articles / Programming Languages / C++

Elevate permission on a SQL login having read only access to Execute Stored Procedures and to View Database Schemas

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
26 Oct 2015CPOL2 min read 7.4K   3  
Due to an unavoidable situation, it was decided that all the write access to a particular server (SQL Server) was required to be removed immediately. And only few people should be able to access the server with full privileges.

Due to an unavoidable situation, it was decided that all the write access to a particular server (SQL Server) was required to be removed immediately. And only few people should be able to access the server with full privileges. How ever this wasn’t sound very friendly to the developers, since they were using this server for various kinds of activities during their development and testing. Once the write access was removed we ran into various kinds of trouble since majority couldn’t view the contents of the database objects such as Stored Procedures, Views etc.

So a requirement came to allow them to access the schemas so that they can view the contents, also to execute the procedures (required when debugging a flow using Profiler) without allowing anyone to change or add any new database objects nor any data updates to existing tables directly.

The easiest workaround was to create a SQL user login with read permission and grant execution access to that login.

1.  Create a SQL Login.

image

 

2. Only keep the ‘public’ server role selected for this SQL Login.

image

 

2. Select ‘db_datareader’ role membership.

image

 

Afterwards if you connect to the SQL Server using the above created login, you can see that only read only operations are allowed. You will be able to see the tables, views (cannot see the underlying SQL Code). Stored procedures will be hidden

 

image

 

image

I have created the following stored procedure in my sample database (AdventureWorks) for testing purpose.

<span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">PROCEDURE</span> ReadOnlyUserProc
<!--CRLF-->
<span style="color: #0000ff">AS</span> 
<!--CRLF-->
<span style="color: #0000ff">BEGIN</span>
<!--CRLF-->
<span style="color: #0000ff">SELECT</span> GETDATE() <span style="color: #0000ff">AS</span> CurrentDate
<!--CRLF-->
<span style="color: #0000ff">END</span>
<!--CRLF-->


And when the aforementioned procedure is executed as the ‘readonlyuser’ we will get the following error:



<span style="color: #0000ff">EXEC</span> ReadOnlyUserProc
<!--CRLF-->

image


Now lets just provide the necessary access to this login so that it can be use to execute the procedures and to see the schemas of database objects. Use the following code to grant the execution access to the previously created user.



<span style="color: #0000ff">USE</span> AdventureWorks
<!--CRLF-->
<span style="color: #0000ff">GO</span>
<!--CRLF-->
 
<!--CRLF-->
<span style="color: #0000ff">GRANT</span> <span style="color: #0000ff">EXECUTE</span> <span style="color: #0000ff">TO</span> readonlyuser
<!--CRLF-->
GO
<!--CRLF-->

 


And now when we execute the stored procedure we will get the desired result.



<span style="color: #0000ff">EXEC</span> ReadOnlyUserProc
<!--CRLF-->

image


However still we are unable to see the schema of the SQL Objects, which is a part of what we want in this exercise.


image


If you try to generate the script by right clicking the object you will get an error message:


image


We will try to fix that issue as well. Please follow these steps:


1. Right click the login and go to the properties window.


2. Go to the ‘Securables’ tab and check/tick the ‘Grant’ check box for ‘View any definition’.


3. Click ‘OK’


image


Now you can see the definitions of the database objects.


image


image


image













Hope this might be useful to you as well.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
-- There are no messages in this forum --