Use sql script for sharepoint in psake

db-setup.psake.ps1

Task Db-Setup -Description "Adds the current user for access to SharePoint schemas - DEV only" {
   $userToAdd =  Read-Host -Prompt 'User to add to Farm Admin group <DOMAIN>\<USERNAME>'
   $db_env =  Read-Host -Prompt 'Environment suffix of the database to setup'
   Write-Host "If this script has a problem then you don't have login access - please sort this out first"
   Write-Host "see http://www.sharepointassist.com/2010/01/29/the-local-farm-is-not-accessible-cmdlets-with-featuredependencyid-are-not-registered/" 
   $whoami = [Environment]::UserName
   Write-Host "Adding dev user to database: $whoami"
   Write-Host "Enter the password for the user $site_owner"
   
   exec { sqlcmd -i .\dev-powershell-access.sql -U $whoami -v user=$userToAdd -v db_env=$db_env }
}

<#

dev-powershell-access.sql

USE [SP_Admin_Content_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_Admin_Content_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_Admin_Content_DB_$(db_env)]
GO
EXEC sp_addrolemember N'SharePoint_Shell_Access', N'$(user)'
GO
USE [SP_Config_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_Config_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_Config_DB_$(db_env)]
GO
EXEC sp_addrolemember N'SharePoint_Shell_Access', N'$(user)'
GO

#>

<#

USE [master]
GO
CREATE LOGIN [$(user)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [SP_Admin_Content_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_Admin_Content_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_BCServiceDB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_BCServiceDB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_Config_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_Config_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_Content_Portal_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_Content_Portal_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_MetaDataService_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_MetaDataService_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_Search_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_Search_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_Search_DB_$(db_env)_CrawlStore]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_Search_DB_$(db_env)_CrawlStore]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_Search_DB_$(db_env)_PropertyStore]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_Search_DB_$(db_env)_PropertyStore]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_SecureStoreServiceApp_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_SecureStoreServiceApp_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_StateService_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_StateService_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_UPSProfile_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_UPSProfile_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_UPSSocial_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_UPSSocial_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_UPSSync_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_UPSSync_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_UsageService_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_UsageService_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_WebReporting_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_WebReporting_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_WebStage_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_WebStage_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_WordAutomation_DB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_WordAutomation_DB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO
USE [SP_WSS_SearchDB_$(db_env)]
GO
CREATE USER [$(user)] FOR LOGIN [$(user)]
GO
USE [SP_WSS_SearchDB_$(db_env)]
GO
EXEC sp_addrolemember N'db_owner', N'$(user)'
GO

#>