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!"

    Relevant Pages

    • Re: Login with no Fixed Server Role and DB Role can stop SQL Agent Service?
      ... a Window 2000 Login with Domain User default permissions, ... > actually answered the question about the permissions the user has re: ... Forget about SQL Server for the moment. ... >> Enterprise Manager, but he is still able to stop the SQL Agent ...
      (microsoft.public.sqlserver.security)
    • Re: SQL Server Security: NT Groups
      ... permissions from their group membership. ... So if I'm a member of GroupA and GroupA is granted a login ... SQL Server and access database B. ... membership, role membership with deny taking precedence. ...
      (microsoft.public.sqlserver.security)
    • Re: SQL Server Security: NT Groups
      ... >permissions from their group membership. ... >So if I'm a member of GroupA and GroupA is granted a login ... >>I'm new to SQL Server security and I don't know if it is ... >>then just add the 2 logins to the SQL Server Roles. ...
      (microsoft.public.sqlserver.security)
    • Re: Security question ..
      ... > If you use NT authentication, a user's permissions to a database are ... Your assertion that a user's permissions are independent of the application ... Even using Access and "exploring" will require an ODBC login to SQL Server. ...
      (microsoft.public.sqlserver.server)
    • Re: permissions
      ... When you link SQL Server tables from Access, ... You must set permissions on database objects in SQL ... >Is it better to set the permissions on a database from the login create ...
      (microsoft.public.sqlserver.server)