create login customer1 with password = 'password1'
create login [praclab4customer_vinod] from windows
exec sp_helplogins customer1
exec sp_helplogins [praclab4customer_vinod]
use shopping1
create user customer1 for login customer1
sp_helplogins customer1
create table profit (id int)
grant select on profit to customer1
insert into profit values (1)
grant insert on profit to customer1
deny insert on profit to customer1
deny select on profit to customer1
grant update on profit to customer1
grant select on profit to customer1 with grant option
create user dummy for login dummy
sp_helprotect 'profit' --to view permissions granted or denied****
revoke select on profit to customer1 with cascade
use shopping1
create table product (id int)
sp_helprotect 'product'
--============following both are same--=============
exec sp_addrole itadmins1 --a database role created shud be assigned to the user
create role itadmins --a database role created shud be assigned to the user
--==================================================
sp_helprole --in case of server level we use sp_helpsrvrole
sp_helprolemember itadmins
sp_addrolemember 'itadmins1', customer1
sp_helpuser customer1
sp_droprolemember 'itadmins1', customer1
sp_helplogins customer1
sp_helprotect 'product'
grant select on product to itadmins
create schema manager
create table manager.project (id int,name varchar(10))
sp_addrolemember 'db_owner', customer1
sp_droprolemember 'db_owner',customer1
create table manager.insentive (id int)
sp_addrolemember 'db_datareader', itadmins
sp_helprolemember 'db_datareader'
sp_droprolemember 'db_datareader',customer1
sp_addrolemember 'db_datareader', customer1
sp_droprolemember 'db_datareader',itadmins
--Application Roles
create table balance (id int)
sp_addapprole 'role1', 'password12'
sp_helprole
grant select on balance to role1
sp_helprotect balance
sp_setapprole 'role1', 'password12'
select * from balance
insert into balance values (1)
--If u close this session u will be forcebily grabbed wit the permissions
create table accounts (acntno int, crdtno varbinary(255))
create masterkey
--Using keys
create table accounts (acntno int, crdtno varbinary(255))
create master key encryption by password = 'password@123'
--new conceptin 2005-> certificates
create certificate crt1 with subject = 'hello'
--triple_des is 128bit algorithm
create symmetric key smkey1 with algorithm = triple_des encryption by certificate crt1
open symmetric key smkey1 decryption by certificate crt1
--encrypting data
declare @key uniqueidentifier
set @key = key_guid('smkey1')
insert into accounts values (1,encryptbykey(@key,'111-222-333-444'))
select * from accounts
--result=1,0x00F3C7FAE2A53249B54BE331BC22E380010000006A5E0908875E3FBC7C921C72551097EF4BADF78E84F315C022DB5326D6819D9A
--To see the data
select acntno,cast(decryptbykey(crdtno) as varchar(255)) from accounts
close symmetric key smkey1
------------------------------------------------
dbcc checkdb
dbcc opentran()
sp_helpdb
dbcc sqlperf (logspace)
create proc temp as dbcc sqlperf (logspace)
set nocount on
create table #temp1 (dbname nvarchar(max), logsize float, logspaceused float, status int)
insert into #temp1 exec temp
select * from #temp1
sp_helpdb 'slktestdb'
select *from sys.databases
select *from sys.sysdatabases
select name,recovery_model_desc from sys.databases
alter database slktestdb
set recovery bulk_logged
alter database slktestdb
set recovery full
sp_dboption 'slktestdb'
sp_dboption 'slktestdb', 'trunc. log on chkpt.','false'
select * from sys.sysprocesses where dbid = db_id('slktestdb')
use master
restore database slktestdb with recovery
create database slkbackupdb1
on primary
(
name='slkbackupdb1',
filename = 'c:Backup1.mdf',
size = 10mb,
maxsize = 20mb
)
log on
(
name='slkbackupdb1_log',
filename = 'c:Backup1_log.ldf',
size = 10mb,
filegrowth = 20%,
maxsize = 20mb
)
sp_addumpdevice 'disk','bak_device_1','C:sqllabkpak_device_1.bak'
backup database slkbackupdb1 to disk = 'praclab2sssvinod.bak'
backup database slktestdb to bak_device_1
backup log slktestdb to bak_device_1
backup database slkbackupdb1 to disk='C:sqllabkpak_diff.bak' with differential
sp_addumpdevice 'disk','test1','C:sqllabkp est1.bak'
backup database slkbackupdb1 to test
restore filelistonly from test
restore headeronly from test
backup database slktestdb to test
restore headeronly from test
restore filelistonly from test
backup database master to test with init --overwrite the backup content(init)
backup log slktestdb to disk = 'c:sqllabkp ailLOG.bak' with norecovery,no_truncate --norecovery=to tell sql server is restoring
alter database slktestdb
modify filegroup fg1 readonly
select * from sys.filegroups
backup database slktestdb read_write_filegroups to disk = 'c:sqllabkppartial.bak'
-- Backup device mirroring
sp_addumpdevice 'disk','bak1','C:sqllabkpak1.bak'
sp_addumpdevice 'disk','bak1_mirror','C:sqllabkpak1_mirror.bak'
backup database slktestdb to bak1
mirror to bak1_mirror with format
select @@servername,@@version
--checksum of a databse
backup database slktestdb to disk = 'C:sqllabkpchk.bak' with checksum
restore verifyonly from disk = 'C:sqllabkpchk.bak'
backup database backupdb to disk='C:sqllabackupdb.bak'
use backupdb
create table test
(
id int,
name varchar(20)
)
backup database backupdb to disk = 'c:sqllabackup_diff.bak' with differential
insert into test values (1,'adarsh')
insert into test values (2,'vinod')
insert into test values (3,'vinnay')
select * from test
backup log backupdb to disk = 'c:sqllabackup_log.bak'
insert into test values (4,'dsaod')
insert into test values (5,'dfds')
use master
restore database backupdb from disk = 'c:sqllabackupdb.bak' with norecovery, replace
restore database backupdb from disk = 'c:sqllabackup_diff.bak' with norecovery
restore log backupdb from disk = 'c:sqllabackup_log.bak' with norecovery
restore database backupdb with recovery
select * from test
backup database backupdb to disk='C:sqllabackupdb.bak'
select getdate()
insert into test values (5,'dfds')
backup log backupdb to disk = 'c:sqllabackup_log.bak'
restore database backupdb from disk = 'c:sqllabackupdb.bak' with norecovery
restore log backupdb from disk = 'c:sqllabackup_log.bak' with stopat = 'aug 02, 2010 04:39:27.770 PM'
select * from test
----------------------------------------
use shopping1
select * from profit
insert into profit values (3)
update profit set id = 100 where id =1
grant select on profit to dummy
deny select on profit to dummy
select * from product
select * from skills1
create table skills1 (id int)
select * from insentive
use master
select * from shopping1.manager.skills
---------------------------------------------------------
dbcc checkdb
dbcc checkalloc
dbcc help (checkalloc)
alter database slktestdb set emergency
alter database slktestdb set online
alter database slktestdb set read_only
alter database slktestdb set read_write
alter database slktestdb set single_user
use testdb
use slktestdb
alter database slktestdb set multi_user
use testdb
alter database slktestdb set restricted_user
use slktestdb
create table test (id int)
insert into test values (1)
select * from test
begin tran t1
insert into test values(2)
commit tran t1
rollback tran t1
begin tran t1
insert into test values(3)
dbcc opentran()
sp_who2
commit tran t1
dbcc inputbuffer(56)
sp_helpdb slktestdb
alter database slktestdb
modify file
(
name = 'slktestdb1',
size = 3mb
)
create database example
sp_helpdb example
sp_helptext 'sp_attach_db'
sp_attach_db
drop database example
sp_helpdb slktestdb
sp_database
create database vinod
sp_helpdb tempdb