how to create tcl command and dcl command in sql server 2005 with example

Posted by Venky.Net under Sql Server on 4/30/2012 | Points: 10 | Views : 2824 | Status : [Member] | Replies : 1
hi
i am trying create tcl commands and dcl command but i can't create.anyone please give me answer with example




Responses

Posted by: Funda on: 4/30/2012 [Member] Starter | Points: 25

Up
0
Down
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



Venky.Net, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response