How to Restore Users in SQL Server 2000

Question

How to Restore Users in SQL Server 2000

Muhammed Sajid Hussain

SSC Veteran

Points: 290
More actions
November 16, 2006 at 1:56 pm

#121569

Hi all,
I need your help badly.
After restore backup I can't access users but I can access only "sa" account..
Previously I had the same problem but I used the following script which was OK, but now no.
Thanks a ton for your reply.
Best Regards
print ''
print '-- Script for restoring standard sql logins.'
print '-- As found on ' + @@servername + ' per ' + cast(getdate() as char)
select cast('exec sp_addlogin ''' + upper(a.loginname) + ''', ' as varchar(40)),
      convert(varbinary(64),b.password),
      cast(',''' + '<default database, varchar(20), your default database>' + ''', ' +
      '''' + '<default language, varchar(20), us_english>' + ''', ' as varchar(35)),
             convert(varbinary(64),b.sid),
      ',''skip_encryption'' '
from master.dbo.syslogins a,
       master.dbo.sysxlogins b
where a.isntname = 0
   and a.sid = b.sid
   and b.name not in ('sa','repl_publisher','repl_subscriber')
order by b.name
Print ''

Viewing 6 posts - 1 through 5 (of 5 total)

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

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 1

Your problem is not clear. Have you restored a DB on another Server ? If yes, check if you have the logins. If not run on the previous server the sp_help_revlogin.

In EM do you see any users ?

Moreno

Muhammed Sajid Hussain SSC Veteran Points: 290 More actions · Answer 2

Hi Grasshoper,

I restored database in new clone server & I can access it via "sa" user only, but when I run that script to restore users from live to clone I am gettig the following error on clone server..

"Server: Msg 170, Level 15, State 1, Line 1

"Line 1: Incorrect Syntax near ','.

Thanks for your feedback.

Best Regards

Balmukund SSCertifiable Points: 6648 More actions · Answer 3

You are looking for a way to move the login from ServerA to ServerB ?

Did you try below?

http://support.microsoft.com/kb/246133/en-us

Muhammed Sajid Hussain SSC Veteran Points: 290 More actions · Answer 4

Hi Lakhani,

Thank you so much for your great link...

I will work on clone server & I will get back to you soon.

Best Regards

AndrewM-375946 Right there with Babe Points: 747 More actions · Answer 5

databse logins can be imported by creating a syslogins.dat file and import using bcp, server logins have to be scripted manually, Do this by rightcliking on the server login in enterprise manager and SCRIPT>>CREATE.