Very Computer

Does anyone use server roles or database roles?
I see the purpose of application roles and very rarely
the use of database roles, but server roles seem useless.
Any thoughts?

Pretty involved question really.  Basically it depends on how people access
data.  Just about every application I've seen has some kind of way of
managing what people in different groups can do in a database.  Many of
these were custom developed with their own user information in the database,
some used groups in the SQL 6.x days, and a few (too few IMHO) use roles in
SQL Server 7.0 and 2000.

Database roles are a good way to abstract permissions to database objects,
so that you just have to assign specific permissions to roles and then
assign users to roles, this works well for both SQL Server logins and
Windows logins.  Application roles can serve a similar purpose, but are
different in that the users' permissions are only overridden when they use
the application that activates the application role.  There are issues with
application roles and connection pooling that have to be considered as well
(there are KB articles that cover this).

The fixed Server roles are a convenience, but one that might not be needed
in a shop with one, or a few multipurpose DBAs, but in a big data center, it
can make sense to break out adminstrative roles along the lines of the fixed
server roles.

The bottom line is to have a thorough understanding of access requirements
and to know exactly how people can get into your database server.

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

Thanks for the reply!

Quote:>-----Original Message-----
>Pretty involved question really.  Basically it depends

on how people access

Quote:>data.  Just about every application I've seen has some
kind of way of
>managing what people in different groups can do in a
database.  Many of
>these were custom developed with their own user

information in the database,

Quote:>some used groups in the SQL 6.x days, and a few (too few
IMHO) use roles in
>SQL Server 7.0 and 2000.

>Database roles are a good way to abstract permissions to
database objects,
>so that you just have to assign specific permissions to
roles and then
>assign users to roles, this works well for both SQL
Server logins and
>Windows logins.  Application roles can serve a similar
purpose, but are
>different in that the users' permissions are only

overridden when they use

Quote:>the application that activates the application role.  

There are issues with

Quote:>application roles and connection pooling that have to be
considered as well
>(there are KB articles that cover this).

>The fixed Server roles are a convenience, but one that
might not be needed
>in a shop with one, or a few multipurpose DBAs, but in a
big data center, it
>can make sense to break out adminstrative roles along

the lines of the fixed

Quote:>server roles.

>The bottom line is to have a thorough understanding of
access requirements
>and to know exactly how people can get into your
database server.

>--
>Bob
>Microsoft Consulting Services
>------
>This posting is provided AS IS with no warranties, and
confers no rights.

>.