• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

read permission on tables

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
bibi92
Asked:
bibi92
1 Solution
 
Mohit VijayCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
bibi92Author Commented:
thanks bibi
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now