Best method to add logins

Question

Post reply

Best method to add logins

SkyBVI

SSCoach

Points: 15128
More actions
November 18, 2010 at 12:41 pm

#142636

Hi,
There was some problem going on the server and we had to reinstall the OS, after that we see that all the databases are gone from the SSMS.
We had the mdf and ldf files on the disk drives.
So, I attached all the databases with the relevant mdf files.
Now I see that that the users from the server level have disappereaed.
In the databases(database level), there are 300 users, but in server level there are just built in adminsitrator and sa logins.
What is the best method to bring all users from database level to the server level.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 10 posts - 1 through 9 (of 9 total)

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You need to restore master database from the backup

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Note that if you moved and mdf/ldf files, you'll get errors when you restore master.

How to restore the master: http://msdn.microsoft.com/en-us/library/ms190679%28v=SQL.90%29.aspx

SkyBVI SSCoach Points: 15128 More actions · Answer 3

@ pradeep.

When I tried to do that, it said that for restoring master database, it should be in single user mode.?

How can I implement that.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 4

set startup option '-m' in configuration manager -->MSSQLSERVER properties.

See here

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

Make sure your session because it lets only one connection to the server.

All the best.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

SkyBVI SSCoach Points: 15128 More actions · Answer 6

I started Sql server in single user mode and then when I try to enter sql server management studio it says:-

Login faield for user 'sa'. Server is in single user mode.Only 1 administrator can connect at this time.

Is sa not the administrator?.

There is no one else logged into the server.

What should i do?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 7

SSMS uses a couple logins. Likely either your query windows or object explorer took the connection. Use SQLCMD to get the only connection and run queries.

Adiga One Orange Chip Points: 27284 More actions · Answer 8

SKYBVI (11/18/2010)
I started Sql server in single user mode and then when I try to enter sql server management studio it says:-
Login faield for user 'sa'. Server is in single user mode.Only 1 administrator can connect at this time.
Is sa not the administrator?.
There is no one else logged into the server.
What should i do?

Please go through the article which Steve had mentioned earlier. It explains how to restore master database.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

SkyBVI SSCoach Points: 15128 More actions · Answer 9

@ steve

When i try to start the sqlcmd, it starts and goes off (within 1 sec)

and show the same error, which i posted ....'only 1 administrator can login'..

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP