We have another opportunity to write as a part of TSQL Tuesday today.  This month Matt Velic (Blog | Twitter).  Matt has proposed a challenge that was derived from a comment on twitter.  The challenge this month is to write about “Apply.”

Admins Apply Within

As an administrator of databases, do you use Apply?  Not only can Apply be used to help return result sets as a part of your application, it can come in quite handy from an administration point of view too.  It can be used to help retrieve the text of currently executing code or code stored in cache.  Apply can be used to help retrieve index information and it can come in quite handy when performing a security audit.

Some Back Story

Just a few days ago, I posted some scripts to help find role membership of logins at the server level.  I wasn’t satisfied with the query and decided to rewrite it as a part of my entry for this months blog party.  You can read up on that script here.  My dissatisfaction with the query was that it felt klugy.  I wanted to write the query with something other than the Union and to present fewer rows to user.  So, I took another look at the query and purpose for the query and decided to use a combination of Apply and Pivot.

My new take on the script is simple, I will create a result set that will show a 1 for each server role for which a login is a member.  Each login will only have 1 row, opposed to the row per server role membership from the prior query.

The Script

Code block

SELECT PrincipalName,CA.[PUBLIC],sysadmin,bulkadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator
FROM    (
SELECT  SUSER_NAME(SR.role_principal_id) AS ServerRole, SP.name AS PrincipalName
FROM sys.server_role_members SR
INNER Join sys.server_principals SP
ON SR.member_principal_id = SP.principal_id
) Roles
PIVOT   (
COUNT(serverrole)
FOR serverrole IN (sysadmin,bulkadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator)
) PVT
Cross Apply (SELECT 1 AS [PUBLIC]) CA

In this example, the bulk of the heavy lifting is done through the pivot.  You will see that the Apply only seems to play a minor role in this script.  It is an important role nonetheless.  All logins (unless you have altered the public role) are also a member of the public role.  That membership is not displayed through the views I have used in this query.  Due to that handling of the public role, and to ensure that the membership in that role is not forgotten, it must be accounted for in the query.

This query will add a new column for the public role to those columns already presented via the pivot.  In this column we will show that every login returned via the query is a member of the public role.  But how is that done?  Using the apply, in really basic terms, acts like a join to this subquery that I use for the public column.  I am taking the value of 1 for column public, and applying that value to all of the results from the Pivot portion of the query.  I am using the Cross version and there is one more version – called Outer.  In my testing, both the Outer and the Cross Apply will return the same results for this particular query.

Some common uses for Apply are in conjunction with TVFs.  Should you use the Apply with a function where the “Join” criteria would be more tightly defined, then the use of Cross V. Outer will produce different results.  Just as with an Outer Join, the Outer would return results for all records in the “outer” or “left” part of the join whether there was a matching record in the TVF or not.  In the same sort of setup, the Cross Apply would only return records that had a match in the TVF.

Conclusion

Again, this is an oversimplification of the Apply statement.  My main goal was to present a use for Apply.  Apply can be a useful tool in the hands of the database administrator.  Examine it and learn what other potential it may have for some of your administration needs.

This is just one piece of the puzzle when performing a security audit.  Getting this information quickly when requested for an audit can be very helpful.  The output format is quite simple and very conducive for many auditors and management.  A simple output report for the Server Roles in your environment is merely one example of the usefulness of Apply.