Post reply

Create new database with password?

  • October 14, 2022 at 10:00 am

    #4099530

    Hi all,

    I have a query that creates database and their mdf and ldf files on desired location on local drive.

    I want to know, how can I modify this query to create a db user or login user with password so that only that user have access to this database and no one else can use it?

     

    USE master
    GO

    IF NOT EXISTS
    (
    SELECT * FROM sys.databases 
    WHERE name = 'TEST_DB_2022'
    )
    BEGIN
    CREATE DATABASE TEST_DB_2022 ON  PRIMARY 
    ( 
    NAME = N'TEST_DB_2022_DATA', 
    FILENAME = N'D:\TEST_DB_2022_DATA.mdf',
    MAXSIZE = UNLIMITED
    )
    LOG ON 
    ( 
    NAME = N'TEST_DB_2022_LOG', 
    FILENAME = N'D:\TEST_DB_2022_LOG.ldf',
    MAXSIZE = UNLIMITED
    )

    END;

  • October 14, 2022 at 10:37 am

    #4099541

    first question is why username/password (unsecure) and not windows authentication.

    Second - unless you add the users to the database no one will have access to those db's - so that is a moot point.

    but to create a new user you first need to create a login (if not there already) - on this if you decide to use a SQL Login (not advisable) you define its password as well.

    then you add that users to the database.

    as for the commands to use - you can find them out if you try and add both a login and a user to a db and script them to a new query window. (or you can read the manuals)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply