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

Hi

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

The account should have proper role permission to execute database roles like

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

Regards, M
michalek19Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you can check for the existence of the database and the user but I'm not sure why you'll need that if you won't change the database and user names:
if not exists (select 1 from sys.databases where name = N'TestDB')
	CREATE DATABASE 'TestDB' 
GO 

USE TestDB
GO

IF NOT EXISTS (SELECT 1 FROM [sys].[database_principals] WHERE name = N'ModD\Miatest')
BEGIN
	CREATE USER [ModD\Miatest] FOR LOGIN [ModD\Miatest] WITH DEFAULT_SCHEMA=[dbo]
	GRANT INSERT on SCHEMA::dbo TO [ModD\Miatest]
END
GO

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

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window


Permission you can give like below
GRANT ADMINISTER BULK OPERATIONS TO [yourserverlogin]
GRANT INSERT on SCHEMA::dbo to Principal 

Open in new window

0
 
michalek19Author Commented:
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
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
michalek19Author Commented:
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.
0
 
michalek19Author Commented:
Can I add multiple accounts at once with the same permissions?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

Need more clarification about the stats monitoring permissions. What do you exactly need to monitor?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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;  

Open in new window

0
 
michalek19Author Commented:
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 '\'.
0
 
Pawan KumarDatabase ExpertCommented:
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]
0
 
michalek19Author Commented:
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;
0
 
Daniel_PLDB Expert/ArchitectCommented:
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

Open in new window

0
 
michalek19Author Commented:
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.
0
 
michalek19Author Commented:
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.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Create it then:
CREATE LOGIN [ModD\Miatest] FROM WINDOWS;

Open in new window


However, you're getting your permissions by membership in a group.
EXEC xp_logininfo 'ModD\Miatest'

Open in new window


Being in sysadmin server group grants all permissions.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
michalek19Author Commented:
What about if I want to add user to existing database with DBO access only?
How I can do that ?
0
 
Daniel_PLDB Expert/ArchitectCommented:
USE dbName
GO
ALTER ROLE dbo ADD member <username>;
--or
sp_addrolemember 'dbo', 'username';
GO

Open in new window

0
 
michalek19Author Commented:
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?
0
 
Daniel_PLDB Expert/ArchitectCommented:
Sorry,
ALTER ROLE db_owner ADD MEMBER ...

Open in new window

0
 
michalek19Author Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you run the script with the user credentials then the user will be automatically the db_owner for that database.
0
 
michalek19Author Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
michalek19Author Commented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
michalek19Author Commented:
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 ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
michalek19, do you still need help with this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.