script: List the permissions of a user

I had a  task where I had to list all the permission of a specific user on the database . The following script came to the rescue


SELECT dppriper USER_NAME(dppriper.grantee_principal_id) AS [UserName], .type_desc AS principal_type_desc, .class_desc, OBJECT_NAME (dppriper.major_id) AS object_name, .permission_name, .state_desc AS permission_state_desc
FROM sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
WHERE
--dppriper.permission_name = 'EXECUTE'
USER_NAME(dppriper.grantee_principal_id) = 'myuser'

 --**replace the myuser with the user we are looking for
--**If we want to find only the stored procedures that the user has perission on comment out the  clause in the above query.

The size of the message body in dbmail

SQL 2008 can take more than 8000 characters in the meassage body of dbmail . While declaring the message body variable just set the size of the varchar to max.
For .e.g.

declare @msg_body varchar(max),           
 @v_subject


This will resolve the issue of the contents getting truncated in the message body of the emailvarchar(100)

Check the backup process

Script to see how the backup is progressing.

SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins , (total_elapsed_time/1000)/60 Total_Elapsed_Time_Mins ,DB_NAME(Database_id) DBName ,* FROM
sys.dm_exec_requests WHERE session_id = (the spid for running the back up - you can find by running sp_who2 active)

MSDTC error while using Transactionscope

In one of my project while writing a client application in VB.NET I had to make a call to two different databases on different servers and in case of error encountered both the transaction had to roll back. I used transactionscope to achieve this. I could use this since the database server is SQL 2008. Transactionscope ( a very cool thing) can be used on in SQL 2005 or higher. However I was running into the following error whenever while trying to open connection2 ( second conection ).

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.



Here is a snippet of my code (in VB.NET)


While debugging through the code, it thre error as soon as it was trying to open the oConn2 ( server2)
I checked in the database server if DTC was enabled. It was. What I had missed was the MSDTC had to be configured in the client server as well. Once the MSDTC was configured on the client server Viola it worked like a charm.



Imports System.Transactions  'you will have to add reference to System.Transactions first

Using scope As New TransactionScope()
Using oconn1 As New SqlConnection(strconn1)


    ocmd1 = New SqlClient.SqlCommand()
    ProgressBar1.PerformStep()
    ocmd1.Connection = oconn1
    ocmd1.CommandText = "PROC_SKIPTRACE_RESULTS_INSERT"
    ocmd1.CommandType = CommandType.StoredProcedure
    ocmd1.CommandTimeout = 600
    Dim paramPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
    paramPTNO.Direction = ParameterDirection.Input
    ocmd1.Parameters.Add(paramPTNO)
   Try
   If oconn1.State = ConnectionState.Closed Then


       oconn1.Open()
   End If
   returnValue = ocmd1.ExecuteNonQuery()
  Catch ex As Exception
    MsgBox(ex.Message.ToString())
  Finally
    oconn1.Close()
  End Try


Using oConn2 As New SqlConnection(strconn2)
 ocmd2 = New SqlClient.SqlCommand()
 ocmd2.Connection = oConn2
 ocmd2.CommandText = "PROC_SKIPTRACE_PATIENT_TABLE_SUSPENSE_UPDATE"
 ocmd2.CommandType = CommandType.StoredProcedure
 ocmd2.CommandTimeout = 600
 Dim paramintPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
 paramintPTNO.Direction = ParameterDirection.Input
 ocmd2.Parameters.Add(paramintPTNO)


Try
 If oConn2.State = ConnectionState.Closed Then
 oConn2.Open()
 End If
 returnValue = ocmd2.ExecuteNonQuery()
Catch ex As Exception
 MsgBox(ex.Message.ToString())
Finally
 oConn2.Close()
End Try


End Using 'oConn2
End Using 'oConn1
scope.Complete()
End Using ' transactionScope

Dropping multiple tables

/*The table names are logged in the table SQLAdmin_Monitor_tbl_size_info
Using cursor to loop thorough the table and dropping them.*/



DECLARE @TableName VARCHAR(100)
DECLARE @dropCommand VARCHAR(255)

DECLARE
FOR

OPEN tableCursor
FETCH next FROM tableCursor INTO @TableName

WHILE (@@Fetch_Status >= 0)
 BEGIN
  SET @dropCommand = N'DROP TABLE ' +@TableName
  EXECUTE(@dropCommand)
  FETCH next FROM tableCursor INTO @TableName
End
CLOSE  tableCursor
DEALLOCATE  tableCursor

Update stats

I had a project where I had to find when the stats were updated last and then had to update the stats of the table which was used heavily. I found the sript on the following blog

http://troubleshootingsql.com/2010/01/22/how-to-check-database-statistics-last-updated-date-time-in-sql-server/


SELECT schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
( select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2 ) as rowcnt,
 convert(DECIMAL(18,8),
convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
FROM sysindexes i
INNER JOIN sysobjects tbls
ON i.id = tbls.id
INNER JOIN sysusers schemas
ON tbls.uid = schemas.uid
INNER JOIN information_schema.tables tl
ON tbls.name = tl.table_name
AND schemas.name = tl.table_schema
AND tl.table_type='BASE TABLE'
WHERE 0 < i.indid
AND  i.indid < 255 and table_schema <> 'sys'
 AND  i.rowmodctr <> 0 and i.status not in (8388704,8388672)
AND  (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0
ORDER BY ModifiedPercent
DESC   

Error: [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.". [SQLSTATE 01000] (Error 7412). The step failed.

I have a stored procedure in server2 that I was trying to call from server1 using linkedserver so that it gets executed in server2.
Server 1

• Windows Server 2008(x86)
• SQL Server 2008 (x86) Standard Edition SP1

Server 2

• Windows Server 2008 (x86)
• SQL Server 2008 (x86) Standard Edition SP1

Following  is the snippet of my code on Server1

SET  XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION 
declare @RetVal int,

exec @RetVal = linksv1.db1.dbo.usp_update_Referrals 0

COMMIT TRANSACTION

So far I have taken the following steps

1. NETWORK CONNECTIVITY

• Ping server1 from server2  -- success
• Ping server2 from server1  -- success

2. DTC CONFIGURATION

• Open Start->Administrative Tools->Component Services
•  Expand the Component Services Node untill you find Local DTC right click and select properties option
•  In the Local DTC properties click in the security section and set the configuration as shown in the image

   Note that every time you make a change in the DTC Security configuration, the DTC service is restarted automatically

The final step is make shure the DTC service is started and configured to start when windows start.

3. DTC SERVICE IS STARTED

• Open Start - > Open Start->Administrative Tools->Services
• Find the Distributed Transactions Coordinator Service
• Make sure the status is “Started” and the Startup Type is “Automatic”.

Follow the same step in Server2

After the DTC is configured , checked the lnked servers
Linked Server Configuration on Server 1

 OKC is the linked server pointing to my Server2
Highlight the linked Server
Right Click and go to properties
Select the Server Option in the properties window and make sure the following settings are applied
Data Access: True
RPC: True
Enable Promotion of Distributed Transactions: True

All the settings were double checked ...will update in few weeks if we still get the "No transaction is active" error...