Tag: T-SQL

The difference between REVOKE and DENY

There was confusion recently with a poster that was moving permissions around and asked why I said they should revoke permissions and not deny them. I decided this was worth a post to explain.

If I GRANT SELECT (or UPDATE/INSERT/DELETE) permissions to a user, then they can use those permissions to view data in a table. If I REVOKE the permissions, it’s the same as if the user never had them. They would need to be GRANTed permissions again to see the data.

However, if I DENY them the ability to see data, then that’s different. They can’t see the data, but a subsequent GRANT will not allow them to see the data because the DENY will still be in effect.

It’s a more permanent change, and should be used when you need to be sure that someone cannot see data, not when you are looking to remove permissions. To undo a GRANT, use REVOKE.

June 30, 2011
HASHBYTES – A T-SQL Function
Someone was asking if the HASHBYTES function was a good one to use in T-SQL as far as performance goes.. I wish I had a good reference for the function, but the best one I had on SQLServerCentral was this piece on using it to load a data warehouse. I also wrote an editorial on it not working with strings beyond 8k, which seems to be a bug, or a lack of resources devoted to ensuring string functions work with varchar(max).

The HASHBYTES function returns a hash of an input string. A hash is essential a calculation based on the values of the input, and two inputs that are the same, ought to produce the same hash. One catch with this function is that you provide the algorithm used, which can be one of these:
- MD2
- MD4
- MD5
- SHA
- SHA1
Each of these produces different output, returning a varbinary(max) value. As an example, suppose I hash “Steve Jones”
```
SELECT HASHBYTES('MD2', 'Steve Jones') 'MD2'
UNION
SELECT HASHBYTES('MD4', 'Steve Jones') 'MD4'
UNION
SELECT HASHBYTES('MD5', 'Steve Jones') 'MD5'
UNION
SELECT HASHBYTES('SHA', 'Steve Jones') 'SHA'
UNION
SELECT HASHBYTES('SHA', 'Steve Jones') 'SHA1'
```
The results look like this:

MD2

———————————————-

0x27851A666BFCB4A35F971DD742CDA15F

0x2E978DE4841B1F3651A8DF4B2D2CF5F5C624A76B

0x75931813C7EAAEAB3CD1D8D621935903

0x979AC597C05CA6DE3A88C31A456D1125

As you can see, there’s a different hash for the same value using different algorithms. However if I were to compare the same string to itself, I can easily tell if something has changed. If the hashes aren’t the same, there’s a difference. I’m not sure this is a great use, but the more obvious use is that I can hash a password and then have the user enter their own version, hash it, and compare the results. In this way, the system never needs to know the value.

Just make sure you use the same algorithm
June 28, 2011

Setting a Unique Index on a Bit Field

Can you set a unique index on a bit field? Well, you can, but you’d end up with a very short table of two (or three) rows. I defined this table:

CREATE TABLE [dbo].[BitTest](
    [MyBit] [bit] NULL,
    [MyName] [varchar](50) NULL
) ON [PRIMARY]

GO

USE [db1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_BitTest] ON [dbo].[BitTest] 
(
    [MyBit] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

and then added some data

INSERT BitTest SELECT 1, 'Test'
INSERT BitTest SELECT 0, 'Test2'
INSERT BitTest SELECT NULL, 'Test 3'

SELECT * FROM BitTest

This table has these rows:

Adding another row:

INSERT BitTest SELECT 1, 'Test 4'

gets you an error:

However what about a compound index? What if I make the table larger and add more fields. Here’s a larger table:

CREATE TABLE UniqueBit
( BureauID INT
, CompanyID INT
, DivisionID int
, DefaultType BIT
, ProductName VARCHAR(100)
)
GO
INSERT Uniquebit SELECT 1, 1, 1, 1, 'Product 1'
INSERT Uniquebit SELECT 2, 1, 1, 1, 'Product 2'
INSERT Uniquebit SELECT 3, 1, 1, 1, 'Product 3'
INSERT Uniquebit SELECT 4, 1, 1, 1, 'Product 4'
INSERT Uniquebit SELECT 1, 2, 2, 1, 'Product 5'
INSERT Uniquebit SELECT 1, 2, 3, 1, 'Product 6'
INSERT Uniquebit SELECT 4, 1, 1, 0, 'Product 7'

If I now add a unique index:

CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueBit] ON [dbo].[UniqueBit] 
(
    [BureauID] ASC,
    [CompanyID] ASC,
    [DefaultType] ASC,
    [DivisionID] ASC
) ON [PRIMARY]
GO

It works fine. I can add another unique row like this:

INSERT Uniquebit SELECT 4, 2, 1, 0, 'Product 7'

without an error. Adding in a non-unique row:

INSERT Uniquebit SELECT 4, 1, 1, 0, 'Product 7'

gives me an error:

There’s nothing special about a bit column for a unique index. There are restrictions for bit fields in some ways that relate to indexing, but uniqueness is not one of them.

This was inspired by this post (before the complete details from the OP): http://www.sqlservercentral.com/Forums/Topic1126794-149-1.aspx#bm1126850

June 23, 2011

SQL Server Default Backup Directory

Someone asked me in a webinar how to change the default backup directory. I knew, but realized that I didn’t have a reference and ended up with more explanation than needed if I’d had a post. So here it is:

If you right click on a server in Management studio and select properties, you get dialog with lots of options.

If you click on the “Database Settings” you get this:

Note that there is a default path for data files and log files, not not one for backup files.

So how do you change it? In XP or Win 7, click Start and type this right away “regedt32”

That will start the registry editor. You ought to get a UAC box to confirm access, which is fine. Do that and you’ll be in the registry. In the left pane, browse to this path:

HKey_LocalMachine\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer”

Note that the “MSSQL.1” might be different, depending on your instance. For me it’s SSQL10.MSSQLServer.

Note that there’s a “BackupDirectory” key here. You can double click it to change the path:

I changed mine to a new path, c:\sqlbackup. Note that I had to create this folder.

That’s not enough, however. If you go into your adminstrative tools and find the Computer Management and look for Users and Groups (Select groups), you’ll find groups like this. The name varies, depending on the name of your workstation/server and instance.

Once you have this name, I’d copy it and go to your new folder. Right click, select properties, and then the security tab

As you can see, my group isn’t in here, but this group, with the SQL Server service account in it, needs permissions to this folder. So add them, with modify.

Is my default changed? I could now run this:

and I find a file in my new default folder:

June 9, 2011