1) Stored Procedures: to grant execute permission on all the stored procedures in a database, use this (quick and dirty) solution:
use DATABASE_NAME
select 'grant execute on ' + specific_name + ' to [LOGIN_NAME] '
from information_schema.routines
where routine_type = 'PROCEDURE'
executing this after the obvious substitutions of LOGIN_NAME and DATABASE_NAME will return a bunch of lines like
grant execute on stored_procedure_name to [LOGIN_NAME]
if you copy and execute those lines, you're done.
2) Stored Procedures: another (less dirty) way to obtain the same result:
DECLARE @proc_name SYSNAME
DECLARE @sql VARCHAR(4000)
DECLARE @username VARCHAR(255)
SET @username = 'LOGIN_NAME_HERE'
SET @proc_name = ''
WHILE 1=1
BEGIN
SET @proc_name = (SELECT TOP 1 ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures here!
AND ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_NAME > @proc_name
ORDER BY ROUTINE_NAME
)
IF @proc_name IS NULL BREAK
SET @sql = 'GRANT EXECUTE ON ' + QUOTENAME(@proc_name) + ' TO ' + @username
EXEC (@sql)
--Print (@sql)
END
3) Shrink database and transaction log: Just a couple of instructions that sometimes are useful to shrink databases log files:
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( databasename_Log , 1)
DBCC SHRINKDATABASE (databasename, 10)
