Authentication or permissions issue ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Authentication or permissions issue ?

Author

Topic

ras2a
Yak Posting Veteran

66 Posts

Posted - 2005-11-16 : 09:52:15

Hi all,

We recently had a major disaster and I had to recover our entire system. I used Veritas IDR to restore our system, then I had to reinstall MS SQL server and finally restore our SQL DBs. However, after the rebuild, users can no longer login (through our frontend VB app). It gives an error message about SQL login failed. The only way to get around this is to add all our domain users to the admins group and then they can login fine. It's probably something and nothing, could someone shed some light on this? I didn't set the SQL server up (initially) so just wondering if maybe it's something to do with Win/SQL authentication etc?

thanks a lot guys

Craig

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-16 : 09:58:42

are the users setup in the db (if you go to Security->Logins)?

if they are, you may need to "reconnect" the users
here is a script we use.... not sure of the author

DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name

OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName
END

CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go

________________________________________________

SQL = Serious Quaffing of Liquor

ras2a
Yak Posting Veteran

66 Posts

Posted - 2005-11-16 : 10:11:50

Hi mate, thanks for that. I just checked 'logins' and all I have is:

domain/Administrator
BUILTIN/Administrators
sa

I assume I can just add our domain users group in here and apply appropriate rights? Can you tell I'm a complete novice with SQL - lol. I more of an exchange man :)

thanks again, mate

Craig

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-16 : 10:16:51

Sure,

Add your domain group. (Security -> Logins) ... right click on logins, new login.... click the "...." button and find your domain user group.

You can give the group permissions as you see fit (once the user is added, click on the account and set database access and their server roles.

________________________________________________

SQL = Serious Quaffing of Liquor

ras2a
Yak Posting Veteran

66 Posts

Posted - 2005-11-16 : 11:09:33

Done that. Works a treat! You're a belter mate (that means you're a good un' in england..lol)

thanks a lot for that, really appreciate it :)

Craig

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-16 : 11:12:00

quote:
Originally posted by ras2a

Done that. Works a treat! You're a belter mate (that means you're a good un' in england..lol)

thanks a lot for that, really appreciate it :)

Craig

A belter mate... I will drink to that!

________________________________________________

SQL = Serious Quaffing of Liquor

ras2a
Yak Posting Veteran

66 Posts

Posted - 2005-11-16 : 11:42:52

Actually it's just a 'belter' - I should have put a comma before the word mate. Thanks again

Superb sig by the way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources