Monday, 26 October 2015

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

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.

CREATE PROCEDURE ReadOnlyUserProc
AS 
BEGIN
    SELECT GETDATE() AS CurrentDate
END


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



EXEC ReadOnlyUserProc

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.



USE AdventureWorks
GO
 
GRANT EXECUTE TO readonlyuser
GO

 


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



EXEC ReadOnlyUserProc

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.

No comments:

Post a Comment