messed up sql server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

messed up sql server

Author

Topic

2luasql
Starting Member

3 Posts

Posted - 2004-07-07 : 17:45:52

Hi,

I'm a newbie and i did a stupid mistake that i removed administrator user from Sysadmin. So now i can't readd it or do any thing on enterprise manager.

Why could i do now without reinstalling sql server cause i have many user accounts on it.

Thanks for any help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-07 : 18:13:45

When you installed SQL Server, did you select mixed-mode authentication? If you did, you can still get in with the sa account. If you didn't, well hopefully you setup a different account and gave it sysadmin. If that's not the case either, you'll need to reinstall SQL Server.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-07 : 19:22:22

Did you remove the BUILTIN/Administrator account from SQL Server, or was it another Administrator account you removed.

Either way, schedule a job to run as sa that adds the user back to Sysadmin. Then, go home and slap yourself really hard several times. Of course, this is assuming you have an account that can even do this, which you probably don't. I don't even actually know if this will work.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

2luasql
Starting Member

3 Posts

Posted - 2004-07-07 : 20:27:09

yes, i removed BUILTIN/Administrator.

now i can't turn on the sql agent to run any jobs.

I tried to run

USE tinhtien
GO
EXEC sp_grantdbaccess 'BUILTIN/Administrator', 'Administrator'
GO
EXEC sp_addrolemember 'sysadmin', 'Administrator'

it gave me this error:

Server: Msg 15007, Level 16, State 1, Procedure sp_grantdbaccess, Line 98
The login 'BUILTIN/Administrator' does not exist.
Server: Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 37
The role 'sysadmin' does not exist in the current database.

but somehow now i can access to that database, i can view the content and have some other permissions which i couldn't before.

Any idea?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-07 : 22:49:48

You should still be able to access it with all the other accounts. ?????? Why can't you run the SQL Agent. Go to Services and see why it isn't starting up. Try to start it manually. Look at the login properties.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

2luasql
Starting Member

3 Posts

Posted - 2004-07-08 : 02:40:53

no, with the administrator user, i can only access to the above db after i've done some queries. I can't view any tables in other dbs.

SQL agent can't run with any users belonging to NT Administrators group. (failed for both "Logon as local System account and This account such as Administrator or others)

It just starts and immediately stops for some reasons

I think i'm gonna have to transfer all accounts to another server and reinstall this one on this server :(

Anyway, thanks for all helps. You guys are so kind.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 08:44:55

Did you install all the latest service packs for SQL Server on this installation? Regardless of whether you took the Administrator account out of SQL Server, you should still be able to changet he account the service runs under and restart it. Have you looked at the application and SQL Server error logs to see what errors are being produced from this?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-08 : 09:34:02

The agent account needs to be able to log on to the server for it to start.

-------
Moo. :)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 13:15:19

If you setup the SQL Server and SQL Server Agent services to run under the same domain admin account, it will start. Also, he never mentioned if he used mixed-authentication. Have you tried logging in as sa?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-08-02 : 07:40:37

Are the agents still running?
If yes, you can just go to another instance of the server on another machine and create an account with sysadmin privileges.

quote:
Originally posted by 2luasql

Hi,

I'm a newbie and i did a stupid mistake that i removed administrator user from Sysadmin. So now i can't readd it or do any thing on enterprise manager.

Why could i do now without reinstalling sql server cause i have many user accounts on it.

Thanks for any help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources