Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

read permission on tables

Posted on 2010-09-14
3
Medium Priority
?
290 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
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

926 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