SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleted all users


Deleted all users

Author
Message
New_at_this
New_at_this
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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?
Erland Sommarskog
Erland Sommarskog
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4978 Visits: 875
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
krypto69
krypto69
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2500 Visits: 2479
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?



Andreas.Wolter
Andreas.Wolter
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1056
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
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45361 Visits: 10835
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
Andreas.Wolter
Andreas.Wolter
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1056
Oh yes, it's a really important proc.
And it's there since V2000 at least I think :-D
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search