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

[Adurota Gideon]

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

[aide...@gmail.com]

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

Enviado desde mi iPhone

El 30-06-2022, a la(s) 14:17, Adurota Gideon <gideon...@gmail.com> escribió:

Hello,

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/df19f1ca-2526-4a45-a967-79b7f38cea78n%40googlegroups.com.

[aide...@gmail.com]

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
  

[Adurota Gideon]

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