Dear Support,
We have a dts job that copies our database backups and restores to our Reporting Database.
The database naming convention remains the same.
I was asked to setup a new user ID on production which I did, they need to access the information from the report server. the user Id was copied across from production via the dts job.
The user Id was also set up in Report Server in security logins.
On the report server in Server Login Properties this user loses access to the databases he is trying to report on.
What am I doing wrong here?
of course each time I select you get the error 21002:[SQL-DMO]User 'userxxx' already exists.
and I have to run the script to correct that, but I cannot be doing this everyday.
I notice in the reporting database under users, the user in question has no login name.