Restrict Access to Excel Work Book using powershell

• nikhil.verma

  SSCrazy

  Points: 2603

  More actions

  April 19, 2016 at 5:30 am

  #313422

  Hi All , I have generate the code which generated the XLSX file using powershell in SQL Server 2012 ( Excel is not installed on the server ) . As a next step I would like to restrict the access of specific set of people on the work book . Can this be achieved using powershell ?

  Would really appreciate if you can share your views on this .

  Regards

• Lowell

  SSC Guru

  Points: 323527

  More actions

  April 19, 2016 at 5:39 am

  #1873009

  limiting access to an existing workbook would simply be permissions on a file share, right?

  I don't think you need powershell for that, just assign the right permissions to a folder for the users/group that the powershell will copy the file to, and you'd be all set.

  Optionally, I guess you could lock the book with a password, and only giving specific people the password.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• nikhil.verma

  SSCrazy

  Points: 2603

  More actions

  April 19, 2016 at 5:46 am

  #1873011

  Thanks for your response . The idea is to generate the XSLX randomly using script and then using database mail I am sharing the file with the users. This process runs in each iteration of cursor . Hence I want to restrict the file with the users names to which database mail is going.

• jeff.mason

  SSCrazy Eights

  Points: 9585

  More actions

  April 19, 2016 at 6:37 am

  #1873030

  If you are using SMTP to send a file, then you are dependent upon Excel itself to preserve the file for users. SQL Server can't do that, nor can an SMTP server. You will need to research the Office sites for understanding of how to program Excel to do that -- and you probably will need to install Excel on the SQL Server in some form so that Powershell has the Excel components to set the password/access (if it can be done at all, and I don't know if it can). Not sure if that could work -- all I can tell you is that you cannot control file access via SQL Server and SMTP at all. And of course the underlying assumption is that if you email someone a file, you want them to be able to open it (unless you want to use Secure SMTP, which Database Mail cannot).