From: Arpan (arpan_de_at_hotmail.com)
Date: 3 Sep 2005 20:16:17 -0700
I am an absolute newbie as far as granting, revoking & denying
permissions to database objects are concerned. So please help me. I
went through BOL but couldn't understand many aspects.
This is what I want to do:
I have a SQL Server 7.0 database table which has 6 columns. I want that
only 3 users should be allowed to access this table. Out of these 3
users, the first user should be allowed to access the records of only
the first 2 columns, the second user should be allowed to access the
records of the next 2 columns only & the third user should be allowed
to access the records of the last 2 columns. How do I implement this?
Do I have to create new Users/Roles to implement this & then GRANT,
REVOKE or DENY permissions to these 3 users? Does 3 users mean I have
to create 3 new Users?
Now I access SQL Server from an ASP application using the following
When I open Query Analyzer & connect, the SQL Server drop-down menu
lists only 1 option which is 'ARPAN' (without the quotes). Under
'Connection Information', the 'Use SQL Server authentication' radio
button is checked & the Login Name is sa. The Password field is blank.
Please note that I login to my Windows 2000 Professional machine using
Administrator as the login name & a password.
This is what I did but failed:
I executed the following query in Query Analyzer:
DENY SELECT ON tblTry(FName,LName) TO dbo
but after that when I executed SELECT * FROM tblTry in QA, the records
under the columns named FName & LName were also retrieved along with
the records of the rest of the columns. Even opening the table tblTry
in Enterprise Manager displayed all the records under the columns FName
& LName along with the records of the rest of the columns! So what did
the DENY statement do?
I would be highly obliged if someone could please explain me the steps
required to do this in detail.