Category Archives: Stored Procedures

Random Security Trap 002

For post number two in my “Random Security Trap” series, let’s take a look at an issue that may cost you a few minutes of head-scratching if you haven’t seen it before. First, the business setup: you need to enable a group of users to have read-only access to a database, but also have controlled insert permissions to one table. A common way to solve this is to create a database user, add it to the database db_datareader role, create a stored procedure to manage the insert operation, and then grant execute permission on the stored procedure to the database user. And, in most cases, that solution works just fine.

However… what if the insert operation is a little more complicated and uses dynamic SQL to perform the insert? Well, in that case, you may fall right into the trap. Let’s demonstrate.

We’ll begin by creating a SQL login to the server and then using that login for a database user with read-only permission.

-- set context to the master database
USE [master];
GO

-- if the ReadOnlyLogin already exists, drop it
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ReadOnlyLogin')
    DROP LOGIN ReadOnlyLogin;

-- create a ReadOnlyLogin login
CREATE LOGIN ReadOnlyLogin
WITH
    PASSWORD = N'ReadOnly'
    , DEFAULT_DATABASE = devDB
    , CHECK_EXPIRATION = OFF
    , CHECK_POLICY = OFF
    ;

-- set context to the working database
USE devDB;
GO

-- if the ReadOnlyUser database user already exists, drop it
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ReadOnlyUser')
    DROP USER ReadOnlyUser;

-- create a ReadOnlyUser database user for the ReadOnlyLogin login
CREATE USER ReadOnlyUser
FOR LOGIN ReadOnlyLogin
WITH DEFAULT_SCHEMA = dbo;

-- add the database user to the datareader role
EXECUTE sp_addrolemember
    @rolename = N'db_datareader'
    , @membername = N'ReadOnlyUser'
    ;

Okay. Now that we have our login and database user created, let’s create a table so we can demonstrate controlling insert operations. Since we’ll end up simulating logging in as the ReadOnlyLogin, we’ll use the table to capture information about our testing operations.

-- if the sample table exists, drop it
IF OBJECT_ID(N'tbl', N'U') IS NOT NULL DROP TABLE tbl;

-- create the sample table
CREATE TABLE tbl (
    ID INT IDENTITY(1,1) NOT NULL
    , DateStamp DATETIME2(3) CONSTRAINT DF_tbl_DateStamp DEFAULT(GETDATE())
    , [Login] SYSNAME
    , ContextLogin SYSNAME
    , [User] SYSNAME
    , IsDynamic BIT
    );

With our table in place, let’s create a procedure to insert data to the table. We’ll capture information about the login and execution context as a means of confirming the execution state related to our business case.

-- if the insert procedure already exists, drop it
IF OBJECT_ID(N'uspInsert', N'P') IS NOT NULL DROP PROCEDURE uspInsert;
GO

-- create the demo procedure
CREATE PROCEDURE uspInsert (
    @bolUseDynamic BIT = 0
    )
AS
BEGIN
    IF @bolUseDynamic = 1
    BEGIN
        -- insert a record via dynamic SQL
        DECLARE @sql NVARCHAR(MAX);
        SET @sql = N'INSERT INTO tbl ([Login], ContextLogin, [User], IsDynamic)
            VALUES (ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME(), 1);'
        ;
        EXECUTE (@sql);
    END
    ELSE
        -- insert a record via static SQL
        INSERT INTO tbl([Login], ContextLogin, [User], IsDynamic)
        VALUES (ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME(), 0)
        ;
END
GO

The last step in our setup is to grant execute permission to the database user.

-- grant the ReadOnlyUser permission to execute the procedure
GRANT EXECUTE ON uspInsert TO ReadOnlyUser;

Okay. Everything is in place. Let’s run a test! We’ll run the first test under our own login simply to confirm that the procedure works and captures the information we need to see.

-- test the procedure
EXECUTE uspInsert @bolUseDynamic = 0;
EXECUTE uspInsert @bolUseDynamic = 1;

