dr_dudd
asked on
SQL Server 2000 Cannot Grant Access to Recreated User
I know its old, and yes we are upgrading to SQL Server 2008, but in the meantime I have this problem.
When I try to grant access to a database using an AD user that was deleted and recreated (they left the company and came back) SQL allows me to add the user, but they get "Access Denied" when they try to access it. I believe that SQL is somehow either linking to the old AD account GUID, or maybe the delete process doesn't quite get rid of the old one. Anyway I need to be able to grant this returning user access to teh DB, any ideas?
When I try to grant access to a database using an AD user that was deleted and recreated (they left the company and came back) SQL allows me to add the user, but they get "Access Denied" when they try to access it. I believe that SQL is somehow either linking to the old AD account GUID, or maybe the delete process doesn't quite get rid of the old one. Anyway I need to be able to grant this returning user access to teh DB, any ideas?
Aneesh
http://askmesql.blogspot.com/2009/05/how-to-fix-orphaned-sql-server-users.html
ASKER
Thanks for the very fast response, but I am afraid it didn't work - No orphaned users.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the comment mastoo, the bit about "added the domain user at the sql server level and specifically gave access to the database of interest?" led me to the answer... when the person left they were removed from the database, but their login to the SQL instance was not deleted. When they returned an the login was recreated SQL tried to link the old login and of course it didn't work! Deleted the SQL instance login and the DB login and recreated the user and it worked.
ASKER
Not quite the answer, but it led me to the solution.