SQLSmart Server Scripter, developed primarily to facilitate error-free SQL Server instance migrations, is a unique tool for scripting SQL Server server-level objects and configurations using a simple and intuitive graphical user interface that provides functionality missing in conventional tools including SQL Server Management Studio (SSMS).
SQLSmart Server Scripter is a powerful application that, with only a single click and in just one iteration, provides the ability to generate DDL scripts for practically all SQL Server-level objects like logins, backup devices, linked servers, server configurations, server permissions and roles in one iteration with pinpoint accuracy.
You would be surprised to find out that many SQL Server migrations/moves are delayed or fail due to one or more missing configurations or permissions that were not transferred. Unfortunately, neither SSMS nor many other tools in the market provide a unified and simple interface to easily and without any errors capture server permissions and configurations.
SQLSmart Server Scripter was developed to cover this gap and can easily generate general sql scripts for SQL Server-level objects and configurations in one attempt and without the need to visit each and every object in SSMS.
Below is a non-exhaustive list of features offered by SQLSmart Server Scripter along with samples as used in the GUI:
- Script out server-level Audits
Audits: a* - Script out Backup Devices
Backup Devices: [b-d]* - Script out Credentials and retrieve the passwords (using an opensource software)
Credentials: * - Script out Cryptographic Providers
Cryptographic Providers: * - Script out Endpoints
Endpoints: * - Script out SQL Agent Jobs
SQL Agent Jobs: * - Script out Linked Servers and retrieve the passwords (using an opensource software)
Linked Servers: * - Script out logins
Logins: * - Script our server configurations
Server Configurations: *
Suppose you wanted to migrate a SQL Server and needed to capture its current state. This translates into generating scripts for:
- Audits whose name start with “a”
- Backup Devices whose names start with “b-d”
- Credentials
- Cryptographic Providers
- Endpoints
- SQL Agent Jobs
- Linked Servers
- Logins
- Server Configurations
- Server Permissions
- Server Roles
Using SSMS and perhaps most of the SQL scripting tool in the market:
1. You would need to visit each and every object.
2. You may not get the credentials scripted with correct encrypted/decrypted passwords.
3. You may not get the linked servers scripted with correct encrypted/decrypted passwords.
4. You may not get the logins scripted with correct encrypted passwords.
5. You will not get the server configurations unless you query the system tables.
6. You will not get the server permissions unless you query the system tables.
7. You will not get the server roles memberships unless you query the system tables.
In contrast, using SQLSmart Server Scripter, you can generate the scripts for all of the above items in one attempt:
- Pick a desired “SQL_Operations” (Figures 1a & 1b).
- Fill in the column “Objects_to_be_scripted” (Figures 2a & 2b).
- Pick the app name (as defined in the config file) (Figure 2a).
- Click the “Generate SQL” button to get the output in seconds (Figure 3).
Here are the screenshots for the steps in SQLSmart Server Scripter:
Figures 1a and 1b
Figures 2a and 2b
Figure 3
Here is the abridged SQL output generated by SQLSmart Server Scripter:
IF NOT EXISTS (SELECT * FROM sys.server_audits WHERE name = N'Audit-20230102-092351')
BEGIN
CREATE SERVER AUDIT [Audit-20230102-092351]
TO FILE
( FILEPATH = N'C:Ttemp'
,MAXSIZE = 30 MB
,MAX_FILES = 6
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'a375547a-9dfa-4752-8051-55152c4e0c75'
)
ALTER SERVER AUDIT [Audit-20230102-092351] WITH (STATE = OFF)
END
IF NOT EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'bkupdev1')
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'bkupdev1', @physicalname = N'C:SQLDataMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackupbkupdev1.bak'
IF NOT EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'cbackupdev3')
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'cbackupdev3', @physicalname = N'C:SQLDataMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLBackupcbackupdev3.bak'
IF NOT EXISTS(SELECT name FROM sys.credentials WHERE name='cred')
CREATE CREDENTIAL [cred] WITH IDENTITY = N'NT AUTHORITYSERVICE', SECRET = N'password111'
IF NOT EXISTS(SELECT name FROM sys.credentials WHERE name='dLocalCred')
CREATE CREDENTIAL [dLocalCred] WITH IDENTITY = N'NT AUTHORITYSERVICE', SECRET = N'password222'
IF NOT EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Dedicated Admin Connection')
BEGIN
CREATE ENDPOINT [Dedicated Admin Connection]
STATE=STARTED
AS TCP (LISTENER_PORT = 0, LISTENER_IP = ALL)
FOR TSQL ()
END
...
IF NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'recovery interval (min)' AND value_in_use = 0)
EXEC sp_configure 'recovery interval (min)', 0
IF NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'allow updates' AND value_in_use = 0)
EXEC sp_configure 'allow updates', 0
IF NOT EXISTS(SELECT * FROM sys.configurations WHERE name = 'user connections' AND value_in_use = 0)
EXEC sp_configure 'user connections', 0
...
IF NOT EXISTS(SELECT TOP 1 * FROM sys.server_permissions sp inner join sys.server_principals spr on sp.grantee_principal_id = spr.principal_id WHERE permission_name = 'CONNECT SQL' AND spr.name = 'sa')
GRANT CONNECT SQL To [sa]
IF NOT EXISTS(SELECT TOP 1 * FROM sys.server_permissions sp inner join sys.server_principals spr on sp.grantee_principal_id = spr.principal_id WHERE permission_name = 'VIEW ANY DATABASE' AND spr.name = 'public')
GRANT VIEW ANY DATABASE To [public]
IF NOT EXISTS(SELECT top 1 sr.role_principal_id FROM sys.server_role_members sr INNER JOIN sys.server_principals sp1 ON sr.member_principal_id = sp1.principal_id WHERE sr.role_principal_id = 3 AND sp1.name = 'sa')
BEGIN
EXEC sp_addsrvrolemember @loginame = 'sa', @rolename = 'sysadmin'
END
/*
The Credential and Linked Server passwords are extracted using the following open source software:
Author: Antti Rantasaari 2014, NetSPI
License: BSD 3-Clause
https://github.com/NetSPI/Powershell-Modules/tree/master
Extract and decrypt MSSQL Credentials passwords.
Get-MSSQLCredentialPasswords extracts and decrypts the connection credentials for all saved Credentials.
Extract and decrypt MSSQL linked server passwords.
Get-MSSQLLinkPasswords extracts and decrypts the connection credentials for all linked servers that use SQL Server authentication on all local MSSQL instances.
*/
One of the interesting features of SQLSmart Server Scripter is that in all cases it allows the use of wildcards for script generation.
For instance, you can:
1. script all system defined Audits whose name starts with “a” through “h” using “[a-h]*”.
2. script all Backup Devices whose name starts with “c” or “z” using “[cz]*”.
3. script all Logins whose name starts with “dom” using “dom*”.
4. script all Server Permissions for “View Server State” or “Connect SQL” using “View Server State*; Connect Sql*”.
To get the CREATE script for Backup Device named “bkupdev1”, simply type fill in GUI as shown to the right:
Backup Devices: bkupdev1;
Pick your “app” (app1 here); and
Click the “Generate SQL” button.
The above will generate the output shown on the right once the “Generate SQL” button is clicked:
To get the CREATE scripts for all Backup Devices that have the name “bkupdev” in them, simply use “*bkupdev*”:
BackupDevices: *bkupdev*;
