Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003

    Unanswered: permission for a user in a database


    Can anybody help me - how to grant a particular user the permission to access a database? The user is having access to another database in the same database server. I know about the grant command which gives permission to the tables in the database but I dont think I can use the same grant command for giving permission to the database for the user.


  2. #2
    Join Date
    Sep 2002
    Hong Kong

    Re: permission for a user in a database

    Consider the following. The login j_doe has access to sales_db and he needs access to deliveries_db as well. Since the login already has access to a database, the login must already exist on the server.

    To allow the login access to the deliveries_db perform the following....

    Login with sso_role - Normally "sa" user has this but I don't know your setup.

    use deliveries_db
    sp_adduser 'j_doe'

    This will add the login j_doe to the database and have the same user name in the database as the login. You can now grant this user access to the tables using something like...

    grant select on delivery_location to j_doe
    grant exec on sp_delivery_report to j_doe

    If "j_doe" needs to be in a specific group, for example the group "dispatcher", do the following instead:

    use deliveries_db
    sp_adduser 'j_doe', 'j_doe', 'dispatcher'

    The login j_doe will have all the privillages of the dispatcher group automatically. The second "j_doe" in the above statement refers to the "name in db". I have never made this different to the login and I can't imagine why anyone would - No doubt someone can show me a good example of this :-)

    If j_doe is required to be able to create objects as the database owner use the following instead

    use deliveries_db
    sp_adduser 'j_doe', dbo

    Grants etc need not be applied as the user is alias to dbo.

    Hope this helps.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts