Search a text in stored procedure source code

use master

go

-- =============================================

-- Description: Search a text in stored procedure source code.

-- @text - any text to find, search is done by like '%text%'

-- @dbname - database where to search,

--         - if omitted, all databases in the SQL server instance

-- =============================================

alter PROCEDURE [dbo].[sp_FindTextInsideProcedure]

  @text varchar(250),

  @dbname varchar(64) = null ,

  @OrderByColumn int = 1 ,

  @Level int = null

AS BEGIN

SET NOCOUNT ON;

if ( @dbname is null or @dbname like '%[_]%' or @dbname like '%[%]%' ) and @Level is null

  begin

  /*

   -- cursor programming

  

    --enumerate all databases.

  DECLARE #db CURSOR FOR Select Name from master..sysdatabases where ( @dbname is null or Name like @dbname   )

  declare @c_dbname varchar(64)

     

  OPEN #db FETCH #db INTO @c_dbname

  while @@FETCH_STATUS <> -1 --and @MyCount < 500

   begin

     execute sp_FindTextInsideProcedure @text=@text, @dbname = @c_dbname

     FETCH #db INTO @c_dbname

   end 

  CLOSE #db DEALLOCATE #db

 

  */

      -- no cursor

      --enumerate all databases.

      Select Name

      into #db

      from master..sysdatabases

      where ( @dbname is null or Name like @dbname   )

      declare @c_dbname varchar(64)

      set @c_dbname = (select top 1 Name from #db)

      declare @result table ( db varchar(64) , name varchar(256) , [definition] nvarchar(max))

      while ( @c_dbname is not null )

      begin

            insert into @result

            execute sp_FindTextInsideProcedure @text=@text, @dbname = @c_dbname , @Level =1

            delete #db

            where name = @c_dbname

            -- get the next database name

            set @c_dbname = (select top 1 Name from #db)

      end 

      drop table #db

      select * from @result r

      order by case @OrderByColumn

                              when 1 then r.db

                              when 2 then r.name 

                              when 3 then r.[definition] 

                              else  r.db

                  end

     

end --if @dbname is null

else

begin --@dbname is not null

  declare @sql varchar(4000)

  --create the find like command

  select @sql = 'select ''' + @dbname + ''' as db, o.name,m.definition '

                        + ' from ['+@dbname+'].sys.sql_modules m '

                        + ' inner join ['+@dbname+']..sysobjects o '

                        + '         on m.object_id=o.id'

                        + ' where [definition] like ''%'+@text+'%'''

  if (@Level is null)

  begin

      select @sql = @sql + 'order by case ' + convert(varchar , @OrderByColumn ) 

                        + '                     when 1 then ''' + @dbname + ''''

                        + '                     when 2 then  o.name  '

                        + '                     when 3 then m.definition  '

                        + '                     else   ''' + @dbname + ''''

                        + '         end'

  end

  execute (@sql)

end --@dbname is not null

END

Xp_cmdshell and permissions: How to use a proxy to run OS Command from SQl server agent (without administrator account)

Xp_cmdshell and permissions

This blog post was inspired from a newsgroup discussion. The question basically is:
What do you need to do in order to use xp_cmdshell?

Note that there are obvious security implications of doing this. (I'm not recommending usage of xp_cmdshell in general, this is atechnical blog post!) We first need to think about what happens here, from an architectural level:

Somebody who has logged in to SQL Server executes xp_cmdshell. For this, SQL Server need to spawn a process in Windows. A process in Windows need to execute in a Windows user account.

So, what Windows account is used? If the SQL Server login who is executing xp_cmdshell is sysadmin, then SQL Server will use the service account (it will not "pretend to be somebody else"). But if the login isn't sysadmin, then we need to configure what Windows account to be used (using sp_xp_cmdshell_proxy_account). Note that this configuration is the same for all non-sysadmins!

But there's a little bit more to it. Below is an outline of what need to be done. Step 2 and 3 are only needed if the one who is to execute xp_cmdshell isn't sysadmin. Note that the steps don't have to be performed in the order listed below.

1.    We need to allow usage of xp_cmdshell in general (on 2005). Use "Surface Area Configuration" or sp_configure for this.

2.    We need to have a user in the master database which has execute permission on xp_cmdshell. If you are uncertain about the difference between logins and users, you should read up about it in BOL.

3.    We need to say what Windows account should be used when a non-sysadmin user is executing xp_cmdshell.

So, here's the TSQL script that does all above:

--1, allow xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

GO

--2, grant permission to xp_cmdshell

USE master

CREATE LOGIN JohnDoe WITH PASSWORD = 'jlkw#.6('

--Note, we are in the master database!!!

CREATE USER JohnDoe FROM LOGIN JohnDoe

--Run as login x

EXECUTE AS login = 'JohnDoe'

--Below fails, no execute permission on xp_cmdshell

EXEC xp_cmdshell 'DIR C:\*.*'

REVERT

GO

--Note, we are in the master database!!!

GRANT EXECUTE ON xp_cmdshell TO JohnDoe

--Try again

EXECUTE AS login = 'JohnDoe'

--Execution of xp_cmdshell is allowed.

--But I haven't configured the proxy account...

EXEC xp_cmdshell 'DIR C:\*.*'

REVERT

GO

--3, specify the proxy account for non-syadmins

--Replace obvious parts!

EXEC sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd'

EXECUTE AS login = 'JohnDoe'

--Execution of xp_cmdshell is allowed.

--And executes successfully!!!

EXEC xp_cmdshell 'DIR C:\*.*'

REVERT

 --Cleanup

EXEC sp_xp_cmdshell_proxy_account null

DROP USER JohnDoe

DROP LOGIN JohnDoe

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

·          My recommendation is to make a user in Active Directory and then import it into SQL server with user interface to avoid the Create login command which sometimes is a bit  annoying.

Free HTML Editor for ASP.Net

Here is a sample:
http://www.asp.net/ajax/ajaxcontroltoolkit/Samples/HTMLEditor/HTMLEditor.aspx

Have a look at here to see the tutorial :
http://www.asp.net/ajax/tutorials/how-do-i-use-the-html-editor-control--cs

You will need AjaxToolkit from CodPlex to be able use it.

how to make a procedure to find and filter columns of a table

USE master

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--declare   @objname nvarchar(776) -- object name we're after

--    ,@fieldname varchar(50)

--set @objname = 'PI$Sales Line'

alter proc sp_qryTable

      @objname nvarchar(776) = null -- object name we're after

      ,@fieldname varchar(50) = null

as

declare     @dbname     sysname ,@no varchar(35), @yes varchar(35), @none varchar(35)

select @no = 'no', @yes = 'yes', @none = 'none'

-- @objname must be either sysobjects or systypes: first look in sysobjects

declare @objid int

declare @sysobj_type char(2)

set @dbname = db_name()

declare @t as table( oID int , oType varchar(100))

insert into @t

( oID  , oType )

exec( 'select  object_id, type from [' + @dbname + '].sys.all_objects where [' +  @dbname  + '].sys.all_objects.name = ''' + @objname + '''  ')

 select @objid = oID, @sysobj_type = oType from @t

-- select @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname)

declare @numtypes nvarchar(80)

select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'

declare @fieldnameNull varchar(50)

if ( @fieldname is null)

begin

      set @fieldnameNull = 'null'

      set @fieldname = ''

end

else

begin

      set @fieldnameNull = 'notnull'

      --set @fieldname = ''

end

declare @sSQL varchar(max)

set @sSQL = '

            select

                  [Column_name]                 = name,

                  [Type]                        = type_name(user_type_id),

                  [Computed]                    = case when ColumnProperty(object_id, name, ''IsComputed'') = 0 then ''' + @no + ''' else ''' + @yes + ''' end,

                  [Length]                            = convert(int, max_length),

                  [Prec]                              = case when charindex(type_name(system_type_id), ''' + @numtypes + ''' ) > 0

                                                            then convert(char(5),ColumnProperty(object_id, name, ''precision''))

                                                            else ''     '' end,

                  [Scale]                             = case when charindex(type_name(system_type_id), ''' + @numtypes + ''' ) > 0

                                                            then convert(char(5),OdbcScale(system_type_id,scale))

                                                            else ''     '' end,

                  [Nullable]                    = case when is_nullable = 0 then ''' + @no + ''' else ''' + @yes  + ''' end

                 

                  from [' + db_name() + '].sys.all_columns

                  where object_id = ' + convert(varchar(20) , @objid  ) + '

                        and (name like ''%' + @fieldname  + '%'' or '''+  @fieldnameNull + ''' = ''null'' )

     

'

exec(@sSQL)

 -- select @sSQL

3 ways to Generate random password on SQL SERVER 2005 & 2008

Best WAY :

select LOWER(left(newid(),8) ) + LOWER(right(newid(),8) )

or just

select LOWER(left(newid(),8) )

OR

select left(newid(),8)

Second Way:

CREATE PROC spGeneratePassword

AS

DECLARE @RandomNumber float

DECLARE @RandomInteger int

DECLARE @MaxValue int

DECLARE @MinValue int

DECLARE @i AS INT

DECLARE @Password AS VARCHAR(10)

SET @Password = ''

SET @MaxValue = 26

SET @MinValue = 1

SET @i = 3

WHILE(@i>0)

BEGIN

      SET @i = @i -1

      select @RandomNumber = RAND()

      set @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

      SET @Password = @Password + CHAR(96 + @RandomInteger)

END

SET @MaxValue = 9

SET @MinValue = 1

SET @i = 2

WHILE(@i>0)

BEGIN

      SET @i = @i -1

      select @RandomNumber = RAND()

      set @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

      SET @Password = @Password + CHAR(48 + @RandomInteger)

END

SET @MaxValue = 26

SET @MinValue = 1

SET @i = 3

WHILE(@i>0)

BEGIN

      SET @i = @i -1

      select @RandomNumber = RAND()

      set @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

      SET @Password = @Password + CHAR(96 + @RandomInteger)

END

select @Password AS 'Password'

Third way  ( just generate 150 password ):

INSERT INTO

dbo.attendee_users (password)

SELECT a.A + b.B + c.C + d.D AS Password

FROM

  (SELECT 'a' AS A

   UNION

   SELECT 'r'

   UNION

   SELECT 'i') a

 CROSS JOIN

  (SELECT '5' AS B

   UNION

   SELECT '2'

   UNION

   SELECT '19'

   UNION

   SELECT '4'

   UNION

   SELECT '87a3') b

 CROSS JOIN

  (SELECT 'vgh' AS C

   UNION

   SELECT '8R7y') c

 CROSS JOIN

  (SELECT '6' AS D

   UNION

   SELECT '7'

   UNION

   SELECT '8'

   UNION

   SELECT '9'

   UNION

   SELECT '1') d