Post reply

SQL Permissions Newbie

  • June 10, 2009 at 1:21 am

    #201255

    Hey

    I'm fairly new to the security environment and dont rly know much about database roles, schemas, securables etc.

    I would really appreciate it if someone can maybe assist. I am using the SSMS 2005 so would appreciate if someone can provide me how i would use this gui to create a database role and granting that to a user to perform specific functions to a specific database.

    I know under the main security folder i Right click to create a new login (using SQL authentication) - then i select the specific default database the user might use. And then check under the user mapping that the user is added to that database object.

    But from here i would now like to create a database role that I would like to grant to this user to enable him to only be able to update, insert, select, and be able to create views - but not be able to delete tables.

    So I presume i go to that specific table and under security folder - under users i create him here also but from here im lost.

    Would really appreciate this newbie lesson 😀

    Kind Regards

    Antonette

  • June 10, 2009 at 5:48 am

    #1007283

    usually, i create two roles in this situation.

    one, just as you describe, that gives a user db_datareader and db_datawriter for example.

    then i create a second role, and it';s sole purpose is to REVOKE delete rights.

    so I create a New Database Role, give it a name and make dbo the owner....

    I don't add any "Owned Schemas", and go straight to the Securables tab.

    I add the second option "All objects of the types" and select Tables"

    Then i monotonously select each table and select the "Deny" Permission for "Delete"

    After the role is created, I make sure my users belong to both roles if they are not allowed to delete.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 10, 2009 at 7:31 am

    #1007382

    Hi

    Thanks for the reply just a question the db_datawriter and db_datareader do i select these from the Owned Schemas or Database Role Membership area's - on the page where i create the user on the database under the security_users folder?

    Also when i create this user for the specific table under security_users do i select dbo as the default schema?

    And after i created the new database role will that role be displayed in the database role membership on the screen where i created my user?

    Note: Also i cant seem to create views - on this database? How to i grant the user or a role this access

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply