August 15, 2007 at 9:32 am
In SQL 2000, we created a user (other than 'sa') that had dbo rights to create databases and administer the databases so that we did not use the 'sa' account. I wanted to do the same in SQL 2005 so I created such a user ('XXX') and added the user to all of the appropriate server roles, logged in as that user into Mgmt Studio, created a new database and set the owner of the new database = 'XXX'. However, when I look at the properties (New Database -> Security -> Users -> dbo -> Properties). I see that the "Login" for 'dbo' is set to 'sa'. Why is this?
Thanks for your help in advance (I am still getting feet wet in SQL 2005).
Norene Malaney
August 15, 2007 at 2:37 pm
OK, I at least changed the login of "dbo" by running sp_changedbowner. However, I still don't understand how SQL determines what the login for "dbo" will be. I also noted that after I restored the database, it set the login of "dbo" to my network windows account (even though I restored the db logged in as a SQL user using SQL authentication). Just curious if anyone knows why this happened.
Norene Malaney
August 16, 2007 at 3:21 am
Our standard is to rename the sa account. Use ALTER USER sa WITH NAME = 'something'. This makes it impossible for anyone to log on using an account name of sa.
However, it does not stop someone who has access to the server from finding out what account name is associated with a SID value of 1, and then trying to log on with the new account name.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply