Doug Krawec (12/18/2007)
I've noticed that when I Restore a database (in my case to a different machine) that the security settings that were in place on the original database are not replicated on the restored database. In particular, the SQL Server logins do not seem to work. Does anyone know what is happening here and if there is a work around to maintain the original security settings.
doug,
the sid in master..syslogin has to be re-created on your restored server if it's not there, the db.dbo.sysusers will have the sid because it's part of the backup.
if you are using sql authentication, then you may have to update dbname.dbo.sysusers sid to match master.dbo.syslogins, because sid will be different from one server to another, but using windows authentication sid will come from windows domain or group, it will not change, it's just a matter of creating the windows group or user on the restored users.
have a look on the result of
select * from master..syslogins
and
select * from yourdbname..sysusers