viju2008
asked on
How to assign table level permisssions in SQL SERVER 2005
i have a SQL SERVER INSTANCE NAMED - SQL EXPRESS
with 3 databases
one of them is MARKET DB
which has 5 tables
1) HISTORY
2) ACCOUNTS
3) CAMPAIGN
4) LOGIN
5) RESPONSE
i have created a login named sam and VP
by default, the login sam can retrieve, select insert , update from the LOGIN TABLE
but i want to change that , SAM SHOULD Only be able to select from the login table
but not update,delete or create any other table
VP should not have any access to response table
but he can create alter ,select update , insert , drop tables , other than response table
HOW DO I ACHIEVE THIS IN SQL SERVER 2005 ?
SHould i do it with SQL QUERIES or is there any other way
I am a newbie on this subject
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SO use DENY Statement
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and yes deny for all other permission like
DENY INSERT,UPDATE,DELETE ON emps TO ForMe
DENY INSERT,UPDATE,DELETE ON emps TO ForMe
finally this is for your [Login] table.
GRANT SELECT ON [LOGIN] TO Sam
DENY INSERT,UPDATE,DELETE ON [LOGIN] TO Sam
ASKER
and type but i want it only for a particular table not the whole DATABASE