SQL Sever Access Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

King Coffee - 28 Nov 2008 04:49 GMT

Hi,

I copied and restore a SQL 2005 database to SQL Sever 2008 SSMS. I can
access it OK with Windows Authentication. But when I try SQL Sever
Authentication, I get the following error:
The database <DataBase Name> is not accessible. (ObjectExplorer)

It connects to the database OK... but it can access it.

King

Reply to this Message

Abba - 28 Nov 2008 08:17 GMT

Hey King,

Has this SQLServer login got the permissions on the database?

hth,
AbbA

> Hi,
>
[quoted text clipped - 6 lines]
>
> King

Reply to this Message

Russell Fields - 28 Nov 2008 16:57 GMT

King,

Restoring a database with a user that is based on a SQL Server login to
another server has a potential problem with the SIDs not matching. Check
out the following to see if it resolves your problem:

EXECUTE sp_change_users_login 'Auto_Fix', 'SQLLoginName'

This assumes that your new server has the login 'SQLLoginName' and that your
database has a user 'SQLLoginName' that is based on the login
'SQLLoginName'.

You could also check in advance to see whether the SIDs match up. (Using
old system views._

SELECT name, sid from master.dbo.syslogins WHERE name = 'SQLLoginName'
SELECT name, sid from database.dbo.sysusers WHERE name = 'SQLLoginName'

RLF

> Hi,
>
[quoted text clipped - 6 lines]
>
> King

Reply to this Message

Uri Dimant - 01 Dec 2008 05:46 GMT

Russel
It is easy doing with a new syntax :-))

ALTER USER uname WITH LOGIN =loginname

> King,
>
[quoted text clipped - 26 lines]
>>
>> King

Reply to this Message