Thread: user rights

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    6
    Rep Power
    0

    Question user rights


    Can anyone tell me how to give users rights to create tables, see data and so on?
    I'm new to MS SQL Server 2000 and can't quite figure it out by myself.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    19
    Rep Power
    0
    My friend Buggirls.
    To give to "some" users the rigths to handle a database, you have to create and authentification proccess. Julie Meloni has a very good explained tutorial @ :

    http://www.zend.com/zend/tut/authent...n.php#Heading8

    Easy to understand, the best way is to create a table with the name, username and passwords of the users with rights to handle the table.

    Best regards

    ciao. Juanelo Von Seattle
  4. #3
  5. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    Here's how to do what you want in SQL Server 2000 using Enterprise Manager. I assume you know how to add logins already -- let me know if you don't. Here's how to assign permissions to a login:

    For creating databases
    0. Go into Enterprise Manager
    1. Expand the Server Group
    2. Expand the server
    3. Expand security and then click on Server Roles
    4. You should see several roles (Bulk Insert, Disk admin, Setup admin etc.) including one called "Database Creators". Double click on Database Creator and add the logins for this role. Note that the login should have already been created previously.

    For reading/manipulating data -- Method A -- permissions for individual user.
    0. Go into Enterprise Manager
    1. Expand the Server Group
    2. Expand the server
    3. Expand the Databases and then expand the database that you want to set up the permissions for.
    4. Select Users and select the user that you want to give permissions to.
    5. Right click on the user and select Properties. In the dialog box that comes up, click the Permissions button and you should be presented with another dialog box with a list of tables, stored procedures, views etc. You can set the permissions on various tables for this login from here.

    For reading/manipulating data -- Method B -- permissions for a database role.
    0. Go into Enterprise Manager
    1. Expand the Server Group
    2. Expand the server
    3. Expand the Databases and then expand the database that you want to set up the permissions for.
    4. Select Roles and you should see a list of roles on the right pane.
    5. Some of the predefined roles (e.g.) db_datareader may already do what you're looking for. However, you may not want to give read rights to all tables. In this case, what you want to do is create a new role. Right click on the right pane and select "New Database Role...". Set the role as a Standard Role and add any logins here, if you like (you can also add the logins to the role later on). Then click OK to create the new role.
    6. Right click on the new role you just created and select Properties. Then click on the Permissions button and set up the permissions for this role.
    7. You can also assign more users/logins to this role later on from the Properties dialog box.

    So, which is preferable, method A or B. Method A is a little shorter for one user. However, method B is much more preferable, especially if you have multiple users. With method A, you'd have to manually repeat all the assigning permissions steps for every new login. With method B, you can simply assign a new login to a role and have it get all the permissions of that role instantly. Note that you can also assign the same login to multiple roles.

    Hope this helps
    Last edited by Scorpions4ever; October 5th, 2003 at 12:12 PM.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo

IMN logo majestic logo threadwatch logo seochat tools logo