How do I create a MSSQL database user (user on the DB level not the server)

Archives Ansible Project
1

Hello,
So I am new to ansible and I have been looking for a way to create a user on mssql database using ansible but I can’t seem to find a module for that. What I saw is more for MySql db.

  • name: Create DB user with name ‘bob’ and password ‘12345’ with all database privileges
    mysql_user:
    name: bob
    password: 12345
    priv: ‘.:ALL’
    state: present
    Even the above looks like the user will be created on the server not the DB

I have explored using SQLCMD but haven’t found anything concrete on that as well.

Any help is appreciated

2

Ok,
Sorry for my english…but
I unnderstand sqlcmd is for sql server not mysql, try use command with mysql -h and use script sql to create

3

If you’re trying to create users with Transact SQL, I do it this way.
You must first have the following prerequisites on the destination host:

  • SQLTools instaled (linux or windows)

  • have a user with sufficient privileges to create login and SQL users

  • In Win, have a user to connect ansible and with sufficient privileges to exec SQLCMD

  • In Linux, thee user whit use the public key, with sufficient privileges to exec SQLCMD

  • have the transact SQL to create user example:
    USE [master]
    GO
    CREATE LOGIN [user_SQL] WITH PASSWORD=N’password’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [user_SQL]
    GO

  • copy your script to destination host

This is the way i use the playbook

Example win:

  • name: exec createUser
    win_command: “sqlcmd -U sa -P {{ mssql_sa_pwd }} -S {{ ansible_hostname }} -i script_createUser.sql”
    args:
    chdir: “{{ servers_setup_dir }}”

Example redhat:

  • name: Ejecuta script de creacion de usuario
    command: “sqlcmd -U sa -P {{ mssql_sa_pwd }} -i /var/opt/mssql/create_user.sql”
    become_method: su
4

Thank you so much for the assistance. I will try this out