Determining if a Folder Exists Using xp_cmdshell

The code below creates a stored proc that will report if a passed-in Windows folder exists or not. While it avoids using the undocumented xp_fileexist system proc, it uses xp_cmdshell, which in many places is the first thing that gets disabled because of the obvious security ramifications. Still, it's a nice hack if you're working in the file system a lot.

IF OBJECT_ID('dbo.usp_FolderExist') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.usp_FolderExist
END
 
GO
 
CREATE PROCEDURE dbo.usp_FolderExist (@FullPathName NVARCHAR(255),
                                      @Exists       TINYINT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON
   
    -- Remove any trailing slash.
    DECLARE @TrailingSlash NVARCHAR(255)
    SET @TrailingSlash = REVERSE(@FullPathName)
    
    IF '\' = LEFT(@TrailingSlash, 1) 
        SET @TrailingSlash = SUBSTRING(@TrailingSlash, 2, LEN(@FullPathName) + 1)
  
    SET @FullPathName = REVERSE(@TrailingSlash)
   
    -- Build and exec the command.
    DECLARE @Cmd NVARCHAR(4000)
    SET @Cmd = 'dir ' + @FullPathName
    SET @Exists = 0
    CREATE TABLE #dir (output NVARCHAR(4000))
 
    INSERT INTO #dir
        EXEC master..xp_cmdshell @Cmd
 
    IF EXISTS (SELECT * FROM #dir WHERE output LIKE '%' + @FullPathName + '%')
        SET @Exists = 1
      
    -- Done.
    DROP TABLE #dir
END
 

/* Test Code
 
set nocount on
declare @bExists int   set @bExists = 0
exec usp_FolderExist 'C:\Temp',  @bExists OUTPUT
print @bExists
exec usp_FolderExist 'C:\Temp\', @bExists OUTPUT
print @bExists
 
*/
 

Displaying Database and Server Principals

A quick example of using the system views to look at the server principals and the database principals. First, let's look at the database principals:

SELECT  dbp.class_desc                       AS GrantedObjectType,
        CASE dbp.class_desc
            WHEN 'DATABASE'         THEN DB_NAME    (dbp.major_id)
            WHEN 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(dbp.major_id)
            WHEN 'SCHEMA'           THEN SCHEMA_NAME(dbp.major_id)
        END                                  AS GrantedObjectName,
        COL_NAME(dbp.major_id, dbp.minor_id) AS GrantedColumnName,
        dbp.state_desc                       AS State,
        dbp.permission_name                  AS Permission,
        dpGrantor.type_desc                  AS GrantorType,
        dpGrantor.name                       AS Grantor,
        dpGrantee.type_desc                  AS GranteeType,
        dpGrantee.name                       AS Grantee,
        dpRole.name                          AS GranteeRole
   FROM sys.database_permissions                                     dbp
   JOIN sys.database_principals                                      dpGrantor
     ON dbp.grantor_principal_id = dpGrantor.principal_id
   JOIN sys.database_principals                                      dpGrantee
     ON dbp.grantee_principal_id = dpGrantee.principal_id
   JOIN sys.database_role_members                                    droGrantee
     ON dbp.grantee_principal_id = droGrantee.member_principal_id
   JOIN sys.database_principals                                      dpRole
     ON droGrantee.role_principal_id = dpRole.principal_id
   JOIN sys.objects                                                  o
     ON dbp.major_id = o.object_id
  ORDER BY GrantedObjectType,
        GrantedObjectName,
        Permission,
        State

The server principals are done in a similar way:

        
SELECT  svp.class_desc            AS GrantedObjectType,
        SCHEMA_NAME(svp.major_id) AS GrantedObjectName,
        svp.state_desc            AS State,
        svp.permission_name       AS Permission,
        spGrantor.type_desc       AS GrantorType,
        spGrantor.name            AS Grantor,
        spGrantee.type_desc       AS GranteeType,
        spGrantee.name            AS Grantee,
        spRole.name               AS GranteeRole
   FROM sys.server_permissions                                     svp
   JOIN sys.server_principals                                      spGrantor
     ON svp.grantor_principal_id = spGrantor.principal_id
   LEFT JOIN sys.server_principals                                 spGrantee
     ON svp.grantee_principal_id = spGrantee.principal_id
   LEFT JOIN sys.server_role_members                               sroGrantee
     ON svp.grantee_principal_id = sroGrantee.member_principal_id
   LEFT JOIN sys.server_principals                                 spRole
     ON sroGrantee.role_principal_id = spRole.principal_id
   LEFT JOIN sys.objects                                           o
     ON svp.major_id = o.object_id
  ORDER BY GrantedObjectType,
           GrantedObjectName,
           Permission,
           State