lundi 8 mars 2021

MSSQL How to create a DB User only when the database is available

I am on SQL Server and want to create a script that creates a database user in different databases, but I do not know which databases are available at runtime. I just want to skip user creation if a database is not available. I came up with this code:

IF EXISTS (SELECT 1 FROM sys.databases WHERE NAME = 'MyDB')
BEGIN
    IF NOT EXISTS (SELECT 1 FROM MyDB.sys.database_principals WHERE NAME = 'XUser')
    BEGIN
        /* This fails when DB MyDB is not available, despite it should not run at all */
        USE MyDB

        SELECT 'Creating User ''XUser''.'
        CREATE user XUser
        FROM LOGIN XUser
    END
    ELSE
    BEGIN
        SELECT 'User ''XUser'' in MyDB already exists, skipping creation.'
    END
    SELECT 'User ''XUser'' gets special user role in MyDB database.'
    EXEC MyDB..sp_addrolemember 'db_owner', 'SpecialUser'
END
ELSE
BEGIN
    SELECT 'Warning: DB MyDB not found. Login ''XUser'' doesn''t get additional rights on that database.'
END

But it is not working, I get

Database 'MyDB' does not exist.

the USE statement seem to be the problem, but without it the user will not be created in the right db?

How can I achive what I want?

Aucun commentaire:

Enregistrer un commentaire