|
|
Posted by lucm on 10/16/06 17:32
cybertoast@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 help
You 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]
|