automated role creation

[cybertoast]

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 <permissionon 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

[nigelrivett]

You create a role in the database then add users/roles to that role as
you say.

sp_grantdbaccess is used to allow users access to the database whereas a
role is a means of allowing permissions to be allocated to all users in
that role.

You can though administer via nt roles which may be the sort of thing
you are looking for.

www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***

[lucm]

cy********@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 <permissionon 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

[cybertoast]

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, [e-mail] wrote:

[e-mail] 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 <permissionon 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

[cybertoast]

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, [e-mail] wrote:

[e-mail] 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 <permissionon 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