-- confirm results
SELECT * FROM tbl;

So far, so good. Next, let’s run the same two procedure executions under the context of the ReadOnlyLogin. To achieve this, we could reconnect under the other login. Alternately, let’s just impersonate it instead!

-- test the procedure
EXECUTE AS LOGIN = 'ReadOnlyLogin';
EXECUTE uspInsert @bolUseDynamic = 0;
EXECUTE uspInsert @bolUseDynamic = 1;
REVERT;

-- confirm results
SELECT * FROM tbl;

As you can see, the second procedure execution failed when executed under the context of the ReadOnlyLogin:

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'tbl', database 'devDB', schema 'dbo'.

Why did it fail? Because even though the dynamic SQL is executed within a stored procedure on which the ReadOnlyLogin is granted permission, dynamic SQL executes within a separate session. The login impersonation is passed through, but the sql is no longer strictly within the context of the procedure. And outside of the procedure, the ReadOnlyLogin does not have permission to insert into the “tbl” table.

As ever, let’s be sure to clean up after ourselves.

-- drop objects
IF OBJECT_ID(N'uspInsert', N'P') IS NOT NULL DROP PROCEDURE uspInsert;
IF OBJECT_ID(N'tbl', N'U') IS NOT NULL DROP TABLE tbl;
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ReadOnlyUser')
    DROP USER ReadOnlyUser;
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ReadOnlyLogin')
    DROP LOGIN ReadOnlyLogin;

For additional information on pitfalls of dynamic SQL related to execution context, give this post a read.

For Each DB… Almost

The potential problems with the undocumented system procedure sp_MSforeachdb are well known to seasoned TSQL developers. Yes, it doesn’t pickup all databases (only those that are accessible at the time of execution). Yes, under certain versions of SQL Server (such as 2005 RTM) it picks up snapshots as well. Yes, it is a cursor behind the scenes and thus doesn’t offer any performance advantage to rolling your own.

And here is one more problem: it doesn’t trap @@FETCH_STATUS = -2.

What is the significance of that? Well, it is one more way for the procedure to let you down in a way you didn’t expect. Let’s take a closer look.

The following code will use the system procedure to list every* database on the server (*within the constraints of accessibility mentioned above! ;).

-- declare and set a sql string to print the database name
DECLARE @sql NVARCHAR(2000) = N'PRINT ''?'';';

-- use sp_MSforeachdb to print the name of "each" database
EXECUTE sp_MSforeachdb @command1 = @sql;

On my DEV server, the above query generates the following result:

master
tempdb
model
msdb
ReportServer$DEV
ReportServer$DEVTempDB
devDB
devDW

Six “system” databases plus a dev database and a dev data warehouse. So far so good. But… what if one of those databases goes offline while the procedure is running? Let’s find out!

For this example I will take the second to last database (devDB) offline during procedure execution. If you are testing at home, choose any database except a system database or the last database in your list. The system database restriction should be obvious; excluding the last database will become apparent after the demo.

-- declare and set a sql string to take the "devDB" database offline
-- and print the context database name from the MSforeachdb procedure
DECLARE @sql NVARCHAR(2000) = N'IF ''?'' = ''master''
    ALTER DATABASE devDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
PRINT ''?'';';

-- use sp_MSforeachdb to execute the sql against "each" database
EXECUTE sp_MSforeachdb @command1 = @sql;

-- put "devDB" back online
ALTER DATABASE devDB SET ONLINE;

On my DEV system, when I execute the previous query the following results are returned:

master
tempdb
model
msdb
ReportServer$DEV
ReportServer$DEVTempDB

Notice anything? Not only is devDB missing from the list… but so is devDW! And yet, no execution errors reported, so I’d have no idea anything went wrong.

But, what actually did go wrong? As I mentioned, the problem is that sp_MSforeachdb does not trap @@FETCH_STATUS = -2. To demonstrate, let’s first recreate what the procedure is doing.

-- declare the cursor output variable
DECLARE @sysDBName SYSNAME;

