none
DW - DB Creation using DB Owner user RRS feed

  • Question

  • We have created Data warehouse server and planning to create Databases, Schema and users.
    If i create any Database that will go under my name but i would like to create Any database under specifice user so how can i do it?
    Should i create sql server user/login or create AD login and create user under then create the Database so it will show as Database under that user not under my name?
    What's the best practice?

    I have to create also Schema for Data warehouse databases so any useful additional information will greatly helpful.

    Thanks

    Wednesday, June 19, 2019 9:36 PM

All replies

  • If i create any Database that will go under my name

    I don't understand what you mean "DB under my/other Name", can you explain it more detailed, please?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 20, 2019 6:06 AM
  • I mean i'm creating through SSMS so it's uses my AD login when i'm connecting to server as windows authentication. So when i run or use SSMS GUI, it will show my me as a DB Owner and i don't want my name but need some specific user so i would like to know what's the best way to create the Database.


    Friday, June 21, 2019 3:20 AM
  • you can Change the dbo = "database owner" on any time with sp_changedbowner (Transact-SQL) or better with ALTER AUTHORIZATION (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, June 21, 2019 6:28 AM
  • Thank you!

    I am planning to create DB for Data Warehouse like, looks ok for Data warehouse?

    USE master;
    GO
    CREATE DATABASE ETLDB
    ON
    PRIMARY
        (NAME = ETLDB_Dat1,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\ETLDB_dat1.mdf',
        SIZE = 100MB,
        MAXSIZE = 200,
        FILEGROWTH = 20),
        ( NAME = ETLDB_dat2,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\ETLDB_dat2.ndf',
        SIZE = 100MB,
        MAXSIZE = 200,
        FILEGROWTH = 20),
        ( NAME = ETLDB_dat3,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\ETLDB_dat3.ndf',
        SIZE = 100MB,
        MAXSIZE = 200,
        FILEGROWTH = 20)
    LOG ON
      (NAME = ETLDB_log1,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\ETLDB_log1.ldf',
        SIZE = 100MB,
        MAXSIZE = 200,
        FILEGROWTH = 20),
      (NAME = ETLDB_log2,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\ETLDB_log2.ldf',
        SIZE = 100MB,
        MAXSIZE = 200,
        FILEGROWTH = 20) ;
    GO

    Friday, June 21, 2019 3:13 PM
  • actually I am getting error while changing the owner as you don't have permission or user don't exists but when i see that i have required permissions.

    ALTER AUTHORIZATION i have used earlier and it worked but this time no luck.

    We have created AD user specially for development DB and i am trying to make this AD user as DB owner and that created by our windows group. so i can't drop and assign back using following command:

    exec sp_dropuser 
    exec sp_changedbowner

    Saturday, June 22, 2019 9:33 PM
  • Thanks for all help!
    Thursday, June 27, 2019 11:13 AM
  • How many rows are you talking about in your largest tables?

    Large tables should be partitioned. Either using the partitioning feature of SQL Server or by manually partitioning. 


    Peter Nolan

    Sunday, June 30, 2019 2:59 PM
  • we have very small tables
    Tuesday, July 2, 2019 11:20 AM