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

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
sterlingdevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
sterlingdevAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sterlingdevAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
sterlingdevAuthor Commented:
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
DBAduck - Ben MillerPrincipal ConsultantCommented:
It may be that simple:

GRANT VIEW DEFINITION ON SCHEMA :: dbo TO user
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
sterlingdevAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
sterlingdevAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
sterlingdevAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes.. You are correct.
Try it out and update me..
0
sterlingdevAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
What does << just look at them. >> means.

Kindly explain me so that I will say what privilege is required for that.
0
sterlingdevAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
sterlingdevAuthor Commented:
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
DBAduck - Ben MillerPrincipal ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fersher1975Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.