Access Control of SQL Login

Question

Access Control of SQL Login

shohelr2003

Hall of Fame

Points: 3535
More actions
April 25, 2013 at 2:02 am

#274525

Dear,
I have created several logins such as ACC, SALES, HR, DIST, ADT and IT. Now I want that the respective login can access (select, insert, update, delete) its corresponding departments' tables. I am using SQL Server 2008 R2 Standard Edition.
But I do not know how to do it.
Please help me to sort out the problem.
Regards,
Akbar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

Have a read of this: http://msdn.microsoft.com/en-us/library/ms189121.aspx

So you can either go with the predefined database roles and choose the appropriate roles (db_datareader, db_datawriter, db_ddladmin) to the mapped users.

Or

Create a database role, grant the role the required permissions then add your users to the role.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

durai nagarajan SSCoach Points: 15778 More actions · Answer 2

Have a look at this for GRANT Object Permissions

http://msdn.microsoft.com/en-IN/library/ms188371(v=sql.105).aspx

Regards
Durai Nagarajan

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 3

In Sql 2000 I could apply these rules on specific tables using GUI mode. Is there any option like that in SQL 2008 R2?

Lowell SSC Guru Points: 323518 More actions · Answer 4

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 5

Thanks Abu Dina, Durai, Lowell.

All of your suggestions served my purpose well.

Thank you again, specially to Lowell for GUI help.