aaron_martone — 2013-01-04T10:48:51-05:00 — #1
Let's say I have a DB with 2 users on it. If I backup that database and move it to another server that has logins with the exact same name, I note that when I restore the database, the user logins still exist under the security of the database, but I have to manually remove them, and then go into the server's security logins section and setup the logins to have rights again to the restored DB.
Is there a way to minimize this or is it natural that the DB says "These logins might HAVE the same name, but since they exist between 2 different servers, they are not actually the SAME user, and you'll have to do what you're doing in order for the logins to work on that restored location"?
gk53 — 2013-01-04T11:30:42-05:00 — #2
you can create script and just run after restore database on different server
sieuraovat_net — 2013-01-07T01:51:05-05:00 — #3
i think you should remove login in database fist and recreate login
database -> security -> user
sorry for my English.
wwb_99 — 2013-01-07T18:56:04-05:00 — #4
Yes, it is natural in an MSSQL sense -- logins are actually identified by SIDs not names. The logins don't exist in the database, they exist on the server so anything granted to them dies with the login you failed to move. A better pattern for MSSQL database security is to use database roles and assign the permissions to them. Said roles live with and travel with the database. So, when you have a restore scenario like you are talking about alls you need to do is create appropriate logins and put them in the right groups.