sa permissions

[seabra]

Hello!

I'm trying a problem with a sql 2000 server.

My SA is not linked to the sys_admin role.
How can i resolve this?

Best regards
MS

 

[Crowley16]

why do you need to resolve it?

removing sa from the sys_admin role is a standard security measure.

what's the problem with using another admin account?

--------------------
Procrastinate Now!

 

[seabra]

My problem is that i have no other system administrator.

MS

 

[mrdenny]

If you have no accounts which are members of the sysadmin role then you will need to rebuild the master database using the rebuildm.exe utility on the SQL CD-Rom. This will wipe out all of your logins to the SQL Server so you'll need to know the passwords in order to recreate the logins.

After you rebuild the master database you may also need to attach your databases back to the SQL Server.

If you don't want to rebuild the master database you can also reinstall SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Crowley16]

that's strange...

as far as I know, you can only remove sa from the admin role or delete it IF another sysadmin has been created.

Am I wrong?

--------------------
Procrastinate Now!

 

[mrdenny]

I would assume that you are correct, however I've never tried to remove the rights from the sa account.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLWilts]

Wouldn't someone in the windows admin group on that server be able to do what you need??

 

[mrdenny]

Only if no one has removed BUILTIN\Administrators from the sysadmin group. If someone remove sa from the sysadmin group I would assume that the BUILTIN\Administrators has been removed from the sysadmin group as well.

Also

My problem is that i have no other system administrator.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLBill]

Seabra,

Microsoft has a script to copy logins and passwords from SQL Server. Find that and run it before you rebuild master (if you haven't done it already). That will create a script of the logins/passwords that you can run once the master db is rebuilt. Look through the script to see if SA exists in it and remove that before you run the script to put the logins back.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875