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/ )

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)

Creating a XML file from SQL data using VB.net

The project involved creating a xml file in the following format with the data from the SQLserver 2008 tables.

File Sample
<claimTrackingRequest>
<Target>
<identifier>1015100236</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/29 - comment test1 </Text>
</Comment>
</Target>
<Target>
<identifier>1015700095</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/25 - comment test1</Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
</Target>
</claimTrackingRequest>

The above result was returned by the stored proc PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat
( The stored procedure details is listed at the end of the post)
I am using VB.NET to write the result returned by the stored proc to a file with extension XML
I am using a DATASET to pull the result and then using the STREAMWRITER to writer the dataset result to the file.

Sub Main()　
Dim sqlConn As New SqlConnection
sqlConn.ConnectionString = "Data Source=sqlsvr1;Initial Catalog=db01;Integrated Security=SSPI;"Dim sqlCmd As New SqlCommandsqlCmd.CommandText = "PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat"sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
sqlCmd.CommandTimeout = 120
sqlCmd.Parameters.Add("@DTBEGIN", SqlDbType.Date).Value = "07/26/2011"sqlCmd.Parameters.Add("@DTEND", SqlDbType.Date).Value = "07/29/2011"Dim ds As New DataSetTrysqlConn.Open()
Dim a As System.Data.SqlClient.SqlDataAdaptera = New SqlDataAdapter(sqlCmd)
a.Fill(ds)
sqlConn.Close()

Using writer As StreamWriter = New StreamWriter("c:\test\notes.xml", True)
writer.Flush()
For Each _row As DataRow In ds.Tables(0).Rows
writer.Write(_row.Item(0).ToString())
Nextwriter.Close()
End Using
Catch ex As ExceptionEnd Try　
End Sub

 /*The stored proc returning the XML result is as follows */

--The proc is using XML Query

