This repository contains scripts for automating SQL Server tasks using PowerShell and T-SQL. The tasks include setting up PowerShell, configuring Always On, creating and deleting databases, and creating users with assigned permissions.
SetupPowerShell.ps1
- Sets up PowerShell for SQL Server.ConfigureAlwaysOn.ps1
- Configures Always On.CreateDatabase.sql
- Creates a new database.DeleteDatabase.sql
- Deletes an existing database.CreateUser.sql
- Creates a new user and assigns permissions.
- SQL Server instance
- PowerShell
- SQL Server Management Studio (SSMS)
-
Setup PowerShell for SQL Server
./SetupPowerShell.ps1
-
Configure Always On
./ConfigureAlwaysOn.ps1
-
Create a Database
-- Run in SQL Server Management Studio :r .\CreateDatabase.sql
-
Delete a Database
-- Run in SQL Server Management Studio :r .\DeleteDatabase.sql
-
Create a User and Assign Permissions
-- Run in SQL Server Management Studio :r .\CreateUser.sql
This script sets up PowerShell to work with SQL Server by installing necessary modules.
# Install SQLServer module
Install-Module -Name SqlServer -Force
# Import SQLServer module
Import-Module SqlServer
This script configures SQL Server Always On availability groups.
# Define parameters
$ClusterName = "ClusterName"
$InstanceName = "InstanceName"
$AGName = "AGName"
# Example configuration command
Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$InstanceName\AvailabilityGroups\$AGName" -ClusterName $ClusterName
This script creates a new database.
CREATE DATABASE [DatabaseName]
GO
This script deletes an existing database.
DROP DATABASE [DatabaseName]
GO
This script creates a new user and assigns permissions.
CREATE LOGIN [UserName] WITH PASSWORD = 'Password';
GO
CREATE USER [UserName] FOR LOGIN [UserName];
GO
ALTER ROLE [db_datareader] ADD MEMBER [UserName];
GO
ALTER ROLE [db_datawriter] ADD MEMBER [UserName];
GO
Feel free to fork this repository, make enhancements, and submit pull requests.
This project is licensed under the MIT License - see the LICENSE file for details.