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. 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: 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: SQL Security & User-Level Access
    ... Because the users are going to be accessing the SQL Server through an .ade ... I'd like the users to be able to login to the SQL Server database using ... their username and password in the Members table. ... > It sounds like you have two security related items to consider. ...
    (microsoft.public.sqlserver.security)
  • Re: Restricting Access priviledge on SQL2000
    ... The "Application-Administrators" are neither members of sysadmin nor ... their respective database, but as well as to the defaults database (master, ... then make them members of the db_owner role in that database. ... then add the real SQL Server admins specifically to the ...
    (microsoft.public.sqlserver.security)
  • Re: Cant login to SQL Server
    ... I did a backup in SQL Server 2000 and restored it in SQL Server Express SP2. ... Once restored the database, I ran a stored procedure in SQL Server Express ... belongs to the sysadmin role. ... Login error for user B". ...
    (microsoft.public.sqlserver.security)