Friday, January 05, 2007

Survival: Sql Server, grant on all stored procedure and shrink db and logs

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)

No comments: