Recovery database from suspect mode

The database was in suspect mode, inaccessible Ran the following query Then restored from the latest full back up

EXEC sp_resetstatus client_367
ALTER DATABASE client_367 SET EMERGENCY
use client_367
DBCC checkdb
ALTER DATABASE client_367 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('client_367', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE client_367 SET MULTI_USER

Permission: To list the permissions on a schema

--the schema name is testschema
select pr.permission_name as permissionName,s.[name] as schemaname,s.[schema_id], p.[name] as principal_name, p.principal_id as pricipalID
from sys.schemas s
inner join sys.database_permissions pr
on s.schema_id = pr.major_Id
inner join sys.database_principals p
on pr.grantee_principal_id = p.principal_id
where s.[name] = 'testschema'

Trace status

Following displays the current status of the trace

DBCC TRACESTATUS (1222, -1);
GO


DBCC TRACEON(1222,-1)
GO

--Turns ON the trace 1222 globally (not just for that session)


DBCC TRACEON(1222,-1)
GO

--turns OFF the trace 1222 globally (not just for that session)

Schema: change schema of a user

The schema of a user was associated to a different schema instead of the default schema dbo. to check the user who do not have the default schema as dbo
select 'ALTER USER [' + name + '] with default_schema = [dbo]'
from sys.database_principals
where type in ('U')
and default_schema_name not in ('dbo')
--the above lists and generates a alter script
ALTER USER [Dom1\user1] with default_schema = [dbo]


--change the schema of the stored procs
SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name
FROM sys.Procedures p
INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name = 'CHANGE_ME_Username'

Fix : truncation of dynamic query result

declare @script nvarchar(max) = ''
SELECT @script += 'grant execute on [' + r.ROUTINE_NAME + '] to [GRP_eApps_allUsers]' + char(13) + char(10)
from INFORMATION_SCHEMA.ROUTINES as r
where specific_name like 'proc_eapps%'
order by specific_name
--print @script
DECLARE @stmp varchar(max)
SET @stmp = ISNULL(@script,'')
WHILE @stmp <> ''
BEGIN
PRINT LEFT(@stmp, 8000)
SET @stmp = substring(@stmp, 8001, len(@stmp))
END

Drop the user defined objects

--remove all user defined tables exec sp_MSforeachtable @command1 = 'Drop table ?';
--remove all the user defined stored procedures from the database
declare @procname nvarchar(max)
declare curProc Cursor for SELECT [name] from sys.objects where type = 'P'
Open curProc Fetch next from curProc into @procName
While @@fetch_Status = 0
Begin
Exec('drop procedure ' + @procName)
Fetch Next From curProc Into @procName
End
Close curProc
Deallocate curProc --drop all user defined views

Declare @viewName varchar(500)
Declare curView Cursor For Select [name] From sys.objects where type = 'v'
Open curView Fetch Next From curView Into @viewName
While @@fetch_status = 0
Begin
Exec('drop view [' + @viewName+']')
Fetch Next From curView Into @viewName
End
Close curView
Deallocate curView

--Remove all Triggers
-- drop all user defined triggers
Declare @trgName varchar(500)
Declare curTrig Cursor For Select [name] From sys.objects where type = 'tr'
Open curTrig Fetch Next From curTrig Into @trgName
While @@fetch_status = 0 Begin Exec('drop trigger ' + @trgName)
Fetch Next From curTrig Into @trgName
End
Close curTrig
Deallocate curTrig --Remove all functions -- drop all user defined scalar-valued functions
Declare @FncName varchar(500)
Declare curFunc Cursor For Select [name] From sys.objects where type = 'FN'
Open curFunc
Fetch Next From curFunc Into @FncName
While @@fetch_status = 0
Begin
Exec('drop function ' + @FncName)
Fetch Next From curFunc Into @FncName
End Close curFunc
Deallocate curFunc

-- drop all user defined table-valued functions
Declare @FncName1 varchar(500)
Declare curFunc Cursor For Select [name] From sys.objects where type = 'TF'

Open curFunc
Fetch Next From curFunc Into @FncName1
While @@fetch_status = 0
Begin
Exec('drop function ' + @FncName1)
Fetch Next From curFunc Into @FncName1
End
Close curFunc
Deallocate curFunc

Fix : Could not obtain exclusive lock on database 'model'

SELECT spid, loginame, DB_NAME(dbid) FROM master..sysprocesses WHERE DB_NAME(dbid)='Model' If you get the following errors - MS-SQL Database Error --------------------------- Create failed for Database 'DbName1'. An exception occurred while executing a Transact-SQL statement or batch.Could not obtain exclusive lock on database 'model'. Retry the operation later.CREATE DATABASE failed. Some file names listed could not be created. Check related errors. kill the connections