Thursday 25 June 2009

XYNTService: start a program automatically

http://www.codeproject.com/KB/cpp/XYNTServiceWrapper.aspx

Get database roles users principles


   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'

Wednesday 24 June 2009

SQL return the insert row id


   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)

SQL Error catching


   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

Wednesday 17 June 2009

SQL search for object by name, for one database only


   1:  SELECT name, type_desc, modify_date
   2:  FROM sys.objects
   3:  WHERE OBJECT_DEFINITION(object_id) LIKE '%somestring%'