Consider the following two examples:

Dim UserInput As String = "1'); DELETE FROM tblInjectionAttack;"
Dim SQL As String
SQL = "INSERT INTO tblInjectionAttack (Column1) VALUES ('" & UserInput & "')"
		

CREATE PROCEUDRE InsertInjection
	@UserInput NVARCHAR(500)
AS
	INSERT INTO tblInjectionAttack (Column1) VALUES (@UserInput)
	--EXEC InsertInjection '1''); DELETE FROM tblInjectionAttack;'
		

In the first example, the SQL query will be injected and the attacker will be able to execute any command that they have access to. In the second example, the attacker’s SQL injection will be saved in Column1 and won’t be executed at all.

If you create a new user in a database, by default, that user has no privileges; however, if you need to remove existing privileges from an account, you can do so by executing the following query:

--To deny access on one single table:
DENY SELECT,INSERT,UPDATE,DELETE ON [TableName] TO [Username]

<span id="more-11"></span>

--To deny access to all existing tables,
--execute the printout of the following query:
DECLARE @Username NVARCHAR(256)
SET @Username = 'JoeUser'
DECLARE @StoredProcedure NVARCHAR(256)
DECLARE cursor_sp CURSOR FAST_FORWARD FOR
	SELECT name FROM sysobjects WHERE xtype = 'u'
OPEN cursor_sp
	FETCH NEXT FROM cursor_sp INTO @StoredProcedure
		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT 'DENY SELECT,INSERT,UPDATE,DELETE ON '+@StoredProcedure+' TO '+@Username
			PRINT 'GO'
			FETCH NEXT FROM cursor_sp INTO @StoredProcedure
			--Note: You may use EXECUTE() instead of PRINT if you
			--		want the procedure to execute the command for you
		END
CLOSE cursor_sp
DEALLOCATE cursor_sp
		

Once an account’s privileges have been removed, you can give it EXECUTE permissions using the following query:

--To grant EXECUTE on one single stored procedure:
GRANT EXECUTE ON [StoredProcedureName] TO [Username]

--To grant EXECUTE on all existing procedures,
--execute the printout of the following query:
DECLARE @Username NVARCHAR(256)
SET @Username = 'YourUsername'
DECLARE @StoredProcedure NVARCHAR(256)
DECLARE cursor_sp CURSOR FAST_FORWARD FOR
	SELECT name FROM sysobjects WHERE xtype = 'p'
	OPEN cursor_sp
		FETCH NEXT FROM cursor_sp INTO @StoredProcedure
			WHILE @@FETCH_STATUS = 0
			BEGIN
				PRINT 'GRANT EXECUTE ON '+@StoredProcedure+' TO '+@Username
				PRINT 'GO'
				FETCH NEXT FROM cursor_sp INTO @StoredProcedure
				--Note: You may use EXECUTE() instead of PRINT if you
				--		want the procedure to execute the command for you
			END
	CLOSE cursor_sp
DEALLOCATE cursor_sp