SQL Server Express Vagrant environment
This is an example Vagrant environment for a SQL Server 2022 Express installation.
It will:
alice.doe
(in the sysadmin
role), carol.doe
, eve.doe
and grace.doe
.bob.doe
, dave.doe
, frank.doe
and henry.doe
.HeyH0Password
password.TheSimpsons
Database
db_executor
database role with permissions to execute stored procedures.carol.doe
in the db_datawriter
, db_datareader
and db_executor
roles.eve.doe
in the db_datareader
and db_executor
roles.Install the Windows 2022 base box.
Run vagrant up --no-destroy-on-error
to launch the environment.
Login into the Windows VM, open ConEmu/bash, and dump the SQL Server TLS details:
# see the TLS certificate validation result:
echo | openssl s_client -connect $COMPUTERNAME:1433 -servername $COMPUTERNAME -CAfile /c/vagrant/tmp/ca/example-ca-crt.pem
# see the TLS certificate being returned by the server:
echo | openssl s_client -connect $COMPUTERNAME:1433 -servername $COMPUTERNAME -CAfile /c/vagrant/tmp/ca/example-ca-crt.pem | openssl x509 -noout -text -in -
List active connections details:
select
c.client_net_address,
s.login_name,
db_name(s.database_id) as database_name,
s.program_name,
c.encrypt_option,
c.connect_time
from
sys.dm_exec_connections as c
inner join sys.dm_exec_sessions as s
on c.session_id = s.session_id
order by
c.client_net_address,
s.login_name,
s.program_name
NB you can customize what appears on s.program_name
by setting the Application Name
connection string property, e.g., Application Name=Example Application;
.
select
principals.principal_id,
principals.name,
principals.type_desc,
principals.authentication_type_desc,
permissions.state_desc,
permissions.permission_name
from
sys.database_principals as principals
inner join sys.database_permissions as permissions
on principals.principal_id = permissions.grantee_principal_id
order by
principals.name,
principals.type_desc,
principals.authentication_type_desc,
permissions.state_desc,
permissions.permission_name
select
schema_name(schema_id) as schema_name,
t.name as table_name,
sum(p.rows) as row_count
from
sys.tables as t
inner join sys.partitions as p
on t.object_id = p.object_id
and p.index_id in (0, 1)
group by
schema_name(schema_id),
t.name
select
sum(p.rows) as row_count,
(select sum(case when type = 1 then size end) * cast(8 * 1024 as bigint) from sys.master_files where database_id = db_id()) as data_size_bytes,
(select sum(case when type = 0 then size end) * cast(8 * 1024 as bigint) from sys.master_files where database_id = db_id()) as log_size_bytes
from
sys.tables as t
inner join sys.partitions as p
on t.object_id = p.object_id
and p.index_id in (0, 1)
select
db_name(database_id) as database_name,
sum(case when type = 1 then size end) * cast(8 * 1024 as bigint) as data_size_bytes,
sum(case when type = 0 then size end) * cast(8 * 1024 as bigint) as log_size_bytes
from
sys.master_files
group by
database_id