-- declare and initialize the cursor
DECLARE crs CURSOR FOR SELECT name FROM sys.databases;

-- open the cursor
OPEN crs;

-- initialize the cursor output
FETCH NEXT FROM crs INTO @sysDBName;

-- iterate through the cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
    -- take the "devDB" offline
    IF @sysDBName = 'master'
        ALTER DATABASE devDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
    -- print the cursor context database
    PRINT @sysDBName;
    -- fetch the next value from the cursor
    FETCH NEXT FROM crs INTO @sysDBName;
END

-- close cursor
CLOSE crs;

-- release cursor from memory
DEALLOCATE crs;

-- put "devDB" back online
ALTER DATABASE devDB SET ONLINE;

The result of the above query is the same as the procedure; devDW is missing and no error is returned. But now we can easily add some additional syntax to identify and report on the issue. For example, add the following immediately after the FETCH command within the WHILE loop:

-- trap for missing records
WHILE @@FETCH_STATUS = -2 BEGIN
    PRINT 'The row fetched is missing. Continuing to the next row....';
    FETCH NEXT FROM crs INTO @sysDBName;
END

Now we can see that the devDB row could not be fetched because it is missing from the cursor record set. But we can also chose to continue on to the next record, or take other action as needed. Another approach could be to loop through cursor results on @@FETCH_STATUS <> -1 instead of = 0. This example also shows why we didn’t get an error record — because, despite the unexpected results, no error actually occurred! @@FETCH_STATUS -2 is not an error, but a possibility that SQL Server anticipates and has already enumerated. See here for more info.

While we don’t need any more reasons to abandon sp_MSforeachdb, I suppose it is worth remembering that the procedure is undocumented for a reason.

Backup Databases on Remote Server

As the last in a three-post series on database maintenance stored procedures (the other two being CHECKDB and file clean-up), the following stored procedure extends a T-SQL script we discussed previously that backs-up all databases on a server. The procedure below adds the capability of specifying the backup type as either FULL or LOG, as well as specifying the backup directory and, optionally, a remote server on which to run the script.

This procedure makes use of linked server functionality, so be aware of security contexts and credentials. Specifically, ensure the account under which this procedure executes has the necessary permissions on the target server on which the backups will be performed, as well as write permissions to the file path to which the backup files will be saved.

We’ll create the procedure within our maintenance schema (mx).

CREATE PROCEDURE mx.uspBackup
    @strBackupType VARCHAR(4) = N'FULL' -- options: FULL, LOG
    , @strBackupDirectory VARCHAR(50) -- the local file path
    , @sysServerName SYSNAME = NULL -- optional remote server name
