Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-03 : 09:51:20
|
| Satyaraj writes "Hi All,This is what I need to do :Implement an advanced browse functionality that is similar to what the SQl Enterprise manager displays to browse the sql server folders during the backup and Restore operations - the big difference being that my dialog box should also display the list of network computers ( and shares) that are accesible to the sql server.Fetching the list of computers on the network is easy to implement using ADSI or the standard networking APIs - however, those queries will get executed on the client machine and using the credentials of the client user.In this case, I need to retrieve the list of shares for which the sql server service has access. In other words, the account under which the sql server service runs should be able to access the shares and the files therein.Our database administrator, for security reasons, has deleted the stored procedure 'xp_cmdshell' that would allow me to run commands on the server.Is there any other way by which I can run a method under the credentials of the account under which the sql server service runs ?thanks in advance for any help.Satya" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-03 : 10:37:34
|
| The first thing that comes to mind is to write a COM object that can enumerate network drives and return these results. You can register this COM object on the server (assuming your DBA lets you) and then call it in T-SQL using the sp_OA procedures.Because network paths can be pretty long, you may have to approach this using a loop; you'd have a PopulatePaths() method, for example, that creates the collection in your COM component, then have something like this:For x=0 To NetworkDriveObject.PathsCollection.Count-1CreateListItem(NetworkDriveObject.PathsCollection(x))NextRealize that even if this works, it is a security breach, because a complete neophyte would have access to the SQL Server's network permissions using this application. If your DBA is concerned enough about security to remove xp_cmdshell, he or she probably won't let you use this method either. Just out of curiosity, why does this browsing ability need to be there for BACKUP and RESTORE operations? It seems a little haphazard to have a backup done to any random location from a list of options. |
 |
|
|
satya
Starting Member
1 Post |
Posted - 2002-04-05 : 10:30:54
|
| Hi robvolk,thanks for the response. I agree that the requirement is indeed wierd. In our product, there exists a workflow wherein users work on their individual databases and want to take a selective database of their work at the end of the day. The requirement is that they want to take the backups to a network file share on which they have write access.Unfortunately, our database administrator says "NO" to the COM object idea ( what's new ?)Are there any other stored procedures ( documented or undocumented) that will allow some simple APIs to be run from within sql, using the credentials of the account that the sql server is running ?thanks for any help.Satya |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-05 : 10:43:11
|
| Hang on a second, if the USER needs to access (write to) the network drive, why need to find the SERVER's network access? Unless you're talking about personal network home drives or something, which the SQL Server wouldn't have access to anyway. It seems to me that you'd need to find a path that both the user and SQL Server have permissions to.If that's the case, you can write a stored procedure to accept a UNC path parameter, build a BACKUP command using dynamic SQL which incorporates this path, and then execute it. In fact, I wonder why a BACKUP operation is needed...when the user's go home, how do they access a BACKUP file, they RESTORE it to a copy of the database? I'm sure you can put together some simple code that finds all available network drives for the user, since it will execute on their local workstation anyway.It may not seem better, but what about using sp_detach_db and sp_attach_db instead? It's not as practical for very large database files, but it avoids the backup situation entirely (don't need to use backup devices, etc.) Once the database is detached it can be copied very easily to just about any location. You can even copy just the .MDF file and use sp_attach_single_file_db. |
|
|
|
|
|
|
|
|
How to retrieve the list of computers and files