Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleted all users Expand / Collapse
Author
Message
Posted Monday, September 9, 2013 6:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 22, 2013 1:17 PM
Points: 5, Visits: 12
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?
Post #1492741
Posted Monday, September 9, 2013 7:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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.)


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1492773
Posted Monday, September 9, 2013 7:37 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:31 AM
Points: 706, Visits: 1,751
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?




Post #1492784
Posted Monday, September 9, 2013 7:42 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:14 AM
Points: 153, Visits: 980
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
Post #1492785
Posted Wednesday, September 11, 2013 11:14 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 4,229, Visits: 3,662
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1493812
Posted Wednesday, September 11, 2013 1:59 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:14 AM
Points: 153, Visits: 980
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
Post #1493890
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse