June 30, 2020 at 10:50 pm
EDIT: Upon further research, it looks like this is a bad question. I 'replied' below with an explanation of why. I would delete this question if I knew how.
--------------
We are migrating from SQL Server 2008 R2 to SQL Server 2019. A consultant did the initial work of setting up the new server, and now it's my turn to do the final part of the migration. In doing general research on best practices, I found that the Login for the "dbo" user in *User* databases should not be an individual account nor the sa account. At least that's what I have gathered.
What I'm wondering about is the 'dbo' user for the *System* databases. The consultant set the Login for the system dbo user to 'sa' (or maybe 'sa' was the default?). Using 'sa' seems to be discouraged, but the consultant also disabled the 'sa' Login and I've seen positive remarks about using a disabled account for the Login belonging to the 'dbo' User.
The Question: Is using a disabled 'sa' account for the 'dbo' user for System databases a secure strategy? Are we likely to experience any problems doing it this way? Is there a different industry standard?
As an alternative, I was thinking that I could re-set the Login for the dbo users in the system databases to the Login I'm using for our User databases. For our User databases, the dbo user is mapped to a Window's Authenticated Login. The Window's Authenticated Login is mapped to a *group* Active Directory account, to which myself and other DBAs/programmers belong. Would it be better to switch the dbo's Login for system databases to the same group Active Directory account we are using for the User databases? Or maybe it doesn't really matter?
I'm more of an accidental DBA, because there's no one else to do it. I apologize if this is a dumb question. I really did try to research it, but I couldn't find anything mentioning the dbo's of System databases and the current mapping to the 'sa' Login doesn't feel right to me. Thank you for your thoughts. 🙂
July 1, 2020 at 4:36 pm
This may have been a bad question on a couple fronts. Can it be deleted?
I found another discussion that says using 'sa' is not a terrible choice and you can't use a Windows group Login for the dbo User. I just found that out when I got the following error giving it a try:
"An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys."
The following discussion (that I just found) talks about the various options and about why one would or would not use 'sa.'
If using a disabled 'sa' account is not great, but is acceptable, then I might as well use that for all the databases??? I don't know what is best, but it appears that the original question above is irrelevant.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply