SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Removing the Builtin Administrators - Some Pitfalls to Avoid


Removing the Builtin Administrators - Some Pitfalls to Avoid

Author
Message
Steve Bergkamp
Steve Bergkamp
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 222

Kathi:

Great Article. Thanks.

I am interested in your login handling job........

"...The way I accomplished this was by adding the accounts and required access start and end dates to a table. My script grants or removes logins based on the accounts listed and dates. That way, when I need to give access to someone temporarily, which happens frequently, all I have to do is add the account and dates to the table and forget about it. ..."

I think this would be useful for a lot of reasons. Have you ever written this up? I think it would make a great article.

Thanks.

Steve B.


Kathi Kellenberger
Kathi Kellenberger
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 347

Steve,

I'm glad you liked the article. Sure, I could write an article on this. However, I am very bad in that I am pulling data from system tables. Maybe I can rewrite my proc for SQL 2005 and include both in the article.

Kathi



Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Larry Aue
Larry Aue
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 668

"Via Group Membership" is interesting to me. I've wondered if it happens when someone has db_owner rights via a Windows group and then creates an object. You can give a group access to a database, but I bet when they create an object under their owner, then it needs to create the login and uses "via group membership" to denote that you didn't add it yourself.

When you remove BUILTIN\Administrators group and your server/network people want to take the box down for maintenance, do they call you to stop SQL gracefully, or do they just pull the plug?





Rudy Panigas
Rudy Panigas
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2392 Visits: 1322
Has anyone just disabled the Buitlin\Administrators account? That way you can test and if something breaks you can enable it and correct the issue. And if nothing is broken you can just leave it disabled until you actually need it.

Just a thought



John M Dennis
John M Dennis
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 432

Hi Grasshopper,

There is no "Disable" but "Deny" will certainly work for testing purposes. I would eventually delete though or someone may come along a grant access. In our Corp environment there are well over 100 users with some nested privilage under the Builtin\Administrators account so our policy is to setup SQL services with domain accounts, add the DBA group as sysAdmin, and then delete the Builtin\Administrators account after every setup of a new server.

John D


CGSJohnson
CGSJohnson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 1686
So, I didn't read these type posts before deleting the Builtin\Admin group. Now our third-party backup process is crying for failure to login as "NT Authority\SYSTEM". Should I just re-add the Builtin\Admin or just give sysadmin rights to nt authority\system or is it pretty much the same? Thanks.

Chris
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (24K reputation)

Group: Moderators
Points: 24702 Visits: 1917
Add NT Authority\SYSTEM and grant it the appropriate rights. You probably have netbackup or the like running its agent service as the local system account. That is preferable to re-adding BUILTIN\Administrators if you can help it.

K. Brian Kelley
@‌kbriankelley
CGSJohnson
CGSJohnson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 1686
Great! Thanks, Brian.
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29615 Visits: 8986
Christopher G.S. Johnson (6/2/2008)
So, I didn't read these type posts before deleting the Builtin\Admin group. Now our third-party backup process is crying for failure to login as "NT Authority\SYSTEM". Should I just re-add the Builtin\Admin or just give sysadmin rights to nt authority\system or is it pretty much the same? Thanks.

Chris


maybe you'd be better off making the tools service account a registered "backup admin" for your instance, in stead of the spooky builtin set.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
John M Dennis
John M Dennis
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 432
As ALZDBA points out--It is better to create a windows account for the service to run under than NT Authority\SYSTEM. Granting permission to NT Authority\SYSTEM gives "any" service or application, running under that context, the same rights to the SQL Server and there is even less tracability than BUILTIN\Administrators.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search