?
Solved

read permission on tables

Posted on 2010-09-14
3
Medium Priority
?
284 Views
Last Modified: 2012-05-10
I shall wish to refine the rights of the groups of users. For that purpose, a group would have to can be in reading only of all the tables (no problem at this level), as well as in reading-writing of certain tables. I do not manage to find the command which can manage these modifications (while of highly-rated graphic there is no problem), but I do not see making it on hundred of table (multiplied by 6 groups), is a script available for doing this manip?

Thanks

Regards
bibi
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
Mohit Vijay earned 2000 total points
ID: 33670338
just doing copy paste a article text [http://www.mssqlcity.com/Articles/Adm/manage_users_permissions.htm]


----------------------
Managing Permissions
You can use the GRANT, DENY, and REVOKE statements to give or take away permission from a user or role.

The GRANT statement is used to give permissions to a user or role. By using the GRANT statement, it is possible to assign permissions to both statements as well as objects. You can use the GRANT statement with the WITH GRANT OPTION clause to permit the user or role receiving the permission to further grant/revoke access to other accounts.

This example grants the SELECT permission on the authors table to Alex

GRANT SELECT ON authors TO Alex

The DENY statement is used to deny a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships. You can use the DENY statement to deny both statements and objects permissions.

The following example denies the user Alex SELECT permissions to the authors table:

DENY SELECT ON authors TO Alex

The REVOKE statement is used to remove a previously granted or denied permission from a user in the current database. You can use the REVOKE statement to remove both statements and objects permissions. You can specify the GRANT OPTION FOR clause with the REVOKE statement to remove the WITH GRANT OPTION permissions. Therefore, the user will have the objects permissions, but cannot grant the permissions to other users. Specify the CASCADE clause along with the WITH GRANT OPTION clause, if the permissions being revoked were originally granted using the WITH GRANT OPTION setting.

The following example revokes SELECT permissions to the authors table from the Alex:

REVOKE SELECT ON authors TO Alex

Use the WITH GRANT OPTION setting very carefully, because in this case users can grant permissions to the objects to other users and it will be more difficult to manage security.

Don't grant the superfluous permissions to the public role, because each database user has the public role's permissions.
----------------------------
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 33670394
The following script assigns read only rights to a specific NT group and allows to specify a specific DB..It should get you started...Hope this helps...
if not exists(select 1 from syslogins where name ='YOURDOMAIN\READ_GROUP') 
begin
	create login [YOURDOMAIN\READ_GROUP] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
end


exec sp_msforeachdb '
if ''?'' in (''YOURDBNAME'')
begin
	if not exists(select 1 from sysusers where name =''YOURDOMAIN\READ_GROUP'')
	begin
		create user [YOURDOMAIN\READ_GROUP] for login [YOURDOMAIN\READ_GROUP]
		exec sp_addrolemember ''db_datareader'', ''YOURDOMAIN\READ_GROUP''
	end
end
'

Open in new window

0
 

Author Closing Comment

by:bibi92
ID: 33688881
thanks bibi
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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