Re: Restricting Access priviledge on SQL2000

The design of SQL Server prevents you from locking anyone out of the system databases. They must have access to master, to get their login validated and determine which databases they can then use. And they need tempdb to perform many routine operations. The general idea, is that they can access master and the other system databases, but they can't necessarily do anything when there. That is, the ability to create a new database is an action performed in master, but just because anyone can get into master, doesn't mean they can create databases.
So if you didn't make these users sysadmins, or go out of your way to give them rights in master, you should be fine. If they have some unexpected right, then that's a problem that can be addressed.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

"topokin" <topokin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:6B17750F-B8B0-4E25-94BE-F491F5B472AE@xxxxxxxxxxxxxxxx
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: unable to start SQL Service ... error code 3417
    ... How about just create a new instance and restore databases to the new instance? ... I wouldn't go with restoring master from another instance. ... The supported route is to rebuild your master database, start SQL Server in single user mode, ...
    (microsoft.public.sqlserver.server)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)
  • Re: MS Access DAO -> ADO.NET Migration
    ... full SQL Server and I see the logic you explained in a multi user ... allow two users to access the same database file Read/Write at any given ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MS Access DAO -> ADO.NET Migration
    ... For that it is much harder to handle the incremental identifier, ... database but although they have the data, they are not connected at the same ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... SQL Server Management Studio is nowhere to be found on my ...
    (microsoft.public.dotnet.framework.adonet)