February 7, 2010 at 9:54 am
Perry Whittle (2/7/2010)
hi i realised that. But whether sql, windows (NTFS), etc be careful with deny always revoke where possible
You can't "revoke" NTFS permissions - you have allow and deny. Same with firewall rule actions. That said, I actually leverage Deny access controls extensively within my infrastructures. Since DENY takes precedence over any other ALLOW permissions, and any combination or resultant permissions that might result in an ALLOW, DENY can be quite powerful and in some cases, desired.
February 8, 2010 at 7:51 am
you revoke NTFS permissions by not adding entities to the resource ACL list, this prevents access but doesnt deny. Access could still be granted via membership of another group.
All I'm saying is be careful with deny
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2010 at 9:36 am
Perry Whittle (2/8/2010)
you revoke NTFS permissions by not adding entities to the resource ACL list, this prevents access but doesnt deny. Access could still be granted via membership of another group.All I'm saying is be careful with deny
Ah - understood. The term "revoke" was throwing me off. I think "don't assign" would be better placed, but your point is now taken. One certainly shouldn't just "deny" willy nilly without understanding what the ramifications are.
In fact, your point is probably better made within the actual context of my usage, where my SQL Server Service user belongs to a group of other service users that I explicitly assign "Deny access to this computer from the network" to (most) hosts to limit what a SQL compromise can yield in regard to attacks down-range. You've certainly got to implement this type of security measure with care.
t
February 8, 2010 at 10:35 am
totally agree Thor.
the revoke thing is a little ambiguous i agree. I understand it as
if you take the user account out of the ACL you have neither allowed or denied, its null or "Revoked". Microsoft use the term revoke to indicate an action that implies no access definition (i.e. neither allowed or denied). Once you done a few of the MS courses and taken some of their exams it soon starts to sink in 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 22, 2010 at 10:31 am
Jonathan,
This function is exactly what I was looking for as part of a SQL Server Disaster Recovery solution I'm developing. The only additional thing I would need to do would be to recurse through all the subdirectories and return information on all files discovered therein. Do you have any tips on how to go about making the function recursive?
Thanks!
~ Ron
March 22, 2010 at 10:47 am
Ron Jennings (3/22/2010)
Jonathan,This function is exactly what I was looking for as part of a SQL Server Disaster Recovery solution I'm developing. The only additional thing I would need to do would be to recurse through all the subdirectories and return information on all files discovered therein. Do you have any tips on how to go about making the function recursive?
Thanks!
~ Ron
You would have to change the C# code to perform the recursion, but I don't know that I would do that all at once for performance/memory scalability reasons. What specifically are you trying to do that needs it to recurse like that?
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
March 22, 2010 at 10:54 am
I want to pass in a top-level backup directory path and have the procedure list all files found in all subdirectories. The idea is to build a list of backup files found in the directory tree, so that I can then grab header information from the backup files themselves to build a list of databases that can be recovered from the files in the directory.
I'm thinking I will probably need to create my own recursive class that uses the GetFileSystemInfos method, then call that method in the os_directory_info method.
March 22, 2010 at 11:09 am
It's a real shame that we have to try to rewrite that which works so well...
[font="Arial Black"]Dir c:\*.* /s /b[/font]
--Jeff Moden
March 22, 2010 at 11:20 am
Since this sounds like a DBA only function in a recovery scenario, I'd probably enable xp_cmdshell for the duration of the recovery and turn it back off once you finish restoring. In a recovery scenario are you really going to want to first Enable CLR for SQL, then deploy the function, and then be able to get your process for recovery running, or do you want to be able to open a script, fire it off, and have it begin the recovery process immediately?
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
March 22, 2010 at 11:33 am
It is a DBA only function, but we already have CLR enabled, and we already use managed code for other DBA functions. When we create our utility database on a recovery server, any CLR functions will be created along with it, so they will be available for DBA use. I could certainly write this using xp_cmdshell, but it would be kludge-y, and I'd prefer to avoid that if possible.
March 22, 2010 at 11:56 am
Jeff Moden (3/22/2010)
It's a real shame that we have to try to rewrite that which works so well...[font="Arial Black"]Dir c:\*.* /s /b[/font]
Good old DOS Jeff, you can't beat it can you 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 23, 2010 at 6:01 am
Perry Whittle (3/22/2010)
Jeff Moden (3/22/2010)
It's a real shame that we have to try to rewrite that which works so well...[font="Arial Black"]Dir c:\*.* /s /b[/font]
Good old DOS Jeff, you can't beat it can you 😀
Yes Sir... and I don't understand why MS doesn't understand that there is a need to do file handling in places other than SSIS and CLRs like T-SQL for instance. xp_DirTree will do similar (although the file path is a bit elusive there) if the 3rd operand is used. It seems only natural that file handling and ETL go together, so it seems only natural that there should be some additional file handling capabilites in T-SQL that is currently sorely missing.
--Jeff Moden
April 29, 2011 at 1:43 am
example here of using c# clr proc to zip and ftp backups. Also using asymmetric key.
April 29, 2011 at 7:13 am
Sorry... posted against a 2 year old post and removed it...
--Jeff Moden
April 29, 2011 at 7:37 am
Viewing 15 posts - 76 through 90 (of 168 total)
You must be logged in to reply to this topic. Login to reply