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

Question

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?

Thom A · Accepted Answer · 2021-03-08 14:25:31Z

2

You'll need to use a deferred statement here. Even though the USE statement won't be reached if the database doesn't exist, the database still needs to exist, otherwise the parser will detect the invalid database name and the statement will fail:

    PRINT N'Creating User ''XUser''.';
    EXEC sys.sp_executesql N'USE MyDB; CREATE USER XUser FROM LOGIN XUser;';

Also, don't use sp_addrolemember, it's been deprecated for at least 13 years; use ALTER ROLE.

edited Mar 8, 2021 at 14:25

answered Mar 8, 2021 at 14:04

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related