Monthly Archives: September 2016

Search for text in stored procedure and views

Today I needed to find a way to alter a bunch of stored procedures and views that where using linkedserver objects. Those linked sever objects became obsolete because we consolidate a couple of instances to one instance. I used RedGate search to find the databases where the linkedserver objects where used. And with this peace of TSQL I removed the linked server objects from the stored procedures and views.

USE <database>
GO

declare @SearchString nvarchar(100) = '[linkedserver].'
declare @ReplaceString varchar(100) = ''


SELECT 
 ObjectType = 'STORED PROCEDURE'
,name
,(OBJECT_DEFINITION(OBJECT_ID)) as OriginalObject
,replace
  (replace
    (OBJECT_DEFINITION(OBJECT_ID)
    ,'CREATE PROCEDURE','ALTER PROCEDURE')
    ,@SearchString,@ReplaceString) 
    +char(10)+'GO' as ModifiedObject
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@SearchString+'%' escape '['

SELECT 
 ObjectType = 'VIEW'
,name
,(OBJECT_DEFINITION(OBJECT_ID)) as OriginalObject
,replace
  (replace
    (OBJECT_DEFINITION(OBJECT_ID)
    ,'CREATE VIEW','ALTER VIEW')
    ,@SearchString,@ReplaceString) 
    +char(10)+'GO' as ModifiedObject
FROM sys.views
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@SearchString+'%' escape '['

sp_AutoFixUsers

This stored procedure checks for orphaned users and fixces them.

use master
go
create procedure sp_AutoFixUsers
as
set nocount on
 
DECLARE @OrphanedUsers TABLE
(
  OrphandedUserId int identity(1,1) primary key,
  UserName sysname,
  UserSID  varbinary(85)
)
INSERT INTO @OrphanedUsers
EXEC sp_change_users_login 'report'
 
if not exists (select 1 from @OrphanedUsers)
begin
       print 'No orphaned users found in database '+quotename(db_name(db_id()))
end
 
DECLARE @LoginName sysname
 
DECLARE orphaned_cursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT UserName FROM @OrphanedUsers
 
OPEN orphaned_cursor  
FETCH NEXT FROM orphaned_cursor INTO @LoginName  
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
       print char(10)+ 'Orphaned user ' + quotename(@LoginName)+' found'
       EXEC sp_change_users_login @Action = 'Auto_Fix' , @UserNamePattern = @LoginName
       print char(10)
    FETCH NEXT FROM orphaned_cursor INTO @LoginName
END  
 
CLOSE orphaned_cursor  
DEALLOCATE orphaned_cursor;

Because this stored procedure is created in the master database with the prefix sp_ it's registered as system stored procedure.  With this feature on it's possible to call this stored procedure from any database on the instance.

single database call example

use <YourDatabase>; exec sp_AutoFixUsers;

Run on every database in one single statement

exec sp_MsForEachDb 'use [?]; exec sp_AutoFixUsers;'