SQL Server Logins ???

Question

Post reply

SQL Server Logins ???

Dagan Lev

SSC Journeyman

Points: 82
More actions
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....

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

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

Balmukund SSCertifiable Points: 6648 More actions · Answer 1

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

Dagan Lev SSC Journeyman Points: 82 More actions · Answer 2

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?

Mathew M.Varghese SSCommitted Points: 1929 More actions · Answer 3

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

Balmukund SSCertifiable Points: 6648 More actions · Answer 4

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

Dagan Lev SSC Journeyman Points: 82 More actions · Answer 5

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'"

SQLBill SSC Guru Points: 51440 More actions · Answer 6

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

Dagan Lev SSC Journeyman Points: 82 More actions · Answer 7

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