DCL
GRANT - Gives user's access privileges to database
REVOKE - Withdraws user's access privileges to database given with the GRANT command
DCL COMMANDS
1)
GRANT SELECT, INSERT, UPDATE, DELETE ON album, song TO Elvis WITH ADMIN OPTION
The database may be accessed by a wide public, grant them only the SELECT statement.
REVOKE INSERT, UPDATE, DELETE ON album, song TO PUBLIC
2)
Create a group of users, which can add and modify records, but not delete them.
CREATE ROLE editors
GRANT SELECT, INSERT UPDATE ON album, song TO editors
3) You have got new guy in your team and you want to authorize him to delete records. His user name is JBlack
GRANT editors, DELETE ON album, song TO JBlack
4) Once again you are alone to maintain the CD catalog, because all your co-workers are gone. Only the editors role is left and useless. Delete it.
DROP ROLE editors
TCL
COMMIT - Saves work done in transactions
ROLLBACK - Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION - Sets a savepoint within a transaction
USE tempdb;
GO
CREATE TABLE ValueTable ([value] int;)
GO
DECLARE @TransactionName varchar(20) = 'Transaction1';
--The following statements start a named transaction,
--insert two rows, and then roll back
--the transaction named in the variable @TransactionName.
--Another statement outside of the named transaction inserts two rows.
--The query returns the results of the previous statements.
BEGIN TRAN @TransactionName
INSERT INTO ValueTable VALUES(1), (2);
ROLLBACK TRAN @TransactionName;
INSERT INTO ValueTable VALUES(3),(4);
SELECT [value] FROM ValueTable;
DROP TABLE ValueTable;
--Results
--value
-------------
--3
--4
Please be specific ?.
Mark this as answered if it is useful