Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

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

0
viju2008
Asked:
viju2008
3 Solutions
 
hari_tailorCommented:
GRANT and REVOKE Statement are SQL Server, you can see more detail of statment  and Object permission in SQL Help

Example GRANT SELECT ON LOGIN TO sam
0
 
viju2008Author Commented:
i logged in using sam  

and type but i want it only for a particular table not the whole DATABASE
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
hari_tailorCommented:
SO use DENY Statement
0
 
RiteshShahCommented:
USE [master]
GO
CREATE LOGIN [ForMe] WITH PASSWORD=N'temp123', DEFAULT_DATABASE=[AdventureWorks],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO
CREATE USER [ForMe] FOR LOGIN [ForMe]
GO

GRANT SELECT ON emps TO ForMe

0
 
RiteshShahCommented:
and yes deny for all other permission like

DENY INSERT,UPDATE,DELETE ON emps TO ForMe
0
 
RiteshShahCommented:
finally this is for your [Login] table.
GRANT SELECT ON [LOGIN] TO Sam
 
DENY INSERT,UPDATE,DELETE ON [LOGIN] TO Sam

Open in new window

0

Featured Post

Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now