salt.modules.mssql

Module to provide MS SQL Server compatibility to salt.

depends
  • FreeTDS

  • pymssql Python module

configuration

In order to connect to MS SQL Server, certain configuration is required in minion configs/pillars on the relevant minions. Some sample pillars might look like:

mssql.server: 'localhost'
mssql.port:   1433
mssql.user:   'sysdba'
mssql.password:   'Some preferable complex password'
mssql.database: ''

The default for the port is '1433' and for the database is '' (empty string); in most cases they can be left at the default setting. Options that are directly passed into functions will overwrite options from configs or pillars.

salt.modules.mssql.db_create(database, containment='NONE', new_database_options=None, **kwargs)

Creates a new database. Does not update options of existing databases. new_database_options can only be a list of strings

CLI Example:

salt minion mssql.db_create DB_NAME
salt.modules.mssql.db_exists(database_name, **kwargs)

Find if a specific database exists on the MS SQL server.

CLI Example:

salt minion mssql.db_exists database_name='DBNAME'
salt.modules.mssql.db_list(**kwargs)

Return the database list created on a MS SQL server.

CLI Example:

salt minion mssql.db_list
salt.modules.mssql.db_remove(database_name, **kwargs)

Drops a specific database from the MS SQL server. It will not drop any of 'master', 'model', 'msdb' or 'tempdb'.

CLI Example:

salt minion mssql.db_remove database_name='DBNAME'
salt.modules.mssql.login_create(login, new_login_password=None, new_login_domain='', new_login_roles=None, new_login_options=None, **kwargs)

Creates a new login. Does not update password of existing logins. For Windows authentication, provide new_login_domain. For SQL Server authentication, prvide new_login_password. Since hashed passwords are varbinary values, if the new_login_password is 'int / long', it will be considered to be HASHED.

new_login_roles

a list of SERVER roles

new_login_options

a list of strings

CLI Example:

salt minion mssql.login_create LOGIN_NAME database=DBNAME [new_login_password=PASSWORD]
salt.modules.mssql.login_exists(login, domain='', **kwargs)

Find if a login exists in the MS SQL server. domain, if provided, will be prepended to login

CLI Example:

salt minion mssql.login_exists 'LOGIN'
salt.modules.mssql.login_remove(login, **kwargs)

Removes an login.

CLI Example:

salt minion mssql.login_remove LOGINNAME
salt.modules.mssql.role_create(role, owner=None, grants=None, **kwargs)

Creates a new database role. If no owner is specified, the role will be owned by the user that executes CREATE ROLE, which is the user argument or mssql.user option. grants is list of strings.

CLI Example:

salt minion mssql.role_create role=product01 owner=sysdba grants='["SELECT", "INSERT", "UPDATE", "DELETE", "EXECUTE"]'
salt.modules.mssql.role_exists(role, **kwargs)

Checks if a role exists.

CLI Example:

salt minion mssql.role_exists db_owner
salt.modules.mssql.role_list(**kwargs)

Lists database roles.

CLI Example:

salt minion mssql.role_list
salt.modules.mssql.role_remove(role, **kwargs)

Remove a database role.

CLI Example:

salt minion mssql.role_create role=test_role01
salt.modules.mssql.tsql_query(query, **kwargs)

Run a SQL query and return query result as list of tuples, or a list of dictionaries if as_dict was passed, or an empty list if no data is available.

CLI Example:

salt minion mssql.tsql_query 'SELECT @@version as version' as_dict=True
salt.modules.mssql.user_create(username, login=None, domain='', database=None, roles=None, options=None, **kwargs)

Creates a new user. If login is not specified, the user will be created without a login. domain, if provided, will be prepended to username. options can only be a list of strings

CLI Example:

salt minion mssql.user_create USERNAME database=DBNAME
salt.modules.mssql.user_exists(username, domain='', database=None, **kwargs)

Find if an user exists in a specific database on the MS SQL server. domain, if provided, will be prepended to username

CLI Example:

salt minion mssql.user_exists 'USERNAME' [database='DBNAME']
salt.modules.mssql.user_list(**kwargs)

Get the user list for a specific database on the MS SQL server.

CLI Example:

salt minion mssql.user_list [database='DBNAME']
salt.modules.mssql.user_remove(username, **kwargs)

Removes an user.

CLI Example:

salt minion mssql.user_remove USERNAME database=DBNAME
salt.modules.mssql.version(**kwargs)

Return the version of a MS SQL server.

CLI Example:

salt minion mssql.version