?
Solved

Allow only read access to SQL Tables, Views, Stored Procedures, etc

Posted on 2009-02-23
23
Medium Priority
?
7,800 Views
Last Modified: 2013-05-08
Hi,

I need to give developers permissions to several SQL Server 2005 databases so they can ONLY view all the tables, views, stored procedures etc, but not create/edit/delete/modify any. If I give them db_datareader this only gives rights to data not to views/stored procedures etc. Is there a way using a database roles? I don't want to be assigning permissions all over the place.

Thanks
0
Comment
Question by:sterlingdev
  • 9
  • 9
  • 3
  • +1
22 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23713373
Ok.. Create a new User..

Click securables / Database Access:

1. Make sure you remove the public privilege for the user.
2. Select the database to which the user needs to have read access.
3. Give db_reader privileges and remove sysadmin privileges to that user.
0
 

Author Comment

by:sterlingdev
ID: 23713492
Ok, do you mean the db_datareader role? So by giving that role and taking the public role away give them rights to view everything not just the data tables?



0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23713535
If you have the db_reader permission alone, then the user will be able to read (SELECT) all objects and do a basic operation on the tables and views.
If that user has Public privilege then that user will be able to see objects in other databases too.

That's why I asked you to remove that privilege.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:sterlingdev
ID: 23713614
There isn't a db_reader role, so do you mean db_datareader?

I need to give permissions to read to Stored Procedures as well. Will this not do it?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23713658
Its typo..
db_datareader role gives SELECT permissions only to Tables and Views.
To execute the Stored Procedures, they need to be granted Execute rights on those procedures.
0
 

Author Comment

by:sterlingdev
ID: 23713710
Ok so it doesn't do what I asked.

I don't want them to execute stored procedures either, but view them. The database in question has hundreds so I can't grant them individually.

Also they probably will want to view the schema but not be able to modify it.

Basically, complete READ ONLY access to the database without being able to modifiy anything.

Obviously it's not that simple?
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 23717011
It may be that simple:

GRANT VIEW DEFINITION ON SCHEMA :: dbo TO user
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23717148
Even then you have to repeat this for all schemas within the database multiple times.

GRANT SELECT ON SCHEMA schemaname TO USER
GRANT EXECUTE ON SCHEMA schemaname TO USER
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 23717267
Not if all they are interested in is the VIEW DEFINITION and then the db_datareader role.

That is 2 statements for accomplishing what he is asking for.  If they have more than 1 schema, then they can grant it on each, but it is much less management than having to grant it to ALL objects to see what he wants them to see.

0
 

Author Comment

by:sterlingdev
ID: 23816736
I want them (AD group) to see everything, but not edit/create/delete anything.

How do I use the VIEW DEFINITION to do this?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23896300
Hope this is what you need at the end:

1. Create a new User in SSMS by Clicking Logins in Security.
2. Give the user name, Choose the Default Database and then click user mapping --> Select your Database --> Select db_datareader privilege and remove public privilege in the Database roles.
3. Run the Following Script for each and every object in your database.

GRANT SELECT ON tablename TO username;
GRANT SELECT ON viewname TO username;
GRANT EXECUTE ON procedurename TO username;
GRANT EXECUTE ON functionname TO username;
0
 

Author Comment

by:sterlingdev
ID: 23896463
Thanks, but there is thousands of objects in the database. I need to grant this to them all automatically. Is there a way to do this?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23896828
No need for worrying about that. Below code will  help you achieve that with ease:

Make sure you do the below before running the scripts:

1. Replace ? with your username.
2. Run with a privilege having access to all objects in that particular db. In other words run this with full privileges or sa privileges to avoid any hassles.
DECLARE @objname varchar(150), @type varchar(10), @usr varchar(20), @sql nvarchar(100);
SET @usr = '?';
 
