Running as SysAdmin

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Running as SysAdmin

44 posts, Page 1 of 5

1 2 3 4 5 »»»

Running as SysAdmin

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Tuesday, May 03, 2011 9:09 PM

SSCertifiable

Group: Moderators
Last Login: Tuesday, June 11, 2013 6:34 AM
Points: 6,463, Visits: 1,388

Comments posted to this topic are about the item Running as SysAdmin

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #1102830

Ninja's_RGR'us

Posted Tuesday, May 03, 2011 9:16 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 21,376, Visits: 9,584

Given to 8 consultants with RDP access from outside the network.

Then when I changed the pass after your possible attack warning last week I got an earfull from the network admin... even if nothing was lost, security tightened AND a real threat eliminated.

Oh ya and this is the SA password for the ERP db. You know the one with all the personnal info including bank accounts.

Post #1102833

JMartin-392745

Posted Tuesday, May 03, 2011 9:38 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:15 AM
Points: 103, Visits: 338

I had an instance once where we were being pushed to deploy a small website internally with the SA password in clear text in the page and hence clearly visible to anyone who decided to view the code of the page. They could not see the issue as it was only a litlte page to look at some asset information, even when explaining to them the implications of using SA let alone putting it in plain sight.

Needless to say after a short chat with network security that little website got quietly dropped.

Post #1102840

SQLRNNR

Posted Tuesday, May 03, 2011 10:22 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 18,857, Visits: 12,442

I think it most often signals lack of effort or desire to do it differently. It seems like the most effective method to get this changed is to just change the password, have things break and then make them change the way they do things.

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

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1102842

www.ssrstips.com

Posted Wednesday, May 04, 2011 12:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:51 PM
Points: 100, Visits: 482

Forgive me in advance for a bit of a rant but by raising lack of knowledge about SQL Server security and then failing to offer any solution (other than saying there should be a course) you've touched a raw nerve in me! The problem is that SQL Server security is overly and uselessly complex and there's a lack of any current books on how SQL Server security works (let alone teaching by example rather than waffling on philosophically).

Logins and users are easy enough to understand, but throw in schemas, roles, owners, and the possibility of having users/group domain and machine name logins, and it begins to get messy (we'll ignore for the moment the SQL add-on components like reporting/analysis services which might be on a different server, and how that security works, and also ignore file-level NTFS security on the SQL server itself, which hopefully will "just work").

But you may as well shoot yourself in the head if any of your users or developers need to do actual work beyond simply selecting tables/executing procedures and you're not allowed to grant sysadmin privileges... enjoy hours searching through BOL for the exact set of permissions meant to allow the SQL Profiler to run (hint: you'll be hard pressed to find any descriptions of exactly which combination of permissions are required to do any kind of statement in SQL Server or its add-on components).

I personally believe that the people who think they know SQL Server security actually just know one or two of the most common cases and (after learning through massive, painful trial and error) applying them. I'd love to throw you onto a real world database or two and see how you fare getting a problem resolved (and I'd even let you access that useless POS BOL). But maybe I'm wrong and you're a genius, in which case please write a book.

Until then, security systems that can't be easily explained and understood are going to be misused so that the case of "most permissions possible" applies; it's time people took MS to task over this instead of kissing butt. Rant over.

Post #1102884

Perry Whittle

Posted Wednesday, May 04, 2011 1:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 5,242, Visits: 11,262

I found a webserver connecting to a back end database using SQL Server authentication, whereby the account used in the web site config file was the SA account. When i questioned the vendors onsite engineer it became very obvious that they did not understand SQL Server security or how to apply it (via the use of appropriate least permissions). Turns out the account only needed to read write a few tables and most of this was done through stored procedures anyway.

The issue is, as we know, datareader and datawriter do not give permissions on SP's so in a vain effort to "Make things work" they just check the Sysadmin box Smile

Also seen situations where they check every single role for a user, again no understanding that by checking just Sysadmin grants all anyaway

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1102912

P Jones

Posted Wednesday, May 04, 2011 2:12 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 516, Visits: 1,026

I agree about the lack of documentation. For in house development I ask for stored procs to be used throughout and have a script which grants execute on all stored procs to a given sql login. That works fine for most things but then I get one team of developers who want sysadmin permissions but don't understand what it's all about.

I ended up being ordered to give them sysadmin as they played their bosses against mine but what they actually needed was control of ssis packages and sql agent jobs and the roles in msdb didn't do the complete job as they couldn't change certain things or work on each others' items. We spent ages researching and trying to give the permissions they needed but they ended up with sysadmin on their server which houses both their development and live databases (our other systems have dev, test and live servers seperated). I have raised a lot of caveats about that server as they now can change anything unwittingly.

Post #1102918

Adrian Chapman

Posted Wednesday, May 04, 2011 2:20 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 4:00 AM
Points: 2,067, Visits: 107

Linked SQL Servers. All the linked servers authenticate with SA, so anyone with access to one SQL Server has sysadmin rights to others.
Some of the devs know this......

Post #1102920

Schodoodles

Posted Wednesday, May 04, 2011 2:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 9:48 AM
Points: 49, Visits: 153

Should someone tell Sony? Smile

Post #1102921

Horatiu Ripa

Posted Wednesday, May 04, 2011 4:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 17, 2012 2:19 AM
Points: 4, Visits: 20

Well let's start first with a "why".
Usually the DB is last in the chain of layers (or first, depending on the perspective :D).
It's a common implementation for Authentification/Authorization to affect the behavior and look of the application on the other end; usually before a message gets to DAL you already have been in a front end that showed/let you do only thigs that you're authorized for, and hit a BL that already stopped you to perform operations that you're not authorized to do and then and only then the DAL operations are performed. Secondly, under some circumsances, one can or cannot save something to DB - the domain objectual model is not the same with the relational model.
Statement: A good separation of layers always hides the subsequent layers to the frontmost layers, and the layers applications impersonates different users.
Basically that means that you have an authorization (usually checked by the BL and Business Facade) over the application content itself, rather than over each piece of infrastructure of the application.
This is a common approach, and quite a good one.

So, if you're doing it the right way, in order for someone (user or not) to break the security of the SQL server it's almost the same with breaking the security of the (phisical) layers deployment; therefore the security of the machines involved in the layers chain. Well, if that is breaked, SQL security will not be a problem, it may be your last concern...

On the other hand, I saw pretty stupid things, from passing connection strings to invisible textboxes in webapps - more or less protected by some cryptography - to two tier applications (sometimes configured with clear text conection stings to acces the DB with sa) where the user was blocked only by the application to access the full database.

Post #1102965

« Prev Topic | Next Topic »

44 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions