March 14, 2005 at 6:30 am
If someone logs on and is a member of the local admin group I beleive they have privileges to log on to SQL Server thru BUILTIN\administrator.
When they do log on to SQL Server what user information appears in current activity?
Does it appear as BUILTIN\administrator or does it appear as their Windows login? I think it appears as their Windows login. Is this right?
If so, how do I audit use of the BUILTIN\administrator login when I don't have control of or know who is a member of the local admin group.
March 14, 2005 at 3:29 pm
It's not BUILTIN\Administrator but BUILTIN\Administrators. The s at the end is important. That means it's the local Administrators group. When a user logs in and gains access through BUILTIN\Administrators, it appears as that user's domain\username login. So, for example, if I was a member of the SSC domain and my user account was bkelley, it would appear as SSC\bkelley.
You can audit for all successful logins and then extract who is a member of the BUILTIN\Administrators group. One way to find out this information, provided you have sysadmin rights in SQL Server is to run the following:
EXEC xp_cmdshell 'net localgroup administrators'
net localgroup is a command that can be run from the command prompt. Using xp_cmdshell is one way of running it on the server. Another option is to use Computer Management (compmgmt.msc), point to the server, and browse the members of the Administrators group.
K. Brian Kelley
@kbriankelley
March 14, 2005 at 11:08 pm
Probably you know that it is recommended to delete the account BUILTIN\Administrators from SQL Server?
But, sadly, this is not always possible.
In that case you can investigate if it is possible to give the group less rights, of even delete members from the local administrators group so only a few or only one account has access to the database server.
March 15, 2005 at 6:40 am
Our standard is to have a windows domain group holding the DBA accounts, and give this group Sysadmin authority in SQL. Having done that we delete the BUILTIN/Administrators entry. We also use NT-Only security on most of our SQL instances, except where 3-rd party products require the use of SQL accounts.
We have had no issues with 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
March 15, 2005 at 7:17 am
Removing the BUILTIN\administrators group is considered a best practice. However, there are some things to be aware of with Full Text Indexing and with clustered SQL Server instances. There are a couple of articles on this site that cover what to do and what to look out for.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply