Cannot create a mssql user across two databases in Azure SQL.

[AndrewBorl]

What happened?

If you create two user resources for different databases, a requirement for logging in to Azure SQL with contained users, you will get an error creating login that the service principal already exists reconciling the second resource.

The login database and the CREATE LOGIN command is not optional in the reconciler, forcing a CREATE LOGIN for every CREATE USER, but for Azure SQL contained users across multiple databases, you want to run CREATE LOGIN once in the master database, and then CREATE USER in each database the user should have access to.

How can we reproduce it?

Apply CRDS such as:

`apiVersion: mssql.sql.m.crossplane.io/v1alpha1
kind: User
metadata:
name: user1
spec:
forProvider:
database: database1
loginDatabase: master
passwordSecretRef:
key: password
name: somecreds
providerConfigRef:
name: config

apiVersion: mssql.sql.m.crossplane.io/v1alpha1
kind: User
metadata:
name: user1
spec:
forProvider:
database: database2
loginDatabase: master
passwordSecretRef:
key: password
name: somecreds
providerConfigRef:
name: config
`

What environment did it happen in?

Crossplane version: v0.13.0
Kubernetes version: v1.32.7
Kubernetes distribution: Azure AKS

[AndrewBorl]

I was thinking for this we could add a new default to user CRD such as createLogin: true (to prevent breaking changes), and then a new login CRD which would allow a 1 to many mapping of logins/users for this scenario.

Then deprecate the login calls from the user crd for future removal.

Edit: Actually thinking we could add a login/loginref/loginselector to user and if it this is set, only the user/database needs to be provided, and the password moves to the login CRD. If not set, retain existing functionality.