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

Schedule a SQL profiler to run at a specific time

I needed to reserach some external backups on the database that ran in the middle of the night. To find out the source and the status. I decided to set up a trace in sql profiler that consisted teh processID, Loginame, NTusername etc .

Steps on SQL Server 2008 R2
---

• Connect to SSMS
• Go to Tools
• Go to SQL Profiler
• Chose the appropriate trace template according to requirements ( or create your own)
• Check enable trace stop time and specify the date and time that you want it to stop
• Click on the Run

If you run the follwing query you will see our trace

select * from ::fn_trace_getinfo(0)
Immediately stop the trace

Now we want to export the create trace script and add this script as  one of the step in the schedule job

Click on File----Select Export--Select Script Trace Definition

Select the entire script and add it as a step in the schedule job.

In the script - Search for key word in the script 'InsertFileNameHere' and replace with where to store the trace file.

exec @rc = sp_trace_create @TraceID output, 0, N'E:\profilertrace\trace1.trc', @maxfilesize, @Datetime

 

Save the Job.

That's all !!! Schedule the job to run at the specific time that you would like it to run.

Giving execute permission to all stored procedures through a databaserole

I had a situation where I had to give a user( AD group) execution permission to all the sps.

- Created a role called db_executor data user role

USE pubs
GO

exec sp_addrole  'db_executor'

GO

--added memebers to the role

USE pubs
GO

exec sp_addrolemember 'db_executor', 'GRP_ProdSupport'

GO

--gave permission to execute the stored procedures to the role.

USE pubs
GO

DECLARE @SQL nvarchar(4000),
  @Owner sysname,
  @StoredProcedure sysname,
  @Return int

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    -- Create the SQL Statement. Since we're giving
    -- access to all stored procedures, we have to
    -- use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT EXECUTE ON [' + @Owner
          + '].[' + @StoredProcedure
          + '] TO db_executor'

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @StoredProcedure
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
        -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END
GO

(code source : http://www.sqlservercentral.com/articles/Security/sqlserversecuritythedb_executorrole/988/ )