Post reply

Database roles

  • February 6, 2006 at 12:53 pm

    #111378

    Need to create standard roles on each db.  I want to create 4 roles with certain types of permissions to a database.  I know how to create a role and apply their permissions but what I would like to do is create a script to create the role and its' permissions so I can run them in each db i choose and change them easily in the future.

    I'm trying to place all my users into groups then assign them to a role, or roles accordingly.

    Thanks

  • February 6, 2006 at 1:16 pm

    #619070

    The script used to create a role would be:

    exec sp_addrole N'rolename'

    Then to add permission you would need to specify the permission as well the object such as:

    GRANT  SELECT  ON [dbo].[tablename] TO [rolename]

    Or if for instance you wanted to grant rights which already exist as part of another role such as the ability to read all data in all tables you could add the new role to an existing role:

    exec sp_addrolemember N'db_datareader', N'rolename'

  • February 6, 2006 at 3:05 pm

    #619113

    Thanks, I'll try that.

  • February 7, 2006 at 2:39 pm

    #619407

    you could also make it a part of your model database and that way each time you create a new database they would be there for you.

  • February 8, 2006 at 9:33 am

    #619561

    Great idea Raymond, thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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