USE [db01]GO/****** Object: StoredProcedure [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] Script Date: 08/03/2011 12:48:43 ******/SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON PROCEDURE [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] (@DTBEGIN SMALLDATETIME, @DTEND SMALLDATETIME)AS/*turn these off so that it wont error if it trys to divide by Zero when doing calcs*/SET
SETARITHABORT OFF ANSI_WARNINGS OFF/*Create a temp table*/CREATE TABLE #TEMPTBLCNA (
RECORD_NUM
C1 INT NULL, VARCHAR(1) NULL,COMMENT_CODE VARCHAR(100) NULL,STATUS_CODE INT NULL,STATUS_DESC VARCHAR(200) NULL,COMMENT VARCHAR(1500) NULL,ACCOUNT VARCHAR(12) NULL,HOSPITAL INT NULL,DATEENTERED DATETIME NULL,PTNO INT NULL,NOTEID INT IDENTITY(1,1) )/*GET 100's*/INSERT INTO #TEMPTBLCNA (RECORD_NUM, C1,COMMENT_CODE,STATUS_CODE,STATUS_DESC,COMMENT,ACCOUNT,HOSPITAL,DATEENTERED,PTNO)SELECTMIN(C.RECORD_NUM), LEFT(COMMENT_CODE,1),C.COMMENT_CODE,LEFT(C.COMMENT_CODE, 3),m.[STATUS],c.COMMENT,RIGHT(DBO.STRIP_NONNUMERIC_12(P.ACCOUNT), 12),p.HOSPITAL, CAST(CONVERT(VARCHAR(10),C.DATEENTERED,101) AS DATETIME),P.PTNOFROMCOMMENTS C INNER JOIN PATIENT_TABLE P ON C.PTNO = P.PTNOINNER JOIN MasterCodeList mON m.NewCode = LEFT(C.COMMENT_CODE, 3)
WHERE CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN '07/22/2011' AND '07/29/2011'
GROUP BY P.PTNO,P.ACCOUNT,C.Comment,C.COMMENT_CODE,p.HOSPITAL,C.DATEENTERED,LEFT(COMMENT_CODE,1),m.[status]ORDERBY P.PTNO--select * from #TEMPTBLCNASELECT
( c.ACCOUNT as 'identifier', 'RCV' as 'Type', SELECT '' AS 'Code',a.comment AS 'Text'
FROM #TEMPTBLCNA aWHERE a.ptno = c.ptnoFOR XML PATH(''), TYPE) AS 'Comment'
FROM #TEMPTBLCNA cgroup by c.ptno,c.ACCOUNTFOR XML PATH('Target'), ROOT('claimTrackingRequest')
DROP TABLE #TEMPTBLCNASET
SET ARITHABORT ON ANSI_WARNINGS ON

SSIS : Export Data to a text file using a package

Open up SQL SERVER Business Intelligence Development Studio
Click on File-->New-->Project-->Integrated Services Project
sometimes "Integrated Services Project" is not visible , make sure that in the Project Types - Business Intelligence Projects is selected. Here is the screen shot.

Fig 1

Once the project name is given (seen in Fig 1) , Drag and drop Data Flow Task from the toolbox on the  Control Flow.( seen in Fig 2)

In the case of toolbox is not visible, go to View-->toolbox

Fig 2

Right click on the Connection Manager as shown below in Fig 3 and select New OLEDB Connection" This will lead you to a screen as shown in Fig 4.

Fig 3

This is where you will fill up all the necessary database connection as shown here in Fig 4 and click the Test Connection to make sure that you are able to connect to the database server as selected .

Fig 4

Now whene you hit OK in the Databse Connection manager you will see OLE DB Source object with green and red arrow in the Control Flow as seen in the Fig 5.

Fig 5

Now double click on the OLE DB Source Object on the Control Flow and the OLE DB Source Editor whill open up as shown below in Fig 6

Highlight the Conenction Manager on the left as you will be filling up the information to exract data from the database now.
In the OLE DB connection Manager dropdown select the Conenction Manager that was created in Step 4.
Select SQL Command in the daat Access mode. We use this mode in this example as we are using a query to get the data from the table.
In the sql Command text box write the query that you are goign to use to extract the data from the database and export out to the flat file.

Fig 6

Now, highlight the columns in the left that will show all the colums that your query will pull and the columns to be written out in the flat file. You can check one of the columns off if you don't want the column to be exported to the file. Leave the error output section as it is . WE are not doing anything with this section in this example.

Fig 7

Now from the tool box, navigate to the Data Flow Destination section and drag and drop the Flat File Destination Item in the Data Flow and expand the green arrow to connect to the Flat File Destination item as shown below picture.

Fig 8

Right click again in the Conenction Mager section and select the New Flat file Connection and thsi will pull up a Flat File Destination editor as show below.

Fig 9

Fig 10

Top 10 SQL Server 2008 Features for the Database Administrator (DBA)

We migrated to the new version Sql server 2008 R2 early this year but I am afraid that we have not been able to utilize all of the "awesome" features that sql 2008 comes with, though we have used SSIS, DBmail etc but that is in development aspect. Here is a list (yes of course the top 10). More blog posts to follow on the research and hands on experience on each of the follwing features.

1.Activity Monitor
2.[Sql Server Audit]
3.BackUp Compression - ( I love this feature - considering how many databases we backup daily and monthly)
4.Central Managemant Servers
5.Data Collector and Management Data Warehouse
6.Data Compression
7.Policy-Based Management ( as the team grows this will be helpful - to implement standards on that naming conventions.)
8.Predictable Performance and Concurrency
9.Resource Governor
10.Transparent Data Encryption (TDE)

Transfer SQL Server Jobs from SQL Server 2000 to 2005

Transfer SQL Server Jobs from SQL Server 2000 to 2005 / 2008
August 15, 2007 – 6:13 pm To transfer sql server jobs from sql server 2000 to sql server 2005 you can use SQL Server Business Intelligence Development Studio.

1. Click file -> new -> project to create a new Integration Services Project
2. Click view -> toolbox so you can see an overview of all the items
3. From the toolbox, drag the “transfer jobs task” to the control flow window
4. Right click the transfer job task and click edit
5. Select jobs and enter the source and destination server, you can test the connection immediately
6. Choose to transfer all jobs, or only the ones you can select from the given list
7. Set the desired IfObjectExists and EnableJobsAtDestination option and click OK
8. Right-click the transfer jobs task and click execute task

If the job fails it will turn red, if it succeeds it turns green. Click on execution results to check for errors.


A couple of reasons why the task would fail:

•the job owner does not exist on the destination server, you can modiy this on the source server in the job properties
•if you have e-mail notifications enabled on the job, be sure the operator exists on the destination server or disable this
•if the database for which the job will be executed does not exist that job transfer will fail
A couple screenshots from the transfer jobs task properties and the execution results:

taken from http://www.gregory.eu/?p=19