snap manager for sql - clone permissions issue

[FREDITSI]

Hi,

i seem to have an issue with security accounts in sql when cloning databases.

we are using snap manager for sql to create a snap shot and then clone the database to a different server.  if a user is specified on the source server on the database (mapped user to that database) they can access the database without any issues (makes sense).

As soon as the clone happens at night it attaches to a different server.  If i then look at the cloned database on the new server i can see the login account that is on the source server.  if i try to login using sql management server (sql auth) it fails.  If i re-map the account to the database then it works fine until the next night which means i have to reset the permissions every morning.  The only way around this is to assign to the account sys admin on the sql server whhich is not very secure at all.

any suggestions on a work around or if i am missing anything?

Thanks

Phill

[ogra]

It is not completely clear to me.

Are you saying once you try to login to the clone database you are not able to login with the source DB account ?

[FREDITSI]

yes.... the source server has an sql account associated with it.  when a clone happens it takes all the security permissions with it so i gather... if i then try to use the same account on the cloned database it fails.  i have to go into sql manager . select the account > map the account to the database with permissions > then works....... at night clone occurs and it fails in the morning even though the account is still mapped to the database that is cloned

[ogra]

What is your Clone schedule ? ( How often you refresh your DB as clone to the other server ) ?

I will suggest to open a case for this. If this occurs outside the clone schedule.

[FREDITSI]

it clones every 24 hours so every 24 hours i have to re-map the account.  is that how it should work?

[spat]

SQL Server always create SID in master database for every login.  If you use SQL authentication, SQL Server will create a unique SID for that login.  This SID is unique for every servers.  That is the reason why your SID for login in master database is not match with SID in user database after the restore or clone process.  The easiest way to fix this is to create a login and specify SID that is the same as your source database instance.  Here is how you would do it.

1. Find the SID of the login from the user database

     use UserDB

     go

     select SID from sysusers where name = 'login_name'

     copy the SID from the query result

     2. create login login_name password 'pwd' with SID = 'put SID that you copy here'