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.

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

2. Select ‘db_datareader’ role membership.

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

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

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

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

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

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’

Now you can see the definitions of the database objects.

Hope this might be useful to you as well.