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

Check a list of objects and permissions

--list views, procs, table

select o.* from sys.database_principals p inner join sys.objects o on p.principal_id = p.principal_id where p.name = 'GRP_App_Users' and o.type in ('U','V','P') and o.is_ms_shipped = 0


--select permissions of a group or user on a database

select p1.*,o.* from sys.database_permissions p1 inner join sys.database_principals p2 on p1.grantee_principal_id = p2.principal_id inner join sys.objects o on o.object_id = p1.major_id where p2.name = 'GRP_SQLReportingUsers'


--FIND SPS WITH EXECUTE PERMISSIONS ON A USER

SELECT [name] FROM sys.objects obj INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id WHERE obj.[type] = 'P' -- stored procedure AND dp.permission_name = 'EXECUTE' AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT AND dp.grantee_principal_id = (SELECT principal_id FROM sys.database_principals WHERE [name] = 'GRP_USERS_ABC') ORDER BY NAME

Tempdb space lookup queries

Troubleshooting Insufficient Disk Space in tempdbhttp://msdn.microsoft.com/en-us/library/ms176029.aspx

Capacity Planning for tempdb
http://msdn.microsoft.com/en-us/library/ms345368.aspx

Optimizing tempdb Performance
http://msdn.microsoft.com/en-us/library/ms175527.aspx


SELECT
SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]FROM sys.dm_db_file_space_usage;
--current free and used spaceSELECT SUM(size)*1.0/128 AS [size in MB]FROM tempdb.sys.database_files
--Determining the Amount of Space Used by User Objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],(
SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]FROM sys.dm_db_file_space_usage;
--Determining the Amount of Space Used by Internal ObjectsSELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],(
SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]FROM sys.dm_db_file_space_usage;
--Determining the Longest Running TransactionSELECT transaction_idFROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;--Determining the Amount Space Used by the Version StoreSELECT SUM(version_store_reserved_page_count) AS [version store pages used],(
SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]FROM sys.dm_db_file_space_usage;

Executing stored procedure with input parameters using sp_executesql

Stored procedure name proc1
Parameters are @param1, @param2, @param3
Values are 1,2,3

declare @SqlString nvarchar(2000)
declare @ParamDef nvarchar(2000)
set @SqlString = N'exec proc1 @param1, @param2, @param3'
set @ParamDef = N'@param1 bit, @param2 bit, @param3 bit'

EXECUTE sp_executesql @SqlString ,@ParamDef, @param1 = 0, @param2 = 1, @param3 = 1