Kalen Delaney
I read your comments in the October issue of SQL Server Professional and was curious about your last two SQL statements, granting update privileges to public and revoking update from guest:
grant update on schedule to public revoke update on schedule from guest
Since every user of a database receives the permissions of public, wouldn’t this statement have no effect?
This is a good question, and one that gives me an opportunity to talk about a change in behavior in SQL Server 6.5 that’s not very well documented. The short answer to your question is that granting update permission to public does not supersede revoking the permission from guest. The actual reason why this is true changed from SQL Server 6.0 to SQL Server 6.5, but in either version, the earlier two statements would result in the situation where everybody in the database, except someone accessing the database as a guest user, would have permission to update the schedule table.
In SQL Server 6.0, permissions were always applied based on the order in which they were granted or revoked. If you first granted one user permission, then revoked permission from everyone via the public group, then no one would have permission:
grant select on employee to joe revoke select on employee from public
If these two statements were executed in SQL Server 6.0, no one would have select permission on the employee table, not even joe. If you replaced public with another group of which joe was a member, you’d see similar behavior:
grant select on employee to joe revoke select on employee from sales
If these two statements were executed in SQL Server 6.0, no one in the sales group would have select permission on the employee table, not even joe if he were a member of the sales group.
However, if the permission statements were executed in the opposite order, you’d get the opposite behavior, because, in SQL Server 6.0 and earlier, permissions are applied in the order the statements are executed:
revoke select on employee from public grant select on employee to joe
Because permission is granted to joe after permission is revoked from public, joe will have select permission but nobody else will. You can be even more complex by assigning permissions both to the public group and to other groups:
grant select on employee to public revoke select on employee from sales grant select on employee to joe
Again, you’re assuming joe is a member of sales. If these three statements are executed, everybody can select from the employee table except for members of the sales group, with the exception of joe. Joe can select from employee, but nobody else in the sales group can.
This behavior is applicable only to SQL Server 6.0. In SQL Server 6.5 the behavior changed so that a permission explicitly granted to or revoked from an individual user will override permissions for a group, no matter in what order the statements are executed. So, look again at the first two statements:
grant update on schedule to public revoke update on schedule from guest
In SQL Server 6.5, these result in the same behavior as in SQL Server 6.0-everyone but guest has update permission on schedule. However, if you reverse the order, the results will be the same in SQL Server 6.5, but different in SQL Server 6.0.
This change in SQL Server 6.5 also applies to permissions applied to groups other than public. Permissions explicitly granted to a user-defined group will always override permissions to public. If you have a combination of grants and revokes to different groups, you may get different behavior in SQL Server 6.0 and 6.5:
revoke select on employee from public grant select on employee to sales
These two statements will result in nobody but members of the sales group having select permission on the employee table, in both versions of SQL Server. But now reverse the order:
grant select on employee to sales revoke select on employee from public
In SQL Server 6.0, these two statements will again result in nobody having select permission on the employee table. But in SQL Server 6.5, the permission for the sales group will override the permission for public, and all members of sales will be able to select from the table.
I have a trigger on a table for which I sometimes update many rows at a time. Will my trigger fire once for each row affected?
This is a very common source of confusion, but the answer is that the trigger fires once no matter how many rows are affected: one row, 100,000 rows, or zero rows! A lot of people are surprised that the trigger fires at all if no rows are affected, but I don’t make the rules, I just tell you about them. You can run a simple test to prove this to yourself. Create an update trigger on the titles table in the pubs database:
create trigger update_titles_trg on titles for update as print 'You just updated a title!' return
Now, execute a legal update statement with a WHERE clause for which no rows qualify:
-- put all astrology books on sale in the pubs database update titles set price = price/2 where type = 'astrology'
This is a legal update statement; you will not receive an error message but only the information that zero rows were affected. If you have an update trigger on titles it will be fired and the trigger message will print.
Because a trigger fires once, no matter how many rows are affected, the first thing a trigger should do is check the @@rowcount global variable. At the start of the trigger’s execution, this global variable will hold the count of the number of rows that were affected by the statement that caused the trigger to be fired. Because that variable is very volatile and its value changes after almost every statement executed, you should save it in your own local variable if you need to look at it more than once. Your trigger would then start out something like this:
create trigger update_titles_trg on titles for update as declare @num_rows int select @num_rows = @@rowcount ... /* rest of trigger code */
The trigger will fire once for every legal update statement. If a rule or constraint is violated or a duplicate value is inserted into a column with a unique index, the update is not considered legal and the trigger will not fire.
I’d like an insert trigger on a table to update a value in the inserted table. Is this possible?
Technically, this isn’t possible. The inserted and deleted tables that are accessible through triggers are really just views of the transaction log, and you should already be aware that the transaction log isn’t updatable. If you want to update the row that you just inserted you can do that in the original table; you can join with the inserted table to determine which row in the original table is the one you just inserted.
Suppose you’re inserting new rows into the titles table in the pubs database. If a row is inserted with a null price, you’d like to change the notes field to include the string ÔPrice to be determined.’ Your trigger could look something like the following:
create trigger insert_titles_trg on titles for insert as if @@rowcount = 0 return update titles set notes = titles.notes + ' Price to be determined.' from titles, inserted where titles.title_id = inserted.title_id and titles.price is null return
This trigger will work even if multiple rows are inserted, because the join in the update will connect all the rows in inserted to their matching row in titles, based on the primary key of title_id.
Sometimes control characters become embedded in my character columns (for example, ERwin text values). How can I get rid of these characters?
To start with, you have to know the ASCII codes for the characters that you’re looking for, but after that it’s relatively straightforward. In fact, the most commonly occurring control characters have ASCII codes in sequence, so you can write a loop to increment from the first special character code to the last. Here are the most common ones:
ASCII VALUE Meaning ----------- ------- 7 beep 8 backspace 9 tab 10 linefeed 11 vertical tab 12 form feed 13 carriage return
You can then use the charindex function to determine for each special character the position at which it appears in the string. If charindex returns null, the character doesn’t appear. Once the character is found, it can be removed by concatenating together the piece of the string before the character with the piece of the string after the character. For a line feed, you’ll probably want to include a space between the two pieces of the string. To retrieve the position where a particular character occurs, the charindex function would look like this:
select @position = charindex(char(@control_char_
ascii_code),convert(varchar(255),@your_string))
The value 255 is used here for simplicity as the maximum length of a character column. Alternatively, you could declare another variable for the actual length and use the datalength function to determine the value to assign to the variable. The concatenation of the part of the string before the position, and the part of the string after the position, would look like this:
SELECT @your_string = substring(@your_string,1,
(@position - 1)) + substring(@your_string,
(@position + 1),255)
Mark Pohto created a complete stored procedure to strip out all the special characters listed earlier. The complete script to create this procedure is included in the file called STRIPOUT.SQL at the SQL Server Professional Web site (www.pinpub.com/sqlpro). s
STRIPOUT.SQL at www.pinpub.com/sqlpro
Kalen Delaney started working with SQL Server in 1987 when she worked for Sybase in Technical 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. 75050.736@compuserve.com.
To find out more about SQL
Server Professional and Pinnacle Publishing,
visit their website at http://www.pinpub.com/sqlpro/
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the January 1997 issue of SQL Server Professional. Copyright 1997, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.