join the MSSQLTips community

MSSQLTips.com - your daily source for SQL Server tips

Google
 
Web mssqltips.com

 
Using PowerShell with SQL Server Management Objects (SMO) - MSSQLTips

MSSQLTips

MSSQLTips.com - your daily source for SQL Server tips
Welcome to MSSQLTips Sign in | Join | Help
in Search

Using PowerShell with SQL Server Management Objects (SMO)

Last post 05-14-2009 4:58 PM by bass_player. 2 replies.
Page 1 of 1 (3 items)
Sort Posts: Previous Next
  • 05-06-2009 12:30 AM

    Using PowerShell with SQL Server Management Objects (SMO)

    This post is related to this tip: Using PowerShell with SQL Server Management Objects (SMO)

    http://www.mssqltips.com/tip.asp?tip=1745

  • 05-06-2009 6:51 AM In reply to

    Re: Using PowerShell with SQL Server Mangement Objects (SMO)

    Great and useful tool. I have a question though, can I script a backup rutine that allows an "End user" to backup their database? Then they would need to run this script from a Web page as I don't want the end user login into my server. Thanks in advance...

  • 05-14-2009 4:58 PM In reply to

    Re: Using PowerShell with SQL Server Mangement Objects (SMO)

    It's a great idea, though, your main challenge when dealing with anything that has something to do with another front-end application - in this case a website - is security. When a user logs in to a website, they get impersonated by an account that is configured for the web application, most common is the Anonymous Userfor any website. So now you have to do two things - grant permissions on the user that needs to do the backups and grant the web application account permissions on the database server and the database - unless you would implement something like Windows authentication on your web application.

    You don't really need PowerShell embedded in a web application for a simple task such as performing backups as you can do this directly using a BACKUP DATABASE command. Besides, PowerShell has its own security mechanism such as Profiles that enables us to execute PowerShell scripts. You would do the exact same thing withPowerShell as you would do with the web application directly - create an account that would use the profile, use this account in your web application to call the PowerShell script, grant this account permissions to your SQL Server database, etc. You get the point.  The more you add layers to your database access, the more security stuff you have to consider.

Page 1 of 1 (3 items)