Related to: xp_deletemail
One of the geekier things you can do with SQL Server is to send it an e-mail in case you think it is lonely or down. Actually, you can send it a single query in the text of an e-mail and SQL Server will send the results of the query to the original sender and anyone on the cc: list as an attached file. E-mails sent to the server are processed when you run this stored procedure, so you'll probably want to run it at regular intervals to clear out the inbox, perhaps as part of a SQL Agent job.
A typical call to sp_processmail
looks like the statement below. This invocation will process messages with a subject line of "SQL:pubs," return the results in a text file attachment with extension TXT
, use a comma to separate the data fields, and run the queries against the pubs
sp_processmail @subject = 'SQL:pubs',
@filetype = 'TXT', @separator = ',',
@dbuse = 'pubs'
Using e-mail with a SQL Server is a fairly complicated process, normally requiring that Outlook be installed on the serverick!and lots of configuration to get it all working. But this can be a handy technique to execute queries against a database, particularly when you are constrained by a network configuration that prevents other ways of connecting to a database.
Despite its inherent risks, the xp_cmdshell
extended system stored procedure is useful for a variety of purposes. You can use it to run any Windows command line from within a stored procedure. But be wary: leaving this stored procedure active on your server can give an attacker a dangerous tool. Keep it available only if you absolutely must use it!
Here are some examples of its usage. It is as simple as passing the command line string as the argument and optionally passing no_output
if you don't want SQL Server to pay any attention to any information returned by the command.
exec master..xp_cmdshell 'dir e:\*.*'
exec master..xp_cmdshell 'format j:', no_output
exec master..xp_cmdshell 'format j:'
The command line executes with the security context of the SQL Server service account. So if you have the account set to Local System or any other high-privileged account, the command executes with that account's privileges.
Secure databases are critical to today's distributed applications, and SQL Server provides plenty of tools you can use to lock down the server. But making a server or database secure is a complex process, and it is far too easy to configure conflicting security settings. sp_helprotect
is handy for getting information about how objects are configured for permissions in the current database.
The procedure has several options, depending on the kind of information you need. At its simplest, you can call it without any parameters and get a complete list of permissions for every object in the database. This is great for a quick survey of how things are set, but can return an overwhelming amount of information that can obscure important problems.
You can also pass in an object name and get back the permissions for just that object. In a default, unmodified version of Northwind, the following statement will show the information in Figure 1
, revealing that the public role has unfettered access to the data in the Categories
table. All of the columns are self-explanatory except perhaps the Column
column. It contains 'all' if the setting applies to all current columns of the object, 'new' if it applies to new columns, or a combination of the two.
|Figure 1: Getting Permissions: The figure shows the results after running sp_helprotect against Northwind's Categories table.|
exec sp_helprotect 'Categories'
You can use the @permissionarea
parameter to specify whether the results should include object permissions ('o
'), statement permissions ('s
'), or both.
exec sp_helprotect @permissionarea='o s'
You can also filter the results for either a specific grantor or grantee user using either the @grantorname
exec sp_helprotect @username='public'
Related to: sp_releaseapplock
Transactions and object locks are a fact of life with relational databases. SQL Server has rich support for both along with many features that serve to relieve the severe concurrency problems that a heavily-used database can suffer. Most of the time you'll be in good shape using the built-in features and you don't have to worry about going deeper. But when you need to go deeper, take a look at sp_getapplock
BOL says that this stored procedure places a lock on an application resource. That simple description belies a lot of power and complexity for a feature that I can't begin to do justice to in a short part of an article like this. But the idea is that you can create what amounts to a custom, shared or exclusive semaphore to synchronize different instances of stored procedures. Basically, if you have a weird concurrency issue you have to code around that isn't related to or solvable by built-in object locks in SQL Server, an application lock might solve the problem.
shows the basic idea. When creating shared locks, both instances of Query Analyzer can lock mySemaphore
and do normal processing. But once a process requests and gets an exclusive lock on the semaphore, it signals to other processes that they can't do some operation until they are able to get their own lock. It's all quite similar to typical threading issues using a custom object in T-SQL.
This is another system stored procedure you won't need to use often, but when you do, it is pretty slick.