September 10, 2007 at 6:48 am
Does anyone have any feedback on how to remove the 'dbo' Db role from the 'BUILTIN\Administrators' account?
I recently inherited some SQL Server 2000 instances which had the 'BUILTIN\Administrators' account defaults (sysadmin role) but it also is the 'dbo' for all the databases on the instance.
I've tried to drop the 'dbo' database role but get the classic error message;
Error 15405: Cannot use the reserved user or role name 'dbo'
I've also tried to use the sp_changedbowner system SP to assign 'dbo' to another login, which works, but its still does not allow me to drop the 'dbo' Db role from the 'BUILTIN\Administrators' account.
I feel like I'm missing something obvious here. Any help or suggestions would be greatly appreciated. Thanks.
September 10, 2007 at 8:40 am
sysadmins alias to dbo.
Remove the builtin_admins from the sysadmin group.
September 11, 2007 at 3:48 am
Our policy is that all DBAs are members of a specific Windows domain group. We make this group a member of sysadmin, and then we remove the BUILTIN/Administrators account. We have not had any issues from doing this.
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
September 11, 2007 at 5:24 am
Steve Jones, you wrote;
sysadmins alias to dbo.
Remove the builtin_admins from the sysadmin group.
I already removed the sysadmins role from the BUILTIN\Administrators login but this Windows group still has rights to each of the databases on the instance (at the dbo level too). How can I prevent the access at the Db level if I can't remove the dbo Db role? I could deny the BUILTIN\Administrators login the right to login but I'd really like to remove the access at the Db level. This goes back to my original post on isssue with removing dbo. Any advice? Thanks.
September 11, 2007 at 7:14 am
Brad,
You've got it a little backwards. The dbo role is a predefined database role for each individual db that CANNOT be removed. The role maps to the db_owner rights. If you want a specific login to no longer map to dbo, access the properties of dbo, and remove that login from the role. It is the login that belongs to the role, not the role belonging to the login.
CAUTION - If you remove the BUILTIN/ADMINISTRATORS login, make sure that there is some other windows identity that has a login mapped to dbo.
Hope this helps.
September 11, 2007 at 2:14 pm
Check to see who the owner of the databases in question are. If the owner is the BUILTIN\Adminstrator group, the ownership of the database may need to be transferred to a different user (sa or a windows user) that is a member of the system administrators role. I'm not sure but any objects (tables, views, etc.) owned by them would probably need to be changed too.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply