SQL script to generate new database and add account and apply proper permission

script to create new db

CREATE DATABASE [Pawan]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Pawan', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Pawan.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Pawan_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Pawan_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [Pawan] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Pawan] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Pawan] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Pawan] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Pawan] SET ARITHABORT OFF 
GO
ALTER DATABASE [Pawan] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Pawan] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [Pawan] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Pawan] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Pawan] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Pawan] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Pawan] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Pawan] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Pawan] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Pawan] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Pawan] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Pawan] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Pawan] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Pawan] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Pawan] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Pawan] SET  READ_WRITE 
GO
ALTER DATABASE [Pawan] SET RECOVERY FULL 
GO
ALTER DATABASE [Pawan] SET  MULTI_USER 
GO
ALTER DATABASE [Pawan] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Pawan] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
USE [Pawan]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Pawan] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

Select allOpen in new window

Permission you can give like below

GRANT ADMINISTER BULK OPERATIONS TO [yourserverlogin]
GRANT INSERT on SCHEMA::dbo to Principal 

Select allOpen in new window

What  if I don't want to set the size but keep us "AUTO" for each. What is should put there?.

( NAME = N'Pawan', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Pawan.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'Pawan_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Pawan_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO


What would be script to pull existing database configuration. To check what is off or on
ALTER DATABASE [Pawan] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Pawan] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Pawan] SET CURSOR_CLOSE_ON_COMMIT OFF

When I ran script below I got this message

1:GRANT ADMINISTER BULK OPERATIONS TO [yourserverlogin]
2:GRANT INSERT on SCHEMA::dbo to Principal


Output

Msg 4621, Level 16, State 10, Line 1
Permissions at the server scope can only be granted when the current database is master
Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'Principal', because it does not exist or you do not have permission.

Can I add multiple accounts at once with the same permissions?

To create a database with the default settings you just need to provide the new database name to the CREATE DATABASE command.
To create a new user in the new database just run the CREATE USER command.
I also provided the required GRANT permission commands:

CREATE DATABASE new_database_name_here
GO

USE new_database_name_here
CREATE USER new_user_name_here FOR LOGIN existing_login_name_here;
GRANT INSERT, UPDATE new_user_name_here;  
GO

USE master
GRANT VIEW SERVER STATE TO existing_login_name_here

Select allOpen in new window

Need more clarification about the stats monitoring permissions. What do you exactly need to monitor?

Can I add multiple accounts at once with the same permissions?

Yes. In the GRANT command separate the user names with commas:

GRANT INSERT, UPDATE TO user1, user2, user3;  

Select allOpen in new window

I ran this query
CREATE DATABASE Test3
GO

USE Test3
CREATE USER ModD\Miatest
GRANT INSERT, ModD\Miatest FOR LOGIN existing_login_name_here;
GO

USE master
GRANT VIEW SERVER STATE TO ModD\Miatest

I got this message. Database has been created but domain account wasn't added

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '\'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '\'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '\'.

You need to use Square brackets around login name if it contains\ or space.

CREATE DATABASE Test3
GO

USE Test3
CREATE USER [ModD\Miatest]
GRANT INSERT [ModD\Miatest] FOR LOGIN youloginname;
GO

USE master
GRANT VIEW SERVER STATE TO [ModD\Miatest]

CREATE DATABASE Test3
 GO

 USE Test3
 CREATE USER [ModD\Miatest]
 GRANT INSERT [ModD\Miatest] FOR LOGIN youloginname;
 GO

 USE master
 GRANT VIEW SERVER STATE TO [ModD\Miatest]

output:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'ModD\Miatest'.
Msg 15151, Level 16, State 1, Line 3
Cannot find the login 'ModD\Miatest', because it does not exist or you do not have permission.

I am SA on SQL server. So I have permission.

What am I should put in youloginname? is it my domain\account or 'ModD\Miatest'?
GRANT INSERT [ModD\Miatest] FOR LOGIN youloginname;

Hi,

Syntax mate

CREATE DATABASE Test3
GO

USE Test3
CREATE USER [ModD\Miatest] FOR LOGIN [ModD\Miatest];
GO
GRANT INSERT ON SCHEMA :: dbo TO [ModD\Miatest];
GO

USE master
GRANT VIEW SERVER STATE TO [ModD\Miatest] 
GO

Select allOpen in new window

The script ran. Database has been created. DBO account has been created.
But, I got this message

Msg 15151, Level 16, State 1, Line 3
Cannot find the login 'ModD\Miatest', because it does not exist or you do not have permission.

This script needs to be able to
1. add account to SQL server level.
2. add multiple users to be database with DBO that can  
•      bulk updates
•      stats monitoring
•      VIEW SERVER STATE
•      Inserts
2. select different location for the data file and log file needs
3.

