|
|
Posted by cybertoast on 10/16/06 18:44
Never mind, I'm an idiot - adding the 'grant control' actually adds the
role to the database control set!!! My apologies. I was going under the
impression that just assigning "alter" permissions would be sufficient,
rather than "control" permissions.
Thanks much.
cybertoast wrote:
> 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]
|