Click here to check my latest exciting videos on youtube
Search Mallstuffs

Flag Counter
Spirituality, Knowledge and Entertainment

Locations of visitors to this page

Latest Articles

Move to top
When to use optional parameters of creating database statement
Posted By Sarin on Aug 26, 2012     RSS Feeds     Latest Hinduism news

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

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
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
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
EXEC sp_dboption  'Test', 'autoshrink', 'False'
auto shrink: 'True' indicates this database can be shrunk in size safely

Creating database with ANSI null default
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
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
( NAME = Payroll_dat,
   FILENAME = 'c:\program files\microsoft sql servermssql\data\payrolldat.mdf',
   SIZE = 20MB,
   MAXSIZE = 70MB,
( NAME = 'Payroll_log',
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\payroll.ldf',
   SIZE = 10MB,
   MAXSIZE = 40MB,

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 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.
WITH PASSWORD = 'jtsDBpswd';
USE test;
exec sp_changedbowner ' TestUser '

Note: Images used on this website are either a production of Bhaktivedanta Book Trust(, 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.
Related Articles
Generating unique random numbers and date
Calling web service asynchronously using jquery ajax
Get All Tables in a Database
Ways of generating random password or string
Get comma separated result of a database query
Testing performance issues with reflection
When to use optional parameters of creating database statement
Delete duplicate records from table in SQL
Trigger custom or manual postback in ASP.NET

Post Comment