SQL Server Security Part 2

  • ckempste

    SSCoach

    Points: 17983

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ckempster/securitypart2.asp


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    This is the one that gets me upset most of the time:

    quote:


    The only problem here can be third party software that utilises SQL Server as its security or other repository in order to run. I have come across a few products that prompt to for the SA password, then end up creating a database with no other user but SA, only to spend hours reading documentation and testing to change it.


    Certain products balk at when they run in anything less than dbo and when making the ODBC connections, you have the same username and password. This leaves a database wide open and just causes headaches. Grrr.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Steven.

    SSCrazy Eights

    Points: 9806

    In the Statement Privilege Revocation section of the document you display a image of User/Roles against DDL statements.

    I have never come across this screen, could you tell me how to get to it etc.

    Regards

    Steven White

    Steven

  • ckempste

    SSCoach

    Points: 17983

    Hi Steven

    Select properties of your DB and go to the last tab called "permissions".

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • ckempste

    SSCoach

    Points: 17983

    Hi Steven

    Select properties of your DB and go to the last tab called "permissions".

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Steven.

    SSCrazy Eights

    Points: 9806

    thanks,

    learn something new everyday

    Steven

  • Thomas LeBlanc

    SSCertifiable

    Points: 7732

    Where is a link to Part I.

     

    Thanks,

    ThomasLL

    Thomas LeBlanc, MVP Data Platform Consultant

  • James Luetkehoelter

    SSCommitted

    Points: 1733

    Outstanding article!  Very thorough with great references for further information. 

    I agree with Brian's post -- that's the most infuriating thing to have applications that demand dbo, or worse, sysadmin access.  There is no reason any application (with the exception of admin utilities) should require this level of access.  I'm often asked to evaluate software for clients, and this one is a deal-breaker from my point of view. 

    The Windows world really has to get use to the idea of having to explicitly set permissions to resources required.  Windows Server 2003 has done a much better job at forcing you to explicitly set NTFS and Share permissions, and Yukon is moving in the same direction.  The problem is that we, as administrators and developers, need to get in the habit of doing this regardless of what the platform forces you to do.  This isn't a technology problem, it's a process problem.

    OK, my 2 cents, I'll shut up now.  Again, great article!

  • J.T. Shyman

    SSCrazy

    Points: 2582

    I used to have that same opinion actually but then I had a conversation with someone who has been in software development for several years. My feelings haven't changed, mind you, but I'm not nearly as stubborn about apps that do this.

    Consider: Not all companies running a given software package have a DBA full or part-time. If they don't, then the application administrator must be able to create new SQL logins through the application itself. To do this requires sysadmin rights.

    Check out http://databasejournal.com/features/mssql/article.php/1473011 for a good discussion on why GreatPlains decided to use the sa account in its application. Yes, it can be turned off. However, it increases workload for the DBA who now has to create SQL accounts by hand.

     

    -- J.T.

    "I may not always know what I'm talking about, and you may not either."

  • Dave Hegwood

    SSC Rookie

    Points: 35

    One way we share DTS Packages where I work is to save the package as a .DTF in an NT shared Folder.  That way, authorized users can access the package and make revisions or run it individually as necessary...

    No need to be a sysadmin because you are opening the DTS Package from the DTF and it appears that you are the author.  Just a thought....

     

  • Yelena Varshal

    SSC-Dedicated

    Points: 34144

    Hi

    The article mentioned denying somethings like syscomments access to PUBLIC. Everyone is a member of Public, will SPs work after that?

    Otherwise the article is very good. Chris even did not forget to remind us to take care of cleaning audit tables having in mind the amount of information in them

    Yelena

    Regards,Yelena Varsha

  • James Luetkehoelter

    SSCommitted

    Points: 1733

    For those of you new to the article (or others of Chris has posted), he's outstanding.  Check out his other articles.  He also has some excellent e-books on his website on disaster recovery (an issue dear and unfortunately near to my heart).

    Keep discussing and debating --it's the only way we truly learn.

  • IsaacGoGo

    SSC Eights!

    Points: 934

    Chris - this is an excellent article. Great work!

  • Liliya Huff

    Old Hand

    Points: 391

    yes. dynamic sql in sp works as well. wery easy to test.

  • tymberwyld

    SSCertifiable

    Points: 7810

    I have a question (and please excuse me if it's been asked before). If I am using SSL between a WebClient and WebServer, is it possible to intercept the communication between the WebServer and SQL Server Database? I would think that the database communication is running on the server end and that the only thing the client can see or intercept would be things that it has a certificate for.

    Can someone shed some light on this? Thanks!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply