September 9, 2013 at 6:01 am
Hi,
We have a prod server that is 2005. Then we have a dev server that is 2008R2.
We copied over the DB's from 2005 to 2008 worked fine. But then one of the BA's 'accidently' deleted all the users on the 2008 dev server.
I wasn't even able to login using my account. So, I logged in to the server as admin and I'm able to see the db's.
I went into SSIS and transferred all the logins from 2005 to the 2008R2 server.
All the users look to be there, but I still can't login as my user. I've checked and my user is there and enabled..? What can I check next?
September 9, 2013 at 7:24 am
Are these logins Windows logins or SQL logins?
When your login fails, what do you see in the error log? There should be the same error message as you get when you log in, but there is a state number which gives further information why the login fails. (There are blog posts out there explaining these state numbers, but where I sit right now, I don't have a URL.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 9, 2013 at 7:37 am
Thanks Erland. I think I have it figured out.
I used this:
http://support.microsoft.com/kb/918992
And it worked, but much to my surprise it didn't transfer everything correctly. It looks like it transferred all the users but some of them it didn't give them access to some DB's..? Weird.
It got like 90 percent correct. How can I fix this going forward?
September 9, 2013 at 7:42 am
Maybe it's the issue of non-matched SIDs?
You can use the proc sp_change_users_login to find out: http://technet.microsoft.com/en-us/library/ms174378.aspx
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
September 11, 2013 at 11:14 am
Andreas Wolter-332291 (9/9/2013)
Maybe it's the issue of non-matched SIDs?You can use the proc sp_change_users_login to find out: http://technet.microsoft.com/en-us/library/ms174378.aspx
Now that's a cool procedure. In the years I've worked with SQL Server, I've never heard of it. Too bad it's flagged for removal in a future version.
I just went through an exercise where we're moving a bunch of database from an old server to a new one. I encountered the whole SID-mismatch problem. I ended up building a list of all the permissions, dropping the user, recreating the user and then assigning all the permissions. It was a complete pain. I have yet to see if I caught everything. So far, I have.
September 11, 2013 at 1:59 pm
Oh yes, it's a really important proc.
And it's there since V2000 at least I think 😀
Yes, ALTER LOGIN is supposed to be the replacement, but it does not do the checking.. I have no news on that.. I think the'll gonna leave until all aspects are covered one way or another..
guess we are expected to just ask the DMVs for that
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply