If you SQL Accounts on your database (not something that I would recommend in production) and you are restoring databases you are proberbly aware of the following above error.    Now if you are like me the first thing that you do is check to see if the user exists in the database.  If you are restoring the db, most of the time it does.   It then takes a couple of seconds for me to remember that if you are restoring a database from a different server then the link between the SQL Server account and the User in the database doesn’t exists.  So even when you open up the database and see the user you cannot still login.   The way you normall fix this is by deleting the user from the database, then going back to the user underneath SQLSecurity and granting access to the database for that user again.  After doing this a couple of times I got sick of it and created the following script. 

DECLARE @USERNAME varchar(8000)
SELECT @USERNAME = 'fred'
if not exists (select * from dbo.sysusers where name = @UserName)
BEGIN
    exec sp_grantdbaccess @USERNAME , @USERNAME
    EXEC sp_addrolemember 'db_owner',@USERNAME
END
ELSE
BEGIN
    EXEC sp_change_users_login 'Update_one',@USERNAME ,@USERNAME
END

What this does is grant access to the current database to the given username specified and then adds then to the role db_owner.   I have another script which will restore the database as well after I’ve santised it I’ll post it.

Advertisements