Re: Security Admin

From: Jim (jafloren_at_insightbb.com)
Date: 06/21/04 

Date: Sun, 20 Jun 2004 17:18:20 -0700

We have assigned the 6 Windows Logins Security Admin at
the server level. We also assigned db_securityadmin and
db_accessadmin at the database level. We need them to
administer all server and database access to users except
SA which according to books online, this should work.

The interesting part is the 6 people can grant Server
level access to the SQL Server and grant public database
access. They just can't grant any database roles
including the basic db_datareader or db_datawriter.

I changed the 6 Logins to DB_Owner and they can grant all
the database roles. The problem is we don't want these
users to be able to modify any DDL.

Jim
 
>-----Original Message-----
>It depends on what roles they are trying to add users to.
>There are fixed server roles, fixed database roles and
>user-defined roles (which exist at the database level)
>At the database level, members of sysadmin and db_owner
can
>add members to fixed database roles and user-defined
roles.
>Role owners can add members to any user-defined roles
that
>they own. Members of db_securityadmin can add members to
any
>user-defined roles.
>At the server level, members of sysadmin can add logins
to
>fixed server roles. Members of a fixed server role can
add
>members to the same fixed servers roles they are members
of.
>You can find more information in books online under:
>sp_addrolemember
>sp_addsrvrolemember
>
>-Sue
>
>On Sun, 20 Jun 2004 13:32:39 -0700, "Jim"
><jim_floren@conseco.com> wrote:
>
>>We are attempting to move our SQL Security
responsibility
>>out of our DBA group and over to our IT Security Group
to
>>get in compliance with SOX. We have granted security
>>admin server role to our 6 IT Security Windows
>>Authentication Logins. We also granted
db_securityadmin
>>and db_accessadmin to the same group of logins.
>>
>>They can create new SQL Logins and grant users public
>>access to databases, but they cannot assign specific
>>roles to any users. They get an ODBC SQL State 42000
>>error 15247 - user does not have permission to perform
>>this action. We have verified SQL 2000 SP3a is
installed
>>on the server(s) and client workstation which I
>>understand is MDAC 2.71a.
>>
>>Can anyone suggest where I can get further information
to
>>fix this issue or who you suggest I communicate with?
>>Your assistance is greatly appreciated.
>
>.
>

Relevant Pages

  • Re: SSMS does not display server level DDL Triggers
    ... Database Level: Databases,, Programmability, Database Triggers ... Server Level: Server Objects, Triggers ...
    (microsoft.public.sqlserver.tools)
  • Re: MYSQL - Multiserver communicate tables ?
    ... 2401 members, members can post wrote: ... We have to build a transfer and merge from Tables hosted on Server ... merge the tables into its operating Database. ... How SERVER MYSQL Berkeley DB 3.2.9a on SERVER.1 could ...
    (comp.lang.php)
  • Re: minimum permissions to grant / delete logins
    ... database and are not users of any other database. ... If they are members ... any login is permission on Server ... the ability to administer user logins, but I don't want this person to ...
    (microsoft.public.sqlserver.security)
  • How to make distribution list from database.
    ... I have a database containing 3000 members. ... Is it possible to extract the mail ... We have a Sybase database and Exchange 2003 server. ...
    (microsoft.public.exchange.admin)
  • Re: Sending LOTS of Email via PHP / Timeout Question(s)
    ... Every morning, ALL members of the database will receive a listing of available jobs in the country (so, at least 4200 emails right there). ... This site will be hosted on its OWN SERVER so I will be able to have full control over PHP and Apache timeout values.... ...
    (alt.php)