Click here to monitor SSC
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 Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 341

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-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 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
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
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 (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 417

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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 1682
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 (6.8K reputation)

Group: Moderators
Points: 6820 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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1914 Visits: 1682
Great! Thanks, Brian.
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6978 Visits: 8839
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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 (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 417
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