This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi, I would like to know if could be some problem leaving a DOMAIN\USER that not exist more like DB owner.
I saw that exist cases in some DB in different instances.
Alen, Italy
@Alen Cappelletti Sorry could not understand your question. So there is domain login and it is not used now and you want to remove it ?
Hi, some DBs have "db_owner" a USER/LOGIN (sysadmin) that from few weeks it will be cancelled from the company domain ('cause he leaving).
In the meantime I try to remove this connection and replace with a "sa" db_owner.
My question is if the eventuality I could miss some updates o fixes, in a case of detach/attach or some other DBA activity the link with this "old" user could be dangerous for some administrative activities on DS's?
THANKS Alen
It would be best to change database owner (database authorization) to a valid account before removing the account from the domain. I suggest a SQL account for database ownership to avoid the external dependency.
There are some functions in SQL like CLR Assembly loading that will fail to work if the owner is not there. Anything in the database on startup dealing with Security will fail if the DB Owner user is no longer valid or missing.
Just thinking what would need database owner permission when database starts ? I have never seen DB not coming online because it has no owner
On the security side, if there is no db owner then the CLR assemblies will NOT load and the database will fail to come online. Not every database uses SQLCLR so many have not come across it. I have come across it a lot in the orgs I have been in because they loved SQLCLR.
Ohk since you mentioned any security related thing during startup I was curious as what that could be.
Thanks
Hi @Alen Cappelletti ,
I would like to know if could be some problem leaving a DOMAIN\USER that not exist more like DB owner.
That is because the domain\user not be added into db_owner role.
Domain user and DB owner, they are different concept level.
Domain user:Damain user is the Active Directory account created under your company domain;
Db_owner:one fixed database role can perform all configuration and maintenance activities on the database;
If my understanding is correct, you want to add one domain user as db_owner, try steps as next(please change database name and domain\account to yours on your side):
use test1021
Go
--STEP1:Creating a login from a Windows domain account(if you have already created,skip this step)
CREATE LOGIN [YX\TEST2] FROM WINDOWS;
--STEP2:Add that user as a login to the database(if you have already created,skip this step)
CREATE USER [YX\TEST2] FOR LOGIN [YX\TEST2]
--STEP3:Add that user to the db_owner
EXEC sp_addrolemember N'db_owner', N'YX\TEST2'
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.
Hi and thanks for your response...
maybe my question isn't very clear... I try to rephrase it...
: )
I have a database whose DB_OWNER is a domain user which will be deleted in a few weeks.
If I don't replace it in time (for example with "sa"), can it lead to some malfunction once canceled?
My question is if the eventuality I could miss some updates o fixes, in a case of detach/attach or some other DBA activity the link with this "old" user could be dangerous for some administrative activities on DS's?
No, this would not be dangerous. You would be able to perform task on database even if its owner no longer exists. I dont have thorough list as what could go wrong but I had seen such situation and frankly the database just worked fine. Like @Dan Guzman mentioned change it to valid account or SA ( for time being) .