Permissions!

From: Arpan (arpan_de_at_hotmail.com)
Date: 09/04/05

  • Next message: Dan Guzman: "Re: Permissions!" 
    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
    ConnectionString:

    objConn.Open
    "Provider=SQLOLEDB;Server=(local);Database=ARPAN;UID=sa;PWD="

    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.

    Thanks,

    Arpan

  • Next message: Dan Guzman: "Re: Permissions!"