Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Security Part 2


SQL Server Security Part 2

Author
Message
ckempste
ckempste
SSC Eights!
SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)

Group: General Forum Members
Points: 989 Visits: 1
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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (7.3K reputation)

Group: Moderators
Points: 7328 Visits: 1917
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.
Steven.
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 247
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
ckempste
SSC Eights!
SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)

Group: General Forum Members
Points: 989 Visits: 1
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
ckempste
SSC Eights!
SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)

Group: General Forum Members
Points: 989 Visits: 1
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.
Steven.
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 247
thanks,

learn something new everyday

Steven
Thomas LeBlanc
Thomas LeBlanc
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3823 Visits: 902

Where is a link to Part I.

Thanks,

ThomasLL



Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
James Luetkehoelter
James Luetkehoelter
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 33

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
J.T. Shyman
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 4

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
Dave Hegwood
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 14

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....


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