Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Access Control of SQL Login Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 2:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
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
Post #1446336
Posted Thursday, April 25, 2013 2:16 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 3:26 AM
Points: 708, Visits: 3,287
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

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
Post #1446342
Posted Thursday, April 25, 2013 2:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
Have a look at this for GRANT Object Permissions

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



Regards
Durai Nagarajan
Post #1446347
Posted Thursday, April 25, 2013 5:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
In Sql 2000 I could apply these rules on specific tables using GUI mode. Is there any option like that in SQL 2008 R2?
Post #1446433
Posted Thursday, April 25, 2013 6:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 12,908, Visits: 32,005


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1446449
Posted Saturday, April 27, 2013 11:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
Thanks Abu Dina, Durai, Lowell.

All of your suggestions served my purpose well.

Thank you again, specially to Lowell for GUI help.
Post #1447313
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse