change db owner from sa to another login with sysadmin provilege

change db owner from sa to another login with sysadmin provilege

Post by Biva » Sun, 04 Feb 2001 02:21:34



Hello All,
Here is my next question.. I am told to completely get rid of sa login as
multiple users know the password.  Instead of just changing the password, I
am told that it is a good practice that we don't have sa at all (from my
training class).  I created a new login with all privileges that sa had.
Now, as a DBA, only I know the password to this login.
Now, I was verifying all the databases and jobs and alerts and objects owned
by sa.  I see that almost all the databases and jobs are owned by sa.  When
I looked up sp_changedbowner in BOL, it tells me that I can't change the
owner of system databases.  So I guess, I can't drop sa. Has anyone run into
this situation before?  I don't understand why the Microsoft certified
trainer said that I should drop sa login as  soon as possible when system
databases are owned by sa during SQL installation.  I am confused.  Can
anyone help me???

Thanks, Biva

 
 
 

change db owner from sa to another login with sysadmin provilege

Post by Sean Arm » Sun, 04 Feb 2001 04:14:24


Biva,

You can not drop the SA login.  Prehaps the MCT was meaning to set your
server up using only NT authentication instead of SQL and NT.  This would
prevent someone from logging on as SA.  If he was not then I would sure like
him to show me how to delete this account.  :)

My suggestion is to go to the propertys of your server set it to NT only
unless you have to use mixed mode (SQL Server Logins).  If you have to use
SQL Logins assign a very long and difficult password to the SA account ex)
tpwiwu4msaawicnd!

Good Luck,


Quote:> Hello All,
> Here is my next question.. I am told to completely get rid of sa login as
> multiple users know the password.  Instead of just changing the password,
I
> am told that it is a good practice that we don't have sa at all (from my
> training class).  I created a new login with all privileges that sa had.
> Now, as a DBA, only I know the password to this login.
> Now, I was verifying all the databases and jobs and alerts and objects
owned
> by sa.  I see that almost all the databases and jobs are owned by sa.
When
> I looked up sp_changedbowner in BOL, it tells me that I can't change the
> owner of system databases.  So I guess, I can't drop sa. Has anyone run
into
> this situation before?  I don't understand why the Microsoft certified
> trainer said that I should drop sa login as  soon as possible when system
> databases are owned by sa during SQL installation.  I am confused.  Can
> anyone help me???

> Thanks, Biva


 
 
 

change db owner from sa to another login with sysadmin provilege

Post by Biva » Sun, 04 Feb 2001 04:36:02


Thanks Sean for the reply.. I do have to use mixed mode authentication for the server.. so I guess I can't drop the login.. I wonder why the MCT was telling us to drop the sa login..

*** Sent via Developersdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

change db owner from sa to another login with sysadmin provilege

Post by Flicke » Sun, 04 Feb 2001 05:00:48


I think the MCT said you'd change the sa password because it is blank, not
to delete the sa account.


Quote:> Hello All,
> Here is my next question.. I am told to completely get rid of sa login as
> multiple users know the password.  Instead of just changing the password,
I
> am told that it is a good practice that we don't have sa at all (from my
> training class).  I created a new login with all privileges that sa had.
> Now, as a DBA, only I know the password to this login.
> Now, I was verifying all the databases and jobs and alerts and objects
owned
> by sa.  I see that almost all the databases and jobs are owned by sa.
When
> I looked up sp_changedbowner in BOL, it tells me that I can't change the
> owner of system databases.  So I guess, I can't drop sa. Has anyone run
into
> this situation before?  I don't understand why the Microsoft certified
> trainer said that I should drop sa login as  soon as possible when system
> databases are owned by sa during SQL installation.  I am confused.  Can
> anyone help me???

> Thanks, Biva

 
 
 

change db owner from sa to another login with sysadmin provilege

Post by Biva » Sun, 04 Feb 2001 06:20:19


No, the MCT specifically said to create a new login with admin privileges
and then delete the SA login.  That was why  I was amazed when I realized
the system databases are owned by sa and possibly could never be owned by
anyone else...


> I think the MCT said you'd change the sa password because it is blank, not
> to delete the sa account.