I am SA on this server.
But, it looks like I am getting DBO access to this database. So, I am assuming this is causing this message

Msg 15151, Level 16, State 1, Line 3
 Cannot find the login 'ModD\Miatest', because it does not exist or you do not have permission.

Create it then:

CREATE LOGIN [ModD\Miatest] FROM WINDOWS;

Select allOpen in new window

However, you're getting your permissions by membership in a group.

EXEC xp_logininfo 'ModD\Miatest'

Select allOpen in new window

Being in sysadmin server group grants all permissions.

Yes, you need to CREATE LOGIN first if don't exists yet.
Daniel already posted the correct command. This should run only once as you don't need to create the login every time you create a database because the login will be stored at the instance level.

What about if I want to add user to existing database with DBO access only?
How I can do that ?

USE dbName
GO
ALTER ROLE dbo ADD member <username>;
--or
sp_addrolemember 'dbo', 'username';
GO

Select allOpen in new window

I ran this script

USE Test7
GO
ALTER ROLE dbo ADD member [ModD\Miatest];

And I got this message
Msg 15151, Level 16, State 1, Line 1
Cannot alter the role 'dbo', because it does not exist or you do not have permission.

What am I doing wrong?

Sorry,

ALTER ROLE db_owner ADD MEMBER ...

Select allOpen in new window

Can you please explain step by step?

1. In order to create database first with location of mdf file on E:\ and ldf file on F:\ , what would be the query?
2. If the database exists and I want to add additional multiple user with DBO access to existing database, what would be the query?
3. The users with DBO access should have additional roles

•      bulk updates
•      stats monitoring
•      VIEW SERVER STATE
•      Inserts

What would be the query?

This will help me to understand this process

If you run the script with the user credentials then the user will be automatically the db_owner for that database.

What query do I need to run in order to grand DBO user roles to execute tasks in Database as follows

•      bulk updates
•      stats monitoring
•      VIEW SERVER STATE
•      Inserts

You're returning to the beginning of this thread and I don't think I'm able to explain better than I already did.
Hopefully some other Expert will have more success than me.

Here is what I put together. This script creates database, creates server level account, grant DBO access to database and set some rules.
However, I got some messages that I am not sure how to resolve. On the bottom of this page you can see additional comments and the output message.

--Script to create new db
CREATE DATABASE [TestDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\MSSQL11.SQL_ARTDB101\MSSQL\DATA\TestDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'TestDB_log', FILENAME = N'F:\MSSQL\LOG\TestDBlog.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [TestDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TestDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TestDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestDB] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [TestDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestDB] SET  DISABLE_BROKER
GO
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TestDB] SET  READ_WRITE
GO
ALTER DATABASE [TestDB] SET RECOVERY FULL
GO
ALTER DATABASE [TestDB] SET  MULTI_USER
GO
ALTER DATABASE [TestDB] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [TestDB] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
USE [TestDB]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [TestDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

--Create server level login (Windows authenticated logins)
CREATE LOGIN [ModD\Miatest] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

--To create the user associated with the login for the specific database you want to grant them access dbo.
USE [TestDB]
GO
CREATE USER [ModD\Miatest]FOR LOGIN [ModD\Miatest]
GO
ALTER USER [ModD\Miatest] WITH DEFAULT_SCHEMA=[dbo]

--Grant role to user
Go
USE [TestDB]
GO
EXEC sp_addrolemember N'db_datareader', N'ModD\Miatest'

--Add user to administer bulk operations
USE [master]
GO
GRANT ADMINISTER BULK OPERATIONS TO [ModD\Miatest]

--Add user to insert on schema
USE [master]
GO
GRANT INSERT on SCHEMA::dbo TO [ModD\Miatest]

--Add user to view server state
USE [master]
GO
GRANT VIEW SERVER STATE TO [ModD\Miatest]

----------------------------------------------------------------------------------------------------------

When I run this script once again I am getting this message below.
How I can fix this? I don't want to see this message.

Also, can you help me to improve this script so it can run without any  problems? This script looks a little bit messy.
Perhaps database creation can be shrink.

Msg 1801, Level 16, State 3, Line 3
Database 'TestDB' already exists. Choose a different database name.
Msg 15025, Level 16, State 2, Line 3
The server principal 'ModD\Miatest 'already exists.
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'ModD\Miatest' already exists in the current database.

That script is meant to run only once. When you run it a second time it will complain because the database and login are already created.

Msg 1801, Level 16, State 3, Line 3
Database 'TestDB' already exists. Choose a different database name.

>> Next time you are running then either delete the existing DB or use a new name or this error will come again. same is the case with other statements. What ever part is done you do not have to perform again.

Is there a way for the query to recognize that the database was already created so, the query will  skip database creation and go to next step?

What is the script needs to be modify to achieve that ?

michalek19, do you still need help with this question?