January 24, 2008 at 3:21 am
Hi,
I have deleted the SQL 2005 database user from the Active directory. Account is recreated in AD, but I can not connect it to its old db user account in SQL SM Studio. Can anybody help me?
January 24, 2008 at 5:37 am
There could be a number of reasons for your problem. To troubleshoot, please follow this link: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1186920,00.html
Paul
January 24, 2008 at 6:18 am
I've had similar problems and never really understood the root cause. But when we would delete/recreate an account or renamed an AD account, the account would no longer be valid in SQL. Simply recreating the account worked without issue though.
January 24, 2008 at 6:50 am
I presume you are suggesting that creating a new account in AD does not present a problem, but recreating a deleted account does?
My AD knowledge is a bit flaky, but could deleting an account in AD (assuming a SQL account) also deletes the same account in SQL Server? If so, then this is a SQL Server issue and is related to the SID of the account.
Does anyone else have an explanation?
Paul
January 24, 2008 at 6:52 am
My apologies, I meant recreating the account in SQL Server.
I do believe you're correct about the SID though. An NT account in SQL server does in fact receive a SID, but until I experienced those problems, I never realized that they do have some tie in with a SID in AD.
January 24, 2008 at 7:10 am
It is because when you recreate user account in AD, it has different SID. And it is SID that is used during authentication process. You have to drop and recreate login on SQL Server I think.
...and your only reply is slàinte mhath
January 24, 2008 at 7:16 am
If you recreate account in SQL you will loose all relations in connection with the DB.
January 24, 2008 at 7:19 am
zurikela (1/24/2008)
If you recreate account in SQL you will loose all relations in connection with the DB.
Correct, but there are numerous scripts provided here on SSC that will script out all appropriate permissions for you before you recreate it.
January 24, 2008 at 7:21 am
Could you provide me with one of those scripts?
January 24, 2008 at 7:30 am
Ever since SSC changed their site, I can never seem to find the scripts I'm looking for anymore using searches ... I looked for a few, didn't come across them. I know there is at least 2 good scripts on here that will assist you in scripting out all appropriate permissions for a specified login.
January 24, 2008 at 9:05 am
Try searching this site under 'orphaned users'.
Paul
January 24, 2008 at 9:09 am
Ah good point ... I was thinking because a new login has to be created, that the user could not be re-applied to the login without manual intervention.
But with sp_change_users_login, you can specify the user and the login ... still though, that would be messy moving forward having a database user mapped to a different login depending on how they do things there.
January 24, 2008 at 9:30 am
Which SSC site do you mean?
January 24, 2008 at 9:42 am
zurikela (1/24/2008)
Which SSC site do you mean?
This site ... sqlservercentral.com
January 25, 2008 at 4:03 am
Hi
Create a login for the newly created domain account.
use sp_change_users_login to connect this login the EXISTING db user account.
"Keep Trying"
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply