Adding the 'sa' login as a new database use 1

[Apollo21]

Good Day!

SQL Server 2000 SP4 running on Windows OS 2003:

I have recently restored a database from server A to server B under a new Name of Database1. After the restore I attempted to add the 'sa' login to Database1 as a user. The system will not let me. It gives the following message;

'Can not use the reserved user or roll name 'sa'.

I thought I had added 'sa' prior to today to other databases. Can you tell me what I am missing?

Thank you in advance for your help.
Apollo21

 

[ptheriault]

You don't add the 'sa' account to a database. By default the sa account has access to all databases. I would run..

sp_change_users_login 'REPORT'

to see what login SIDS are out of sync.

You can fix them with...

sp_change_users_login 'update_one', 'login', 'login'

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Apollo21]

Ok, Thanks for the response.
I ran the report and I got a result like this;

dbo 0xE1BDA9674BD4E44385DFFF509DE57C8C

Does this mean I have to link the dbo to a user account under this new database?

 

[ptheriault]

Yes,
But you can't fix the dbo account with sp_change_users_login

I would script out the dbo account, drop it and re-add it to the database.



- Paul
- If at first you don't succeed, find out if the loser gets anything.