C Sharp Logs: June 2009

Thursday, 25 June 2009

   1:  SET NOCOUNT ON

2:

   3:  --Execute two select statements.

   4:  SELECT 'IF NOT EXISTS (SELECT NULL FROM sys.sysusers WHERE [name] = ' +

   5:  QUOTENAME(dp2.[name],'''') +  ') ' + CHAR(13) + '  CREATE USER ' +

   6:  QUOTENAME(dp2.name) + ' FOR LOGIN ' +

   7:  QUOTENAME(dp2.name)

   8:  FROM sys.database_principals dp

   9:  JOIN sys.database_role_members rm ON dp.principal_id = rm.role_principal_id

  10:  JOIN sys.database_principals dp2 on dp2.principal_id = rm.member_principal_id

  11:  WHERE dp2.[name] != 'dbo'

  12:  AND dp.[name] = 'GroupNameHere'

13:

  14:  SELECT 'CREATE ROLE [GroupNameHere] AUTHORIZATION [dbo]'

  15:  UNION

  16:  --Returns database role members.

  17:  SELECT

  18:  'EXEC sp_addrolemember N''' + dp.[name] + ''', N''' + dp2.[name] + ''''

  19:  FROM sys.database_principals dp

  20:  JOIN sys.database_role_members rm ON dp.principal_id = rm.role_principal_id

  21:  JOIN sys.database_principals dp2 on dp2.principal_id = rm.member_principal_id

  22:  JOIN sys.server_principals sp ON sp.sid = dp2.sid

  23:  WHERE dp2.[name] != 'dbo'

  24:  AND dp.[name] = 'GroupNameHere'

   1:  alter procedure [dbo].[usp_SomeName] (

   2:  @value varchar(100)

   3:  ) as

   4:  declare @id int

   5:  create table #out(id int, value varchar(100))

   6:  INSERT INTO test2

   7:  (value)

   8:  output inserted.* into #out

   9:  VALUES

  10:  (@value)

  11:  return (select top 1 id from #out)

   1:  begin catch

   2:  DECLARE

   3:  @ErrorMessage NVARCHAR(4000),

   4:  @ErrorNumber INT,

   5:  @ErrorSeverity INT,

   6:  @ErrorState INT,

   7:  @ErrorLine INT,

   8:  @ErrorProcedure NVARCHAR(200);

   9:  -- Assign variables to error-handling functions that capture information for RAISERROR.

  10:  SELECT

  11:  @ErrorNumber = ERROR_NUMBER(),

  12:  @ErrorSeverity = ERROR_SEVERITY(),

  13:  @ErrorState = ERROR_STATE(),

  14:  @ErrorLine = ERROR_LINE(),

  15:  @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

  16:  -- Build the message string that will contain original error information.

  17:  SELECT @ErrorMessage = 'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();

  18:  -- Raise an error: msg_str parameter of RAISERROR will contain the original error information.

  19:  RAISERROR

  20:  (

  21:  @ErrorMessage,

  22:  @ErrorSeverity,

  23:  1,

  24:  @ErrorNumber, -- parameter: original error number.

  25:  @ErrorSeverity, -- parameter: original error severity.

  26:  @ErrorState, -- parameter: original error state.

  27:  @ErrorProcedure, -- parameter: original error procedure name.

  28:  @ErrorLine -- parameter: original error line number.

  29:  );

  30:  end catch

set @msg = convert(varchar,getdate(),108) + ' - ' + some message here ...'
raiserror(@msg,0,1) with nowait

   1:  SELECT name, type_desc, modify_date

   2:  FROM sys.objects

   3:  WHERE OBJECT_DEFINITION(object_id) LIKE '%somestring%'