DECLARE read_only_cur CURSOR FOR 
SELECT sc.name + '.' + ob.name, ob.type
FROM sys.objects ob, sys.schemas sc
WHERE ob.schema_id = sc.schema_id
AND ob.type IN ('U', 'V', 'FN', 'IF', 'TF', 'FS', 'FT','P', 'PC');
 
OPEN read_only_cur;
FETCH NEXT FROM read_only_cur
INTO @objname, @type;
WHILE @@FETCH_STATUS = 0
   BEGIN
   	  IF @type IN ( 'U', 'V')
   	  BEGIN
   	  	SET @sql = N'GRANT SELECT ON ' + @objname + N'TO ' + @usr;
   	  	EXEC sp_executesql @sql;
   	  END
   	  ELSE
   	  	BEGIN
   	  	   SET @sql = N'GRANT EXECUTE ON ' + @objname + N'TO ' + @usr;
   	  	   EXEC sp_executesql @sql;
   	  	END
      FETCH NEXT FROM read_only_cur;
   END;
CLOSE read_only_cur;
DEALLOCATE read_only_cur;

Open in new window

0
 

Author Comment

by:sterlingdev
ID: 23896892
Ok, bit confused...

Do I replace "?" with the user or group I want to give the read only access to?
Surely the users I want to give READ ONLY access to shouldn't need EXCUTE permission??

I have Domain Admin/sa rights so thats not a problem.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23897033
Yes.. You are correct.
Try it out and update me..
0
 

Author Comment

by:sterlingdev
ID: 23897081
If I'm reading that script right it is giving execute, when I don't want the users to be able to execute any stored procedures, just look at them.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23897121
What does << just look at them. >> means.

Kindly explain me so that I will say what privilege is required for that.
0
 

Author Comment

by:sterlingdev
ID: 23897222
I mean read the stored procedure code, to see what it does, but not modify it or execute it or create new stored procedures.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23897392
Ok.. The options available for a Stored Procedure are DELETE, EXECUTE, INSERT, SELECT, and UPDATE.

I hope you need to allow the user to only view the code of the procedure.
Without having the CREATE Privileges, you wont be able to View the code of those Procedures.

If you provide CREATE privileges, then the user can do all the above operations. Hence I didn't find it possible.
0
 

Author Comment

by:sterlingdev
ID: 23898254
Thanks Vee_Mod. I selected the wrong zones by accident.

rrjegan17:

Isn't SELECT read access? Someone else earlier in this question mentions the command "GRANT VIEW DEFINITION" - Will this do what I require?
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 2000 total points
ID: 23898682
The VIEW DEFINITION permission allows the ability to view the definition of the object (in this case, stored procedures to see the code, but not to execute or create or edit).

GRANT VIEW DEFINITION ON SCHEMA :: schemaname TO user

That will allow them to view the definition on all objects in that schema.  Tables, stored procedures, views, etc.  But not to edit, select, execute, update, or delete.  So this is the route to allow them to see the objects definition or code, but not anything else.

You could grant them one at a time so that you could say:

GRANT VIEW DEFINITION ON dbo.StoredProcedureName TO user
for each stored procedure so that they only saw the stored procedures.

Go to a Query Window and execute this (hit Ctrl-T before executing)
SELECT 'GRANT VIEW DEFINITION ON [' + object_name(object_id) + '] TO user'
FROM sys.sql_modules

When you get the output from that query, then you copy and paste it into another query window and execute it.
0
 

Expert Comment

by:fersher1975
ID: 39149502
I am trying to accomplish the same things which is only allowing a user to read the contents of the db's including  stored procedures.  when i run the following command

(GRANT VIEW DEFINITION ON SCHEMA :: schemaname TO user)

i replaced schemaname with dbo and replaced user with my test account and i get the following error "Cannot find the user 'testuser', because it does not exist or you do not have permission.  My test user is a domain user not a local server account.

I am logged in as a domain admin into the SQL server running sql Management studio.

I figured out my mistake..i did [domain\username] and it worked but how can i truly test it?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

807 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