November 1, 2010 at 1:50 pm
Here is the story:
We have a production box and a Dev box. We refresh the Dev from production when the data needs to be more up-to-date. After the refresh I have to reissue security after the refresh because some of the logins are not in production. I use:
exec sp_addrolemember 'db_datareader', 'Domain\Name'
exec sp_addrolemember 'db_datawriter', 'Domain\Name'
to reissue the security for the domain accounts. This does not work (I am not sure why that is why I am asking this question) It creates the user's account in the right sql instance and it gives them the rights to read and write but it does not work when the user logs in. When I go to security in SQL under User Mappings the box is not checked for that sql instance but if you click on it, it shows that the datareader and datawriter is checked.
To fix this I have the delete the user's account out of the sql instance and manually check the instance in User Mapping and check datareader and datawriter and it will start working...
What am I doing wrong...
Thanks for your help
Scott
November 1, 2010 at 2:14 pm
Sounds like you have different SIDs for the user.
In each database, run sp_change_users_login 'report' - this will show you all of the windows users that have different SIDS.
You might want to check out the sp_help_revlogin stored procedure on Microsoft's site to script out the login from prod. You can then add it to dev with the same SID, which will prevent this issue.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply