|
|
Posted by cybertoast on 10/16/06 18:39
This sequence is fine, but the problem is that the "grant control to
role1" portion does not add the role to the database permissions list.
Perhaps this is just a bug in the way SQL Server Mgmt Studio displays
roles and users. For instance, when I do a "sp_grantdbaccess
@username", the user shows up in the database's permissions list. Doing
a "create role @rolename" then "grant control to @rolename" still does
not add the role to the "users or roles" permission list under Database
Properties.
I need to test out whether the permissions assigned to the role using
the steps above propagate down to the users irrespective of the
Database Properties issues I've described. However, it seems that there
could be synchronization issues if the "grant control" execution is not
reflected in the permissions gui (for example if someone decides to add
the same role again, what happens?).
Thanks much for the responses tho'.
On Oct 16, 12:32 pm, l...@iqato.com wrote:
> cyberto...@gmail.com wrote:
> > i seem to have some misunderstanding about how roles work in sql server
> > 2005. i see that i can add a role to a database
> > (dbname->[right-click]->properties->permissions->[add ...]. THis allows
> > me to add either users or roles. Users can be added programmatically
> > using sp_grantdbaccess @username, but this does not allow for addition
> > of roles to access the database (i.e., sp_grantdbaccess @rolename does
> > not work).
>
> > Is there some other command that is used to add a role to the
> > database's permissions list? Seems there must be since all the other
> > parts of the permissions chain work quite well:
> > * sp_addrole
> > * sp_addrolemember
> > * grant <permission> on role::@rolename
> > etc.
>
> > So the missing ingredient of something like sp_grantdbroleaccess is
> > what I need and can't seem to find it anywhere!
>
> > Thanks much for any helpYou should use the T-SQL syntax instead of the SPs whenever possible,
> as SPs might get obsolete at some point.
>
> Basically, here is a simple process to handle the users and roles.
>
> 1) Create a server-wide login:
> create login user1 with password = '123';
>
> 2) Create the user in your database:
> use database1;
> create user user1;
>
> 3) Create a role in your database:
> use database1;
> create role role1;
>
> 4) Add the user to the role:
> use database1;
> exec sp_addrolemember 'role1','user1';
>
> 5) Give some permissions to the role:
> grant control to role1;
>
> Whenever you need to fine-tune the permissions, you just use the
> "grant" keyword on the role.
>
> Note: "grant control" gives all permissions on the database objects,
> most of the time you should be more restrictive.
>
> Regards,
> lucm
Navigation:
[Reply to this message]
|