, , , , , , , , , .
DDL, DDL, DDL. , .
: db_datareader db_datawriter DML . .
, . , , SP, . ( , .) , , .
SQL Server 2000, SQL Server 2008 . .
DBO SA :
create table dbo.SO (PK int identity constraint SO_PK primary key
, SomeData varchar(1000)
)
DML:
create procedure dbo.InsertSO (@SomeData varchar(1000)) as
begin
insert into dbo.SO (SomeData) values (@SomeData)
return SCOPE_IDENTITY()
end
go
create procedure dbo.SelectSO (@PK int=null) as
begin
if @PK is not null
select PK, SomeData from dbo.SO where PK = @PK
else
select PK, SomeData from dbo.SO
end
go
create procedure dbo.CountSO as
begin
select COUNT(*) as CountSO from SO
end
go
create procedure dbo.DeleteSO (@PK int=null ) as
begin
if @PK is not null
delete dbo.SO where PK = @PK
else
delete dbo.SO
end
go
create procedure dbo.UpdateSO (@PK int, @NewSomeData varchar(1000)) as
begin`
update dbo.SO
set SomeData = @NewSomeData
where PK = @PK
end
go
create procedure dbo.TruncateSO as
begin
truncate table dbo.SO
end
go
dbo, SQL:
declare @PK_to_update int
insert into dbo.SO (SomeData) values ('Hello world!')
set @PK_to_update = SCOPE_IDENTITY()
declare @PK_to_delete int
insert into dbo.SO (SomeData) values ('Goodbye cruel world!')
set @PK_to_delete = SCOPE_IDENTITY()
insert into dbo.SO (SomeData) values ('Four score and seven years ago...')
select PK, SomeData
from dbo.SO
delete dbo.so
where PK = @PK_to_delete
update dbo.SO
set SomeData = 'Hello Milky Way!'
where PK = @PK_to_update
select PK, SomeData
from dbo.SO
truncate table dbo.SO
select COUNT(*) as CountSO from dbo.SO
go
declare @PK_to_update int
exec @PK_to_update = dbo.InsertSO 'Hello world!'
declare @PK_to_delete int
exec @PK_to_delete = dbo.InsertSO 'Goodbye cruel world!'
exec dbo.InsertSO 'Four score and seven years ago...'
exec dbo.SelectSO
exec dbo.DeleteSO @PK_to_delete
exec dbo.UpdateSO @PK_to_update, 'Hello Milky Way!'
exec dbo.SelectSO
exec dbo.TruncateSO
exec dbo.CountSO
DDL :
create procedure dbo.DropSO as
begin
drop table dbo.SO
end
go
begin transaction
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'SO'
exec dbo.DropSO
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'SO'
rollback transaction
. . (, . , , .)
exec sp_addlogin @loginame = 'SoLogin' , @passwd = 'notsecure', @defdb = 'Scratch'
exec sp_adduser @loginame = 'SoLogin', @name_in_db = 'SoUser'
go
grant execute on dbo.InsertSo to SoUser
grant execute on dbo.InsertSO to SoUser
grant execute on dbo.SelectSO to SoUser
grant execute on dbo.CountSO to SoUser
grant execute on dbo.DeleteSO to SoUser
grant execute on dbo.UpdateSO to SoUser
grant execute on dbo.TruncateSO to SoUser
grant execute on dbo.DropSO to SoUser
SoLogin. DML:
declare @PK_to_update int
insert into dbo.SO (SomeData) values ('Hello world!')
set @PK_to_update = SCOPE_IDENTITY()
declare @PK_to_delete int
insert into dbo.SO (SomeData) values ('Goodbye cruel world!')
set @PK_to_delete = SCOPE_IDENTITY()
insert into dbo.SO (SomeData) values ('Four score and seven years ago...')
select PK, SomeData
from dbo.SO
delete dbo.so
where PK = @PK_to_delete
update dbo.SO
set SomeData = 'Hello Milky Way!'
where PK = @PK_to_update
select PK, SomeData
from dbo.SO
truncate table dbo.SO
go
select COUNT(*) as CountSO from dbo.SO
go
drop table dbo.so
, :
Msg 229, Level 14, State 5, Line 2
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 6
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 9
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 14
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 14
The DELETE permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 17
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 17
The UPDATE permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 21
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 1088, Level 16, State 7, Line 24
Cannot find the object "SO" because it does not exist or you do not have permissions.
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 3701, Level 14, State 20, Line 2
Cannot drop the table 'SO', because it does not exist or you do not have permission.
DML:
declare @PK_to_update int
exec @PK_to_update = dbo.InsertSO 'Hello world!'
declare @PK_to_delete int
exec @PK_to_delete = dbo.InsertSO 'Goodbye cruel world!'
exec dbo.InsertSO 'Four score and seven years ago...'
exec dbo.SelectSO
exec dbo.DeleteSO @PK_to_delete
exec dbo.UpdateSO @PK_to_update, 'Hello Milky Way!'
exec dbo.SelectSO
, SP , SoUser .
:
exec dbo.TruncateSO
go
exec dbo.DropSO
:
Msg 1088, Level 16, State 7, Procedure TruncateSO, Line 4
Cannot find the object "SO" because it does not exist or you do not have permissions.
Msg 3701, Level 14, State 20, Procedure DropSO, Line 4
Cannot drop the table 'SO', because it does not exist or you do not have permission.