Re: Restricting Access priviledge on SQL2000

Thanks for the response.

The "Application-Administrators" are neither members of sysadmin nor
Local-Administrators of the SQL-Server. They actually have access only to
their respective database, but as well as to the defaults database (master,
msdb, tempdb). It is in these default database that I will like to resctrict
their access.

Thanks
topokin

"Rick Byham, (MSFT)" wrote:

You must have made the "Application-Administrators" members of the sysadmin
role in SQL Server.
Take them out of the sysadmin role, add them as users in the correct
database, then make them members of the db_owner role in that database. Then
they will have full privileges in that database, but not others.

I'm assuming that the "Application-Administrators" do not need administrator
privilege on the computer so they aren't members of the domain
administrators or the local administrators group. If they must have such
membership, then add the real SQL Server admins (you) specifically to the
SQL Server sysadmins role, and remove the BUILTIN\Administrators from the
SQL Server sysadmins role. BE CAREFUL HERE. If you remove
BUILTIN\Administrators from the sysadmins role you might lock yourself and
everyone else out as sysadmins. Make double sure you (or others) are members
of the SQL Server sysadmin role under their own specific login, before you
remove BUILTIN\Administrators. If possible, it's better to just remove the
"Application-Administrators" from the computer local administrators group.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:%23%23AapKQhIHA.4140@xxxxxxxxxxxxxxxxxxxxxxx
Hi
Giving this access however allow them
priviledges on other database,

What do you mean? What kind priviledges? I doubt that you can achive it
without adding them to sysadmin server role
can you be more specific?



"topokin" <topokin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7566BC29-2CF2-4BB8-8A59-58B6354CF802@xxxxxxxxxxxxxxxx
I have Application-Administrators that need to have access through EM to
their database on an SQL2000 Server. Giving this access however allow
them
priviledges on other database, including the default DB (Master, mode,
etc)
on the Server.

Is there a way to rescrict each administrator to his/her own specific
database.

Thanks,
topokin



.

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: Sharepoint index problems in SQL Server.
    ... The Administrator account is a sysadmin and have all the access to all the ... > database is master and language US_English? ... >> I've set back the account localsystem for both SQL Server and Microsoft ...
    (microsoft.public.sqlserver.fulltext)
  • Re: System Administrator Implied Permissions
    ... mapped to the 'dbo' user are members of the public and db_owner roles. ... Permissions are not checked for sysadmin role members so all other role ... > database role need to be assigned to any sysadmin role? ... >>> SQL Server initially creates the 'sa' user. ...
    (microsoft.public.sqlserver.security)
  • Re: db_denydatawriter
    ... perhaps this also gives read write access on the database to this user? ... Resrictive permissions overrides in its own level. ... However, if she has sysadmin right, then she'll be able to modify that data. ... Is it possible she has some admin rights which override DenyWriter (though ...
    (microsoft.public.sqlserver.security)
  • Re: Restricting Access priviledge on SQL2000
    ... You must have made the "Application-Administrators" members of the sysadmin role in SQL Server. ... Take them out of the sysadmin role, add them as users in the correct database, then make them members of the db_owner role in that database. ...
    (microsoft.public.sqlserver.security)