Solved

How to assign table level permisssions in SQL SERVER 2005

Posted on 2009-04-03
7
759 Views
Last Modified: 2012-05-06


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
Comment
Question by:viju2008
7 Comments
 
LVL 9

Assisted Solution

by:VikramMullick
VikramMullick earned 100 total points
Comment Utility
0
 
LVL 1

Assisted Solution

by:hari_tailor
hari_tailor earned 100 total points
Comment Utility
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
 
LVL 5

Author Comment

by:viju2008
Comment Utility
i logged in using sam  

and type but i want it only for a particular table not the whole DATABASE
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Expert Comment

by:hari_tailor
Comment Utility
SO use DENY Statement
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 300 total points
Comment Utility
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
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
and yes deny for all other permission like

DENY INSERT,UPDATE,DELETE ON emps TO ForMe
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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

10 Experts available now in Live!

Get 1:1 Help Now