June 19, 2019 at 9:33 pm
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.
June 19, 2019 at 10:13 pm
You can change the owner of databases or schemas using alter authorization. For Example:
--change database owner
ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa;
--change schema owner
ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;
Sue
June 20, 2019 at 2:05 am
Thanks Sue.
I know that as i have done couple of times but my question is when we have built the servers new and have to create the new databases and would like to create new DB under specific user not under my user login as when i login to the server it will use my windows authentication so as a Db owner it will show my name so what's the best way i can handle it?
June 20, 2019 at 10:58 am
When you create your database as part of your create script you simply change the authorization afterwards, so it'll look something like Create Database
[…]
Once your objects are created you can change the authorization to whichever user you want, as an alternative you can Always login as that user and if permissions allow, run your create script as that user OR you could use EXECUTE AS to run a script as specific user, this requires your account to have impersonation permissions.
June 21, 2019 at 3:16 am
Got it!
What do you prefer more AD account or sql user DB Owner?
Thank you!
June 21, 2019 at 3:11 pm
I'm planning to create like below but What will be the ideal DB script should be 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
June 24, 2019 at 9:06 pm
No one can tell you what the ideal would be - it really depends on your environment, your data warehouse, etc.
The files seem pretty small with a small growth increment. A lot of small growths could be a performance hit. But the thing I was wondering is why do you have two log files?
Sue
June 26, 2019 at 3:32 am
Thanks Sue.
What should be the ideal files and File size for small Sql Data Warehousing?
June 26, 2019 at 4:41 pm
No one can tell you ideal size for database files just by asking what they should be. You would want to do some capacity planning to determine things like that. Do a search on capacity planning for SQL Server.
Sue
June 27, 2019 at 11:11 am
Thanks everyone!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply