Task-1: HOW TO CREATE THE
DATABASE:
There are various methods to create a
database:
Method-1: With the help
of GUI:
- In
Object Explorer, connect to an instance of the SQL Server Database Engine
and then expand that instance.
- Right-click
Databases, and then click New Database.
- In
New Database, enter a database name.
- To
create the database by accepting all default values, click OK.
Otherwise , continue
with the following optional steps.
- To
change the owner name, click (…) to select another owner.
- To
change the default values of the primary data and transaction log files,
in the Database files grid, click the appropriate cell and enter the new
value.
- To
change the collation of the database, select the Options page, and then
select a collation from the list.
- To
change the recovery model, select the Options page and select a recovery
model from the list.
- To
change database options, select the Options page, and then modify the
database options.
- To
add a new filegroup, click the Filegroups page. Click Add and then enter
the values for the filegroup.
- To
add an extended property to the database, select the Extended Properties
page.
- In
the Name column, enter a name for the extended property.
- In
the Value column, enter the extended property text. For example, one or
more statements that describe the database.
- To
create the database, click OK.
Method-2: Using Script:
CREATE
DATABASE
[DB_name] ON
PRIMARY
(
NAME =
N'
DB_name ', FILENAME = N'E:\SQL\Data\DB_name.mdf' , SIZE = ‘size’ , FILEGROWTH = ‘filegrowth’ )
LOG ON
(
NAME =
N'DB_name_log', FILENAME = N'F:\SQL\Log\DB_name_log.ldf' , SIZE = ‘size’ , FILEGROWTH = ‘filegrowth’ )
GO
For example:
CREATE
DATABASE
[saily] ON
PRIMARY
(
NAME =
N'saily', FILENAME = N'E:\SQL\Data\saily.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB )
LOG ON
(
NAME =
N'saily_log', FILENAME = N'F:\SQL\Log\saily_log.ldf' , SIZE = 38912KB , FILEGROWTH = 25600KB )
GO
Task-2: HOW TO CHANGE DATABASE OWNER TO SA :
METHOD-1:
- Run
the following query and the database owner will be changed to SA.
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] to sa;
Example: ALTER AUTHORIZATION ON DATABASE::fred to sa;
METHOD-2:
· Run the following query and the
database owner will be changed to SA
USE
[Db_name]
EXEC
sp_changedbowner 'sa'
Example:
USE
saily
EXEC sp_changedbowner 'sa'
METHOD-3:
- We
can change the database owner through GUI.
- You can actually change it in SQL
Server Management Studio under Database
/ Properties / Files
- Right
click database ->click properties ->files ->Owner, and change the
owner to SA.
METHOD-4:
- To change all the databases present in the server to sa, follow the
following steps:
Step 1:
Execute the following
script:
declare @name varchar(100)
declare
@cmd nvarchar(200)
begin
declare
db_cur cursor
for
select
name from
sys.databases
where
name not
in('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open
db_cur
fetch
next
from
db_cur into
@name
while
@@FETCH_STATUS=0
begin
select
@cmd=N'ALTER
AUTHORIZATION ON DATABASE::'+@name
set
@cmd =@cmd+' to sa'
print @cmd
fetch
next
from
db_cur into
@name
end
close
db_cur
deallocate
db_cur
end
Step 2:
copy the script from
messages
Step 3 :
Execute the script.
Then you find that all the databases owner is changed to sa.
To check the owner
name of the database use the following methods:
Method-(i):
Right click database
->click properties ->files ->Owner.
Method-(ii):
You
can also check the owner name using the below query after you modified.
select
suser_sname(owner_sid) from sys.databases
where
name =
'databasename'
IMPORTANT NOTE:
The databases whose
ownership can’t be changed:
1) Master
2) Model
3) Tempdb
By default their
owner will be ‘sa’
If you try to change
the owner the following error message comes:
“Cannot change the
owner of the master, model, tempdb or distribution database.”
We can change the
owner of msdb in system databases.
