Create Statement is used to create a new database and specifying additional options on how you want to create this database. Some of the options are specifying Files used to store the database, a database snapshot, or attaches a database from the detached files of a previously created database.
Create simple database with no parameters
CREATE DATABASE Payroll Create a database called "Payroll" with database data files and transaction logs to be created automatically in the default location.
Adding optional Arguments while creating database
Creating database with option of clearing resources when the last user logs off
CREATE DATABASE Test
EXEC sp_dboption 'Test', 'autoclose', 'True'
autoclose: 'True' ensures that the resources are cleared when the last user logs off.
Creating database with read-only data
CREATE DATABASE Test
EXEC sp_dboption 'Test', 'read only', 'True'
Read only: 'True' ensures no modifications to the data can be made.
Creating database with access to only created user
CREATE DATABASE Test
EXEC sp_dboption 'Test', 'dbo use', 'False'
dbo use: 'True' means that only the user ID that created the database has access and can use the database. Any other ID will be refused
Creating database with auto shrink option
CREATE DATABASE Test
EXEC sp_dboption 'Test', 'autoshrink', 'False'
auto shrink: 'True' indicates this database can be shrunk in size safely
Creating database with ANSI null default
CREATE DATABASE Test
EXEC sp_dboption 'Test', 'ANSI null default', 'False'
ANSI null default - 'True' ensures SQL Server follows the rules to see if a column can allow NULL values.
Creating database with access to only one user at a time
CREATE DATABASE Test
EXEC sp_dboption 'Test', 'single', 'False'
single: 'True' ensures only one user has access to the database at a time. Any subsequent request by any other users will be refused
Create database by setting the mdf file, size, maxsize and file growth rate
CREATE DATABASE Payroll
ON
( NAME = Payroll_dat,
FILENAME = 'c:\program files\microsoft sql servermssql\data\payrolldat.mdf',
SIZE = 20MB,
MAXSIZE = 70MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = 'Payroll_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\payroll.ldf',
SIZE = 10MB,
MAXSIZE = 40MB,
FILEGROWTH = 5MB )
First name and location - Database data file
Second name and location - Database transaction log file
SIZE- Initial size of the associated file
MAXSIZE- Maximum size of the associated file
FILEGROWTH- Growth increment of each file
Difference between CREATE DATABASE and CREATE ANY DATABASE
Difference is in types of permissions. CREATE DATABASE only looks into the security context of the currently used database, while CREATE ANY DATABASE looks on all lists of logins on the SQL Server.
So, when you grant CREATE DATABASE, you must actually have a user in master to grant this permission.
use master2 ; 1 GRANT 1 CREATE 1 DATABASE 1 to TestUser3
Msg 15151, Level 16, State 1, Line 1 Cannot finds the user 'TestUser', because it does not exist or you do not have permission.
Since the user testuser does not exist in database, we got the above error.
To resolve this, create a user in master, grant this user create database, and then you can make databases. This also means that the testuser doesn’t go outside the master database to look out for logins or user.
use master2 ; 1 GRANT 1 CREATE 1 ANY 1 DATABASE 1 to TestUser3
Command(s) completed successfully.
This statement succeeds because CREATE ANY DATABASE looks into the list of logins for the current SQL instance, not just the users in the currently used database. use test2 ; 1 GRANT 1 CREATE 1 DATABASE 1 to TestUser3
Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'TestUser', because it does not exist or you do not have permission.
This error is same as the first example. To solve this error message, create the user in this test database. use test2 ; 1 GRANT 1 CREATE 1 ANY 1 DATABASE 1 to TestUser3
Msg 4621, Level 16, State 10, Line 1 Permissions at the server scope can only be granted when the current database is master
As explained in the second query, CREATE ANY DATABASE consults the list of logins in the current SQL instance. Since I am attempting to grant a server-level permission somewhere other than master, SQL Server throws out with this message use test2 ; 1 create 1 user TestUser2 ; 1 grant 1 create 1 database 1 to TestUser3
As shown above, we are trying to grant CREATE DATABASE to the test database but permission can only be granted in the master database. So, we get the below error:
Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
To resolve this issue, create the user and then change the database owner to your newly created user.
CREATE LOGIN jtsDBuser
WITH PASSWORD = 'jtsDBpswd';
USE test;
exec sp_changedbowner ' TestUser '
Note: Images used on this website
are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon
Foundation or were found in google search under "Free to use and share". If any
of the images presented here violates copyright issues or infringes anyone copyright
or are not under "Fair use", then please bring it to our notice. Read
Disclaimer for more.
Share this to your friends. One of your friend is
waiting for your share.