We help IT Professionals succeed at work.

SQL Server 2000 Cannot Grant Access to Recreated User

dr_dudd
dr_dudd asked
on
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?
Comment
Watch Question

AneeshDatabase Consultant
Top Expert 2009

Commented:

Author

Commented:
Thanks for the very fast response, but I am afraid it didn't work - No orphaned users.
Commented:
The user likely just didn't get recreated with the right permissions.  You added the domain user at the sql server level and specifically gave access to the database of interest?  Is the Access Denied happening on the initial connection or on trying to run something?  If the latter, you need to grant read, execute, etc. permissions directly or via roles to allow access to the objects involved.

Author

Commented:
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.

Author

Commented:
Not quite the answer, but it led me to the solution.