> > Hello All,
> > Here is my next question.. I am told to completely get rid of sa login
as
> > multiple users know the password.  Instead of just changing the
password,
> I
> > am told that it is a good practice that we don't have sa at all (from my
> > training class).  I created a new login with all privileges that sa had.
> > Now, as a DBA, only I know the password to this login.
> > Now, I was verifying all the databases and jobs and alerts and objects
> owned
> > by sa.  I see that almost all the databases and jobs are owned by sa.
> When
> > I looked up sp_changedbowner in BOL, it tells me that I can't change the
> > owner of system databases.  So I guess, I can't drop sa. Has anyone run
> into
> > this situation before?  I don't understand why the Microsoft certified
> > trainer said that I should drop sa login as  soon as possible when
system
> > databases are owned by sa during SQL installation.  I am confused.  Can
> > anyone help me???

> > Thanks, Biva

 
 
 

change db owner from sa to another login with sysadmin provilege

Post by Allan Mitchel » Mon, 05 Feb 2001 07:24:37


sa cannot be dropped.
I think/hope the MCT meant (and this is what I do and tell others to do
when building a server) if you have to use Mixed Mode authentication
then assign a password to sa and forget it. All your DBAs should be in
the sysadmin server role anyway.

If you do ever need to use sa account then you can change the password
to whatever you want anyway so forgetting the old one isn't a problem.

Allan



Quote:> Hello All,
> Here is my next question.. I am told to completely get rid of sa
login as
> multiple users know the password.  Instead of just changing the
password, I
> am told that it is a good practice that we don't have sa at all (from
my
> training class).  I created a new login with all privileges that sa
had.
> Now, as a DBA, only I know the password to this login.
> Now, I was verifying all the databases and jobs and alerts and
objects owned
> by sa.  I see that almost all the databases and jobs are owned by
sa.  When
> I looked up sp_changedbowner in BOL, it tells me that I can't change
the
> owner of system databases.  So I guess, I can't drop sa. Has anyone
run into
> this situation before?  I don't understand why the Microsoft certified
> trainer said that I should drop sa login as  soon as possible when
system
> databases are owned by sa during SQL installation.  I am confused.
Can
> anyone help me???

> Thanks, Biva

--

Allan Mitchell
MCSE

Sent via Deja.com
http://www.deja.com/

 
 
 

change db owner from sa to another login with sysadmin provilege

Post by Richard Waymir » Mon, 05 Feb 2001 01:24:00


The MCT is simply wrong :-)  You can't drop the sa login - it's not
supported.

--
Richard Waymire, MCSE, MCDBA

> No, the MCT specifically said to create a new login with admin privileges
> and then delete the SA login.  That was why  I was amazed when I realized
> the system databases are owned by sa and possibly could never be owned by
> anyone else...



> > I think the MCT said you'd change the sa password because it is blank,
not
> > to delete the sa account.



> > > Hello All,
> > > Here is my next question.. I am told to completely get rid of sa login
> as
> > > multiple users know the password.  Instead of just changing the
> password,
> > I
> > > am told that it is a good practice that we don't have sa at all (from
my
> > > training class).  I created a new login with all privileges that sa
had.
> > > Now, as a DBA, only I know the password to this login.
> > > Now, I was verifying all the databases and jobs and alerts and objects
> > owned
> > > by sa.  I see that almost all the databases and jobs are owned by sa.
> > When
> > > I looked up sp_changedbowner in BOL, it tells me that I can't change
the
> > > owner of system databases.  So I guess, I can't drop sa. Has anyone
run
> > into
> > > this situation before?  I don't understand why the Microsoft certified
> > > trainer said that I should drop sa login as  soon as possible when
> system
> > > databases are owned by sa during SQL installation.  I am confused.
Can
> > > anyone help me???

> > > Thanks, Biva

 
 
 

1. Change DB owner/Table owner

I know it is possible to change the database owner, but am I able to
change
the owner of a table as well?  Currently, the owner of the database and
tables
is EXAV.  The problem is for the programmers...  this is the only table
owned by
anyone other than DBO.  We've got some code that is supposed to work
for both tables and it causes them some coding problems because that
qualifier
isn't the same.  For the tables owned by EXAV, they have to code it
EXAV.tablename
and for the tables owned by DBO, they just code the tablename.

Is there anything I can do short of creating a new database and copying
the data over?
Thanks for the advice!
Toni Eibner

2. How do I implement a b-tree in vb?!

3. SA is not owner of database need to change

4. Sales/Marketing job opening in Bay Area (California)

5. Change database owner to sa.

6. Scalable SQL v4.0 get Me !

7. Changing the online database owner to sa

8. Monolith Board web ?

9. non sa owner viewing sa owned jobs

10. changing sa password / login

11. Changing passwords / Blocking SA login attempts

12. Can't access sysadmin features as sa on win2k3

13. deny access from sa / sysadmin role members