none
Using variable to create databse RRS feed

  • Question

  • Hi,

    I'm making a generic script to create my work environment. SSMS 18 don't accept 2 variables : @MyDatabase & @SqlUserName (may be others one which will be found when these 2 problems are solved.

    What's the matter ?

    (I'm not sure with escape characters for path because SSMS & the MSDN forum don't interpret T-SQL syntax coloring the same way with '\' )

    Thanks,

    Vincent

    USE master ;  
    GO  
    
    DECLARE @DatabaseRootFolder varchar(30) = 'C:\Users\Public';
    DECLARE @MyDatabase varchar(255) = 'BddTest'; 
    DECLARE @SqlUserName nvarchar(30) = 'TestUser'; 
    DECLARE @SqlUserPassword varchar(30) = '$Password1234'; 
    
    -- Remove previous DB instance
    DROP DATABASE IF EXISTS @MyDatabase
    GO
      
    -- Create new database - destination folder MUST EXIST
    CREATE DATABASE @MyDatabase
    ON PRIMARY  
    ( NAME = @MyDatabaseName + '_data',
        FILENAME = @DatabaseRootFolder + '\\' + @MyDatabase + '.mdf',  
        SIZE = 2MB,  
        FILEGROWTH = 5 )  
    LOG ON  
    ( NAME = @MyDatabaseName + '_log',
        FILENAME = @DatabaseRootFolder + '\\' + @MyDatabase + '.ldf',  
        SIZE = 2MB,  
        FILEGROWTH = 5MB ) ;  
    GO 
    
    ALTER DATABASE @MyDatabase SET COMPATIBILITY_LEVEL=140
    
    -- Create local database user
    use @MyDatabase
    go
    
    exec sp_configure 'contained database authentication', 1
    go
    reconfigure
    go
    
    alter database @MyDatabase
    set containment=partial
    go
    
    CREATE USER @SqlUserName WITH PASSWORD=@SqlUserPassword
    go
    

    Sunday, November 17, 2019 7:05 PM

Answers

  • (I'm not sure with escape characters for path because SSMS & the MSDN forum don't interpret T-SQL syntax coloring the same way with '\' )

    I wouldn't be overly concerned with the color highlighting. It's basically just a effort to help guide you but isn't useful in many cases because the token fragments may or may not need to be colored differently depend on the context in which they appear.

    The bigger problem with this script is that one cannot use variables/parameters in place of identifiers (the database name) in the DROP/CREATE/ALTER statements and the concatenation expressions are not permitted for the file paths. Instead, consider using SQLCMD varaibles and run the script from SSMS in SQLCMD Mode (Query-->SQLCMD Mode) or with the SQLCMD command-line tool. Below is an example.

    USE master ;  
    GO  
    
    :SETVAR DatabaseRootFolder "C:\Users\Public"
    :SETVAR MyDatabase "BddTest"
    :SETVAR SqlUserName "TestUser"
    :SETVAR SqlUserPassword "$Password1234"
    
    -- Remove previous DB instance
    DROP DATABASE IF EXISTS [$(MyDatabase)];
    GO
      
    -- Create new database - destination folder MUST EXIST
    CREATE DATABASE [$(MyDatabase)]
    ON PRIMARY  
    ( NAME = '$(MyDatabase)_data',
        FILENAME = '$(DatabaseRootFolder)\$(MyDatabase).mdf',  
        SIZE = 2MB,  
        FILEGROWTH = 5 )  
    LOG ON  
    ( NAME = '$(MyDatabase)_log',
        FILENAME = '$(DatabaseRootFolder)\$(MyDatabase)_log.ldf',  
        SIZE = 2MB,  
        FILEGROWTH = 5MB ) ;  
    GO 
    
    ALTER DATABASE [$(MyDatabase)] SET COMPATIBILITY_LEVEL=140;
    
    -- Create local database user
    USE [$(MyDatabase)];
    GO
    
    EXEC sp_configure 'contained database authentication', 1;
    GO
    RECONFIGURE
    GO
    
    ALTER DATABASE [$(MyDatabase)]
    SET containment=PARTIAL;
    go
    
    CREATE USER [$(SqlUserName)] WITH PASSWORD='$(SqlUserPassword)';
    GO
    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Scoubidou944 Sunday, November 17, 2019 10:20 PM
    Sunday, November 17, 2019 8:55 PM

All replies

  • (I'm not sure with escape characters for path because SSMS & the MSDN forum don't interpret T-SQL syntax coloring the same way with '\' )

    I wouldn't be overly concerned with the color highlighting. It's basically just a effort to help guide you but isn't useful in many cases because the token fragments may or may not need to be colored differently depend on the context in which they appear.

    The bigger problem with this script is that one cannot use variables/parameters in place of identifiers (the database name) in the DROP/CREATE/ALTER statements and the concatenation expressions are not permitted for the file paths. Instead, consider using SQLCMD varaibles and run the script from SSMS in SQLCMD Mode (Query-->SQLCMD Mode) or with the SQLCMD command-line tool. Below is an example.

    USE master ;  
    GO  
    
    :SETVAR DatabaseRootFolder "C:\Users\Public"
    :SETVAR MyDatabase "BddTest"
    :SETVAR SqlUserName "TestUser"
    :SETVAR SqlUserPassword "$Password1234"
    
    -- Remove previous DB instance
    DROP DATABASE IF EXISTS [$(MyDatabase)];
    GO
      
    -- Create new database - destination folder MUST EXIST
    CREATE DATABASE [$(MyDatabase)]
    ON PRIMARY  
    ( NAME = '$(MyDatabase)_data',
        FILENAME = '$(DatabaseRootFolder)\$(MyDatabase).mdf',  
        SIZE = 2MB,  
        FILEGROWTH = 5 )  
    LOG ON  
    ( NAME = '$(MyDatabase)_log',
        FILENAME = '$(DatabaseRootFolder)\$(MyDatabase)_log.ldf',  
        SIZE = 2MB,  
        FILEGROWTH = 5MB ) ;  
    GO 
    
    ALTER DATABASE [$(MyDatabase)] SET COMPATIBILITY_LEVEL=140;
    
    -- Create local database user
    USE [$(MyDatabase)];
    GO
    
    EXEC sp_configure 'contained database authentication', 1;
    GO
    RECONFIGURE
    GO
    
    ALTER DATABASE [$(MyDatabase)]
    SET containment=PARTIAL;
    go
    
    CREATE USER [$(SqlUserName)] WITH PASSWORD='$(SqlUserPassword)';
    GO
    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Scoubidou944 Sunday, November 17, 2019 10:20 PM
    Sunday, November 17, 2019 8:55 PM
  • Wonderful :)

    Bonus question : is there any SQL-Injection risk with this code ?

    Sunday, November 17, 2019 10:22 PM
  • Bonus question : is there any SQL-Injection risk with this code ?

    Yes, this is an ad-hoc script so the person running it can run any other statements too, limited only by their permissions. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, November 18, 2019 1:37 AM