October 2, 2003 at 2:14 pm
I have a situation that is baffling me completely... There are many facets to it, though. Here we go.
Here's the first facet:
Lets say you have a SQL server registered on DOMAINX (an NT Domain). Lets say you have a user also regisered on DOMAINX, we'll call him USERX. Some time ago, I created a Windows Authentication account called DOMAINX/USERX, and gave it database creator rights. He created a database called USERXDB, and imported some stuff into it from and MS Access database. Any tables that were imported from Access have an owner of USERX (not DOMAINX/USERX). But if that user just creates a table from scratch, the owner shows up as "dbo". That's the first thing that I don't understand.
The second facet:
As long as USERX is the owner of that table/proc/whatever, I cannot delete DOMAINX/USERX from the logins list. Furthermore, I can't figure out how to "transfer ownership" of these objects to a different user, so that I CAN delete the user from the list.
And now, the third part:
DOMAINX/USERX is no longer valid, because DOMAINX has been retired in our migration to Active Directory (jeez). So at this point, I need to create an account that's called just USERX (without the domain tag), which I did. I then tried to assign permissions to this user which included USERXDB, but a message came back saying that "User 'USERX' already exists", assumedly because it still exists in the old DOMAINX/USERX account that I am unable to delete. But since the user can no longer get to their old account, they cannot access the database they created with it.
Thought Process:
I could simply change the new user account to something other than USERX, but I'm not willing to do that, if for no other reason than the fact that it shouldn't be necessary. Regardless, I need to stick with my naming conventions. The question then becomes, how to reassociate ownership of an object within a database to a different user (or to dbo) so that an obsolete user can be deleted?
October 2, 2003 at 2:36 pm
quote:
Here's the first facet:Lets say you have a SQL server registered on DOMAINX (an NT Domain). Lets say you have a user also regisered on DOMAINX, we'll call him USERX. Some time ago, I created a Windows Authentication account called DOMAINX/USERX, and gave it database creator rights. He created a database called USERXDB, and imported some stuff into it from and MS Access database. Any tables that were imported from Access have an owner of USERX (not DOMAINX/USERX). But if that user just creates a table from scratch, the owner shows up as "dbo". That's the first thing that I don't understand
He is the member of 'db_owner' database role. He can create object owned by himself without specific object owner during the creation. Or he can also create object owned by DBO by specific object owner 'dbo' during the creation.
quote:
The second facet:As long as USERX is the owner of that table/proc/whatever, I cannot delete DOMAINX/USERX from the logins list. Furthermore, I can't figure out how to "transfer ownership" of these objects to a different user, so that I CAN delete the user from the list
As long as it owns objects, you can't delete the login. What you can do is change the db owner and objects he own to another uses. Use sp_changeobjectowner to change the owner of an object if the security account that owns the object has to be dropped but the object must be retained. This procedure removes all existing permissions from the object. You will need to reapply any permissions you want to keep after running sp_changeobjectowner. sp_changedbowner will change the database owner.
quote:
And now, the third part:DOMAINX/USERX is no longer valid, because DOMAINX has been retired in our migration to Active Directory (jeez). So at this point, I need to create an account that's called just USERX (without the domain tag), which I did. I then tried to assign permissions to this user which included USERXDB, but a message came back saying that "User 'USERX' already exists", assumedly because it still exists in the old DOMAINX/USERX account that I am unable to delete. But since the user can no longer get to their old account, they cannot access the database they created with it.
Once the login deleted from second step, user USERX shouldn't be in your database anymore. If it still there, you have to drop it either from EM or sp_dropuser. After drop the old user, re-grant newly created login to access your database with proper permissions.
quote:
Thought Process:I could simply change the new user account to something other than USERX, but I'm not willing to do that, if for no other reason than the fact that it shouldn't be necessary. Regardless, I need to stick with my naming conventions. The question then becomes, how to reassociate ownership of an object within a database to a different user (or to dbo) so that an obsolete user can be deleted?
You have to assign objects to another user/dbo and obsolete user can't own any objects, otherwise, you can't delete it.
October 2, 2003 at 3:10 pm
Excellent. That did the trick. But is there anything I can tell these guys to do when importing from access to prevent their id's from being specifically associated with objects they import? Has anyone ever seen this before?
Thanks!
October 2, 2003 at 7:31 pm
In the DTS import wizard --> select source tables and views --> change the object owner to 'dbo' in destination column, the table you import will be owned by 'dbo'. Assuming the login you used is member of 'dbo' database role.
If the login is member of 'sysadmin' server role, the table will be imported and belongs to 'dbo' automatically.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply