Permissions, Batches, and the 2558 Error

Kalen Delaney

I thought that if the System Administrator created a stored procedure, and then granted a user execute permission on that procedure, the user could always run the procedure no matter what statements the procedure contained. As the System Administrator (sa), I created a procedure that contained the TRUNCATE TABLE command, and then granted my user, “joe,” execute permission. However, when Joe tries to execute the procedure, he gets a permissions error. Why?

Your assumption is a bit off. The rule to remember pertaining to when SQL Server will or won’t check permissions is as follows: If you have permission to execute a stored procedure, and that procedure accesses other objects that are owned by the procedure’s owner, SQL Server won’t check to see if you have permission to access the dependent objects. So, suppose the dbo owns a procedure that updates a table called “data” which is also owned by the dbo. If the database owner (dbo) gives Joe permission to execute the procedure, SQL Server won’t verify Joe’s permission to update the “data” table. If someone else owns the “data” table, Joe must have explicit permission to update the table, granted by the table’s owner.

The automatic (inherited) permissions capability only applies to permissions that are inherently grantable. Permission to TRUNCATE TABLE is never grantable by any user to any user, and putting it inside a stored procedure doesn’t change that fact. Only the owner of the table can truncate that table (and the sa, for whom no permissions are ever checked at any time.) There are other commands for which this is also true. A database owner can grant permission to a user to be able to create a table, so the dbo can also put that GRANT command inside a stored procedure:

create procedure Grant_Rights
as
   grant create table to public
return 

However, if the dbo grants execute permission on this stored procedure to another user, that user will encounter a permissions error because the dbo could never independently grant that user permission to GRANT create table permission to others.

The following commands can only be executed by the sa and can never be granted to others:

The following commands can only be executed by the dbo and can never be granted to others:

The following commands can only be executed by the table owner and can never be granted to others:

ALTER TABLE

CREATE INDEX

CREATE TRIGGER

DROP TABLE

UPDATE STATISTICS

I’ve been trying to monitor SQL Server activity using NT Performance Monitor, and I’m confused by the reports of the number of  I/O - Transactions/Sec counter. Can you shed some light on what this counter is actually measuring?

If you look at the “explain” box for the I/O - Transactions/Sec counter in Performance Monitor, you’ll see the definition: “The number of Transact-SQL command batches executed per second.” This means that the counter is keeping track of batches—which aren’t the same thing as transactions. This is a very important observation: In spite of its name, this counter isn’t counting transactions.

In SQL Server, a batch is one or more commands that are sent to SQL Server as a group, to be parsed and executed together. If you’re using the Query Tool, every time you click the green arrow, you’re sending a command batch to SQL Server. A batch can contain one transaction—or it can contain multiple transactions. With SQL Server, any single INSERT, UPDATE, or DELETE statement is a transaction by itself, but you can combine multiple statements into a user-defined transaction by using the commands BEGIN TRAN and COMMIT TRAN. A user-defined transaction can either be all in one batch or can span batches. In effect, there’s potentially a many-to-many relationship between batches and transactions. For example, here’s a transaction that spans three command batches. Assume you’re using the Query Tool:

begin tran
update authors set state = ‘HI’  
-- all the authors are moving to Hawaii 
<click green arrow to execute>
delete publishers where state = ‘CA’  
-- get rid of all the California publishers
<click green arrow to execute>
commit tran
<click green arrow to execute>

This single transaction would be counted as three command batches with the Performance Monitor counter. By contrast, here’s a batch that contains multiple transactions. Because there are no BEGIN TRAN and COMMIT TRAN keywords, the UPDATE statement is a transaction by itself.

declare @num int
select @num = 1
while @num < 100 begin
   update titles set price = price * 1.1
   select @num = @num + 1
end

<click green arrow to execute>

Even though the above code will execute 100 transactions as the WHILE repeats 100 times, the Performance Monitor counter will record it as a single command batch in the I/O - Transactions / Sec counter. If you want this counter to really only measure transactions per second, make sure that every batch contains one transaction and that every transaction is in a single batch.

In your November 1997 column ( “SQL Essentials: Keys, Devices, and In-Place Updates”), you indicated that it could be disastrous to load a dump of a database into one that was created with a different order of data and log fragments. I’ve actually done this on several occasions, and I haven’t noticed any problems. Is there something I’m missing?

The issue with mismatched fragments isn’t that it’s always disastrous, but that it can be. If you end up with data where before you had log defined, SQL Server will be able to find the data. It keeps track of tables by their starting page number and doesn’t care, when it’s looking for existing data, if it happens to find that page on a log fragment.

