Post reply

SQL Server Logins ???

  • June 27, 2006 at 3:05 am

    #191116

    Hi ppl,

    I hope I'm actually asking the right question here.

    We have a dedicated SQL Server 2000. there was a main login assigned to all databases and that one has system administrators role.

    My problem is that I am looking to create a secure DB on my server that only an assigned user will be able to login, and that can be done, however the main login will still be able to view it being system administrator. Is there a way to actually create a DB that is only viewable to one user excluding all system admins???

    Hope I made myself clear enough....

  • June 27, 2006 at 3:36 am

    #646004

    you want to hide it from System Administrator (sa account) ?

     

     

  • June 27, 2006 at 4:18 am

    #646011

    yes, unfortunately I wasn't in the company when the DB was created and they created a few DB's under the main sa login, which is now the owner and would be very hard to change.

    so I would like to create a DB that would be accessable to a user but not to sa admin, is that possible?

  • June 27, 2006 at 4:42 am

    #646017

    It is a good Idea

    If you create a user and using that user create a database you can remove all other system administrator from your database expect ‘SA’.

    Regards

    Mathew

  • June 27, 2006 at 4:56 am

    #646021

    If you want to change database owner it should be simple.

    you can use system procedure sp_changedbowner

    Someone has to be dbo of the database. You can revoke Sysadmin role from sa

     

     

  • June 27, 2006 at 7:23 am

    #646047

    how do i revoke sysadmin role from sa, when ever i try to do so it gives me an error "can not use the reserved user or role name 'sa'"

  • June 27, 2006 at 11:04 am

    #646141

    You cannot revoke sysadmin from SA. That login is 'wired' in by Microsoft and can't be changed. However, if you really want SA to 'not work', you can change the authentication to use WINDOWS ONLY.

    -SQLBill

  • June 28, 2006 at 1:39 am

    #646289

    Thanks guyz, I guess I don't want to do that, as I am actually using the sa login to maintain the db's via the enterprize manager.

    I think I'll have to get on with the tidies job of assigning other users to each DB and than pointing the connection strings to them...

    Thanks for all your help

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply