Tag Archives: stored procedure

MSSQL Grant Execute Stored Procedure only

There are cases that we only want to grant read-write only access then also enable users to execute stored procedure with in the SQL Server database.

In order to do this we will need to:

Create a new Role named, db_exec_storedprocedure

CREATE ROLE db_exec_storedprocedure
Grant that role with execute stored procedure

GRANT EXECUTE TO db_exec_storedprocedure
Then add the user as a member of that role.

That’s it you can now grant specific permission to execute stored procedure.

God Bless!
Thanks,
Thomie

MSSQL – Drop all SP and Table on a Database

Execute this with Precaution

USE YourDataBaseName
declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = ‘p’
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec(‘drop procedure [‘ + @procName+’]’)
fetch next from cur into @procName
end
close cur
deallocate cur
EXEC sp_MSforeachtable @command1 = “DROP TABLE [?]”