The biggest problem I’ve seen is when lots of data from the original database is loaded into pages that are marked as log in the new database. Yes, SQL Server can find this data when needed, but it’s using up space that should be going to the log. The problem occurs when SQL Server needs new log space, because the server will only look for it in the specified fragments. When looking for new log space, the server will come up short, because all of this data is where it shouldn’t be. In other words, the space marked as belonging to the log is being used by data. I’ve gotten the message that I can’t run commands because the log is full, but when I run dbcc checktable(syslogs), it shows the log is only a few percent full. It’s only counting existing log pages (pages actually containing log records) and dividing by the size of the log to make that computation. It doesn’t take into account pages marked as log pages but filled with data.

If you’ve never encountered problems like these, it might be that your database simply wasn’t very full. You might not have had actual data on the pages that got loaded into the log space. Or, you just might have kept your log cleared out enough that you never noticed. Running dbcc newalloc immediately after the load will show you if there’s any actual data loaded into log space, or log loaded into data space, and if so, it will report error number 2558.

This problem is fairly easy to demonstrate with the pubs database. Because the pubs database, as originally created, has its log and data combined, you’ll first have to create a new database with separate log and data. To be able to follow the example below, you’ll first need to create two new devices: newdatadev should be 4M and newlogdev should be 2M. After you’ve done that, you can follow these steps, using the Query Tool to execute any SQL statements:

Step 1. Create the newpubs database. It will have pages 0–1023 for data, and pages 1024–1535 for log:

create database newpubs
on newdatadev = 2
log on newlogdev = 1

Step 2. Use the Transfer Tool to copy all of the original pubs database into the newpubs database.

Step 3. Dump the database to disk:

dump database newpubs to disk = “c:\mssqlbackup\pubsdump.dat” with init

Step 4. Create a copy of newpubs:

create database newpubs2
on newdatadev = 1
log on newlogdev = 1

Step 5. The newpubs2 database will have pages 0-511 for data, 512-1023 for log, and pages 1024-1535 for data:

alter database newpubs2 
on newdatadev = 1

Step 6. Load newpub2 with the database dump created in Step 3:

load database newpubs2 from disk = “c:\mssqlbackup\pubsdump.dat” 

Even though we’ve mismatched data and log fragments between newpubs and newpubs2, the LOAD command itself won’t give us any error messages.

Step 7. Run dbcc:

dbcc newalloc(newpubs2)

You should see numerous 2558 errors occurring. In addition, if you start doing updates in the newpubs2 database, you could quickly run out of room in the log, even though running dbcc checktable(syslogs) might report that the log wasn’t completely filled.

I have an application running on SQL Server that has a security module that allows a couple of users to define access profiles. I want these users to be able to act as “Security Officers.” I’d like this module also to allow these users to add or drop SQL Server logins, but even if I grant these users permission to execute the sp_addlogin procedure, they get the following message:

You must be System Administrator (sa) to execute this stored procedure.

Is there any way that someone besides the sa can add new logins?

If you look at the text of sp_addlogin, you’ll see that the code does an explicit check to see who’s running the procedure:

IF (suser_id() <> 1)
   begin
   RaisError(15003,-1,-1)
   GOTO LABEL_86RETURN
   end

If you’re not the sa (with an suser_id( ) value of 1), you won’t be able to run code beyond this point. (If you manually run the raiserror statement shown, you can confirm that the error message above is the one generated.) As the sa, you can change the code for sp_addlogin to allow other specific users to get past this point in the error checking. All you need to do is change the IF test shown previously to look for other suid values besides just that of the sa.

However, rather than changing the code of the supplied sp_addlogin procedure, which might be overwritten next time you upgrade your SQL Server, you can create a copy of sp_addlogin (possibly by just cutting and pasting the code) and add the new checks into it. Call the new procedure something like sp_SOaddlogin (for Security Officer).

Because the procedure updates the syslogins system table, when you create or recreate this procedure you must enable updates to system tables by turning on the configuration option “Allow Updates.” Be sure to turn the option off when you’re done creating the procedure. You’ll still need to grant permission on the procedure to any users who will need to execute it.

Kalen Delaney started working with SQL Server in 1987, when she worked for Sybase in Tech Support. Since then, she’s done SQL Server training both for Sybase and for Microsoft. Delaney currently provides independent training and consulting from her Seattle-area consultancy, Solid Quality Learning, Inc. (www.sqlinc.com). Kalen_Delaney@compuserve.com.