Generate XML data

--XML data sample to be genereated

<?xml version="1.0"?>
-<serialization SerializationRoot="SBOInboundBatchCO">

-<SBOInboundBatchCO ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchCO">

-<sboInboundBatchIO ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchIO">
<creationDate Type="DATE">2017/05/02</creationDate>
<externalReferenceDate Type="DATE">2017/05/02</externalReferenceDate>

-<sboInboundMessageIOs ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO" IsIndexed="1">

-<element ClassName="com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO">
<billingPeriodsequenceNumber>2</billingPeriodsequenceNumber>
<messageCodeBehaviourCode Type="STRING">CACISBOACKGMENT</messageCodeBehaviourCode>
<messageText Type="STRING"/>
<receivableGrpId Type="STRING">615466</receivableGrpId>
<responsiblePartyIdentifier Type="STRING">Y</responsiblePartyIdentifier>
</element>

</sboInboundMessageIOs>
<sboName Type="STRING">CACi BD Collections</sboName>
<totalMessageCount Type="NUMERIC">284</totalMessageCount>
</sboInboundBatchIO>
</SBOInboundBatchCO>
</serialization>

Used the following SQL script to get this output

declare @intTotMsgCount int , @xmlstr xml



　
 set @inttotMsgCount = (Select count(Account_number) from Imp_staging_referrals_claims_FollowUp where imp_fileId = @intfileId

and uploaded = 1 and deleted = 0)



　
 set @xmlstr=



　
 (select 'SBOInboundBatchCO' as '@SerializationROOT',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchCO' as '@ClassName',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundBatchIO' as '@ClassName',

'DATE' as 'creationDate/@Type',

convert(varchar(10),cast(getdate() as date) ,111) as 'creationDate',

'DATE' as 'externalReferenceDate/@Type',

convert(varchar(10),cast(getdate() as date) ,111) as 'externalReferenceDate',




(
  select 'com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO' as '@ClassName', '1' as '@IsIndexed',




(
  select ' com.siemens.med.hs.sf.thirdpartycollection.inbound.SBOInboundMessageIO' as '@ClassName',

'1' as 'billingPeriodsequenceNumber',

'STRING' as 'messageCodeBehaviourCode/@Type',

'CACISBOACKGMENT' as 'messageCodeBehaviourCode',

'STRING' as 'messageText/@Type',

'' as 'messageText',

'STRING' as 'receivableGrpId/@Type',

substring(Account_Number,2,len(Account_Number)-4) as 'receivableGrpId',

'STRING' as 'responsiblePartyIdentifier/@Type',

'Y' as 'responsiblePartyIdentifier'

from Imp_staging_referrals_claims_FollowUp

where imp_fileId = @intFileId

and Facility_Code = @intClientCode



　
 for xml path ('element') , elements,type




)
  for xml path ('sboInboundMessageIOs') , type

)

,'STRING' as 'sboName/@Type',

'CACi BD Collections' as 'sboName',

'NUMERIC' AS 'totalMessageCount/@type',

@intTotMsgCount as 'totalMessageCount'

for xml path('sboInboundBatchIO'), elements,type




)
  
for xml path('SBOInboundBatchCO'), type




)
  for xml path ('serialization') , type




)
  --AS COL_XML
  
select '<?xml version="1.0"?>' + cast(@xmlstr as varchar(max)) as COL_XML

db_executor role for user permission to execute all procedures

SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately this is easily resolved by creating a new role.

The following SQL creates the new role in a database, and then grants it execute rights :

-- Create a db_executor role
CREATE ROLE db_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor

 

--then add a member to this role

 

Stored procedure with an OUTPUT parameter

CREATE procName1 (@intClientId int, @account varchar(50), @intCount int OUTPUT)
AS

SELECT @intCount = COUNT (*) FROM Clients where clientId = @intClientId

SELECT @intCount

GO


--procedure call
Declare @out int
EXECUTE procName1 999, 'ABCSDRF',@out OUTPUT
SELECT @out

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

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