SQL Sever Access Error

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


"King Coffee" wrote in message

> 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
 >> Stay informed about: SQL Sever Access Error 

Russel
It is easy doing with a new syntax )

ALTER USER uname WITH LOGIN =loginname



"Russell Fields" wrote in message

> 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
>
>
> "King Coffee" wrote in message
>
>> 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
>
 >> Stay informed about: SQL Sever Access Error