Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Restricting SecurityAdmin on SQL Server 2005/2008 Expand / Collapse
Author
Message
Posted Wednesday, September 1, 2010 8:10 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
Comments posted to this topic are about the item Restricting SecurityAdmin on SQL Server 2005/2008

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #979248
Posted Wednesday, September 1, 2010 8:47 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
Thanks for demonstrating this vulnerability.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #979253
Posted Thursday, September 2, 2010 1:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:04 AM
Points: 89, Visits: 294
Excellent article!

Clement
Post #979329
Posted Thursday, September 2, 2010 3:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 4:08 AM
Points: 64, Visits: 740
Excellent, informative and slightly scarey - all at once!

Post #979379
Posted Thursday, September 2, 2010 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 6:23 AM
Points: 1, Visits: 30
Thanks for raising the awareness of this behavior!
Post #979475
Posted Thursday, September 2, 2010 10:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:11 PM
Points: 31,368, Visits: 15,837
Excellent article and I noticed this on your blog recently and was concerned.

It seems like this is a bug since essentially the securityadmin role now has no real meaning. You might as well be a sysadmin or not have this at all.

I would love to see a server level role that allowed someone to add a login, and a user for a specific database (s) only. That's the type of permissions that I often want to hand over to another person.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #979676
Posted Thursday, September 2, 2010 10:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 30, 2012 12:36 PM
Points: 404, Visits: 442
Excellent article.

I have one question about the workaround. If a person has SecurityAdmin, could they give themselves permission to alter the LimitSecurityAdmin trigger?

Chris
Post #979713
Posted Thursday, September 2, 2010 11:09 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
croberts 36762 (9/2/2010)
Excellent article.

I have one question about the workaround. If a person has SecurityAdmin, could they give themselves permission to alter the LimitSecurityAdmin trigger?


No. As a securityadmin, you cannot assign permissions to your own login. But that makes me think there's another attack vector that I need to test.

Steve, I would agree with you, but Microsoft was adamant this isn't to be considered a bug. And to consider securityadmin = sysadmin. However, I know folks who've converted and have controls in place assuming securityadmin is limited, so they're stuck in the middle. I wish they would consider it a bug, too, because as Chris just brought up, there are surely more attack vectors.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #979727
Posted Thursday, September 2, 2010 12:16 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
It is definitely good to have this pointed out since a lot of people do not realize it, and this was well written and clear.

From my standpoint, it tends to be irrelevant. Even if they cannot take full control of the server someone with even the SQL Server 2000 limited version of SecurityAdmin could cause so much mischief I would never hand it to someone I would not trust with full control server. At that point, I see the value of it in keeping honest people honest. Even if they know how to bypass it easily, they are faced with the fact that they are bypassing it. This reminds them that they are doing something that is properly in someone else's domain. For an trustworthy person, that is enough; for a non-trustworthy person even limited SecurityAdmin is far too much power.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #979799
Posted Thursday, September 2, 2010 12:21 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
timothyawiseman (9/2/2010)
It is definitely good to have this pointed out since a lot of people do not realize it, and this was well written and clear.

From my standpoint, it tends to be irrelevant. Even if they cannot take full control of the server someone with even the SQL Server 2000 limited version of SecurityAdmin could cause so much mischief I would never hand it to someone I would not trust with full control server. At that point, I see the value of it in keeping honest people honest. Even if they know how to bypass it easily, they are faced with the fact that they are bypassing it. This reminds them that they are doing something that is properly in someone else's domain. For an trustworthy person, that is enough; for a non-trustworthy person even limited SecurityAdmin is far too much power.


Agreed, to a point. From a Principle of Least Privilege perspective, even if you trust someone to be a sysadmin, but they only should be doing the work of a securityadmin, you give them securityadmin. Only the permissions to do the job - no more, no less. And that's where this really busts audit controls.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #979807
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse