Solved

read permission on tables

Posted on 2010-09-14
3
234 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 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now