SQL Server – How to Identify the Program That Called a Stored Procedure

Securitysql serversql server 2014stored-procedures

As part of an application database design spec, we've been asked to block execution of a set of stored procedures if they have been fired directly from SSMS / osql / sqlcmd and so on; that is, they must be permitted to run only from within the application itself.

When our team questioned if this is even possible, we were shown a demo that limited queries to specific systems (actually it let the query run, but logged to a table where it was running from). However, the procedure that did that is encrypted, so we could not find out how it was done.

How do we accomplish this?

Best Answer

Multiple ways to get this information:

SELECT APP_NAME();

SELECT PROGRAM_NAME();

SELECT [program_name] 
  FROM sys.dm_exec_sessions 
  WHERE session_id = @@SPID;

Just keep in mind that it can be spoofed in the connection string or in Management Studio's connection properties. If I connect using the following parameter, all three of the above will return foobar:

Related Solutions

Sql-server – SQL Server Replication :: Dedicated Distributor Hardware Requirements

Running on a VM is a good idea as it's hard to define resource requirements but in most cases distributors are quite lightweight. The main factors are number of subscribers, transactions count and the number of articles in replication.

Some tips to help:

Only replicate what you need to, this includes SPs, views etc.
Using pull will reduce CPU and memory requirements on the distributor.
- This is harder to implement in Web edition but possible with scripts.
- If you have a lot of push agents running, you should allow more memory outside of SQL.
- Consider increasing the desktop heap size if using push and have a lot of agents. (this is a registry change)
Choose the right type of replication.
- Snapshot is light on distributors as it doesn’t have the overhead of the log reader or clean-up jobs but heavier on the network and the subscriber when pushing out.
Enterprise edition doesn’t provide any real performance benefits on the distributor.
- I hoped that merry-go-round reads would but no, it’s mostly in CPU when problems occur.
You may find it helpful to reserve some cores for the network as large distributors can get overwhelmed during imports or clean-up jobs and struggle to get the data out.
- I’m talking 1000 subscribers and 100,000 articles (not my idea).
Throwing CPU at the distributor rarely fixes the high CPU issue. You need to treat the distribution databases like any other database and tune it (I believe Denny Cherry has an article which recommends some indexes).
CPU can also be caused by the locking and deadlocking during the clean-up job or large transactions failing to be delivered causing the subscriber agents to scan the entire table. You may find that reducing the frequency of the clean-up job actually help overall throughput but could impact latency.

In a nutshell, if replication is configured correctly, updates are batched properly (don’t update millions of rows in one transaction) and the subscribers are up to date then it’s happy.

SQL Server – Modify and Return a Result Set in a Stored Procedure

Is there a better way of achieving this, or am I on the right track?

Yes there is and no, I don't think you are on the right track. There is almost no reason to use a cursor in this situation.

If ShowLocation is a value on each record in the table then this is the kind of situation where a CASE statement comes in handy.

SQL Server code would be similar to the following

CREATE PROCEDURE dbo.GetLocationData
AS
SELECT
  <all_other_fields_here>,
  CASE 
     WHEN ShowLocation = 1 THEN Location 
     ELSE NULL 
END AS Location
FROM PERMTABLE
WHERE Location within bounds -- not entirely sure how you're doing this
ORDER BY Location;
GO

Best Answer

Related Solutions

Sql-server – SQL Server Replication :: Dedicated Distributor Hardware Requirements

SQL Server – Modify and Return a Result Set in a Stored Procedure

Related Question