AS
BEGIN
    -- Create a linked server, if needed
    IF @sysServerName IS NOT NULL
    BEGIN
        -- Create an effectively-unique name for a linked server
        DECLARE @sysServer SYSNAME = @sysServerName
            + N'_'
            + CONVERT(CHAR(36), NEWID())
        ;

        -- If the linked server already exists, drop it
        IF EXISTS (SELECT name FROM sys.servers WHERE server_id <> 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;

        -- Add the linked server
        EXECUTE master.dbo.sp_addlinkedserver
            @server = @sysServer
            , @srvproduct = N'SQL Server'
        ;

        -- Add login information to the linked server
        EXECUTE master.dbo.sp_addlinkedsrvlogin
            @rmtsrvname = @sysServer
            , @useself = N'True'
            , @locallogin = NULL
            , @rmtuser = NULL
            , @rmtpassword = NULL
        ;
    END

    -- Create the file directory
    EXECUTE master.dbo.xp_create_subdir@strBackupDirectory;

    -- Declare and initialize the backup SQL
    -- Exclude tempdb from all backup operations
    DECLARE @strSQL NVARCHAR(MAX) = N'
        IF ''?'' <> ''tempdb'' -- Do not execute against tempdb'
    ;

    -- If performing a log backup, only include simple recovery model dbs
    IF @strBackupType = N'LOG'
        SET @strSQL += N'
AND N''SIMPLE'' <> ( -- Confirm recovery model for log backups
      SELECT recovery_model_desc
      FROM sys.databases
      WHERE name = N''?'')'
        ;

    -- Define the backup path
    SET @strSQL += N'
BEGIN
      -- Define the backup path
      DECLARE @strPathFull NVARCHAR(128) =
            N''' + @strBackupDirectory + '\?\?''
            + N''_backup_''
            + CONVERT(CHAR(8), GETDATE(), 112)
            + N''_''
            + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 114), '':'', '''')'
    ;

    -- Add the backup type file extension and backuyp type command
    IF @strBackupType = N'FULL'
        SET @strSQL += N'
            + N''.bak''
;
      -- Backup the database
      BACKUP DATABASE [?]'
        ;
    ELSE IF @strBackupType = N'LOG'
        SET @strSQL += '
            + N''.trn''
      ;
      -- Backup the log
      BACKUP LOG [?]'
        ;

    -- Continue the backup T-SQL command
    SET @strSQL += N'
      TO DISK = @strPathFull
      WITH
            NOFORMAT
            , NOINIT'
    ;

    -- Set the backup name based on the backup type requested
    IF @strBackupType = N'FULL'
        SET @strSQL += N'
            , NAME = N''?-Full Database Backup'''
;
    ELSE IF @strBackupType = N'LOG'
        SET @strSQL += N'
            , NAME = N''?-Log Backup'''
        ;

    -- Continue the backup T-SQL command
    SET @strSQL += N'
            , SKIP
            , NOREWIND
            , NOUNLOAD
            , STATS = 10
      ;
     
      -- Declare a variable to retrieve backup position
      DECLARE @intPos INT;
     
      -- Initialize the backup position variable
      SELECT @intPos = position
      FROM msdb.dbo.backupset
      WHERE
            database_name = N''?''
            AND backup_set_id = (
                  SELECT MAX(backup_set_id)
                  FROM msdb.dbo.backupset
                  WHERE database_name = N''?'')
      ;
     
      -- Check for error in initializing the backup position variable
      IF @intPos IS NULL
            RAISERROR(N''Backup verification failed for database ''''?''''.'', 16, 1)
      ;
     
      -- Verify the backup was created successfully
      RESTORE VERIFYONLY
      FROM DISK = @strPathFull
      WITH
            FILE = @intPos
            , NOUNLOAD
            , NOREWIND
      ;
END'
    ;

    -- Execute the SQL against each database, remote if needed
    IF @sysServerName IS NULL
        -- Execute locally
        EXECUTE sp_MSforeachdb@strSQL;
    ELSE
    BEGIN
        -- Prepare SQL for remote execution
        DECLARE @strSQL2 NVARCHAR(MAX) =
            N'EXECUTE (N''EXECUTE sp_MSforeachdb N'''''
            + REPLACE(@strSQL, N'''', N'''''''''')
            + N''''''') AT '
            + N'[' + @sysServer + N']'
        ;

        -- Execute remotely
        EXECUTE sp_executesql@strSQL2;

        -- Drop the linked server
        IF EXISTS(SELECT name FROM sys.servers WHERE server_id <> 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;
    END
END

CheckDB on Remote Server

While we are on the subject of maintenance scripts (see previous post on file clean-up), here is one to run a CHECKDB against all databases, even on a remote server. We’ll make use of the sp_MSforeachdb system stored procedure as well as linked servers.

Generally linked servers are unpopular as they can present a security risk due to embedded credentials; however, we will minimize that risk by specifying that credentials are simply passed through to the linked server rather than embedding alternate credentials within the linked server definition. Since we are passing through credentials, you should ensure that the credentials under which the CHECKDB procedure will execute are sufficient to access and run the commands on the linked server.

We’ll create this stored procedure within our maintenance schema (mx).

-- Create a stored procedure in our maintenance schema
CREATE PROCEDURE mx.uspCheckDB
    @sysServerName SYSNAME = NULL -- server name if to be executed remotely
AS
BEGIN
    -- Create a linked server, if needed
    IF @sysServerName IS NOT NULL
    BEGIN
        -- Create an effectively-unique name for a linked server
        DECLARE @sysServer SYSNAME =
            @sysServerName
            + N'_'
            + CONVERT(CHAR(36), NEWID())
        ;
        -- If the linked server already exists (however unlikely), drop it
        IF EXISTS (SELECT name FROM sys.servers WHERE server_id != 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;
        -- Add the linked server
        EXECUTE master.dbo.sp_addlinkedserver
            @server = @sysServer
            , @srvproduct = N'SQL Server'
        ;
        -- Add login information to the linked server
        EXECUTE master.dbo.sp_addlinkedsrvlogin
            @rmtsrvname = @sysServerName
            , @useself = N'True'
            , @locallogin = NULL
            , @rmtuser = NULL
            , @rmtpassword = NULL
        ;
    END
    -- Initialize variables
    DECLARE @strSQL NVARCHAR(MAX) = N'-- Set the database context
USE [?];
-- Run the CHECKDB command
DBCC CHECKDB(N''?'') WITH NO_INFOMSGS;'
    ;
    -- Execute the SQL against each database, remote if needed
    IF @sysServerName IS NULL
        -- Execute locally
        EXECUTE sp_MSforeachdb @strSQL;
    ELSE
    BEGIN
        -- Prepare SQL for remote execution
        -- Wrap the existing SQL within an EXECUTE command and specify
        -- to execute that command at the linked server; also need to
        -- replace single quotes with quadruple quotes as the command
        -- will execute at the third nested level
        DECLARE @strSQL2 NVARCHAR(MAX) =
            N'EXECUTE (N''EXECUTE sp_MSforeachdb '''''
            + REPLACE(@strSQL, '''', '''''''''')
            + N''''''') AT '
            + N'[' + @sysServer + N']'
        ;
        -- Execute remotely
        EXECUTE sp_executesql @strSQL2;
        -- Drop the linked server
        IF EXISTS (SELECT name FROM sys.servers WHERE server_id != 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;
    END
END

Backup File Clean-Up

Here is a handy little stored procedure to delete backup files from your file system. It makes use of the undocumented system stored procedure xp_delete_file; I’ve provided some information on the parameters the procedure accepts in the example below.

FYI, xp_delete_file will only delete SQL backup files or SQL maintenance plan reports, so don’t try to use it to delete non-SQL files on your file system. And it doesn’t simply look at the file extension either… it actually parses the file header information to determine the file type.

While this sample clean-up procedure is rather simple, it could easily be extended to accept additional parameters to calculate the date boundary, whether to delete the backup history as well as (or instead of) backup files, whether to traverse sub-folders, and so on.

For this example we’ll create the procedure in our maintenance schema (mx). Once created you can simply run it on-demand, through the SQL Agent, or via the windows task scheduler with the command-line SQL utility.

Enjoy!

CREATE PROCEDURE mx.uspCleanUp
    @strDirectory NVARCHAR(50)
    , @strFileExtention CHAR(3) = 'bak' -- options: bak, trn
    , @dtDeleteBefore DATE = NULL
AS
BEGIN

    -- Declare and initialize variables
    DECLARE @dt DATE =
        ISNULL(@dtDeleteBefore, DATEADD(WEEK, -2, GETDATE()));

    -- Delete the backup history older than the specified date
    EXECUTE msdb.dbo.sp_delete_backuphistory
        @oldest_date = @dt
    ;

    -- Delete the backup files older than the specified date
    EXECUTE master.dbo.xp_delete_file
        0 -- file type; 0 = backup, 1 = report
        , @strDirectory -- directory in which to look for files
        , @strFileExtension -- extension of files to look for (no period!)
        , @dt -- delete files older than this date
        , 1 -- traverse sub-folders; 0 = FALSE, 1 = TRUE
    ;

END