Scripting advanced security properties in SQL Server 2008?

Question

Under SQL Server 2008, you can set the permissions for a given user down to the table and/or field level.

In the GUI, it's easy to select the "Securables" and apply them, but is it possible to script the changes?

Answer 1

Yes, you can script the security permissions (including column-level) by using the GRANT statement.

If you want to use SSMS to script the information, assign the permissions in Securables and click on the Script button at the top of the screen.

Below is an example of scripting column-level permissions with T-SQL:

USE master
GO

/* Create test database */
CREATE DATABASE StackO
GO

USE StackO
GO

/* Create a table */
CREATE TABLE TestSelect (
  RowID INT NOT NULL,
  RowValue VARCHAR(1) NOT NULL,
  RowProperty VARCHAR(1) NOT NULL
) 

/* Populate with data */
INSERT TestSelect VALUES (1,'A','X'),(2,'A','Y')

/* Create a user */
CREATE USER SO_User WITHOUT LOGIN

/* Grant the user SELECT permissions on RowID and RowValue */
GRANT SELECT ON TestSelect (RowID) TO SO_User
GO

GRANT SELECT ON TestSelect (RowValue) TO SO_User
GO

/* Deny user SELECT permissions on RowProperty */
DENY SELECT ON TestSelect (RowProperty) TO SO_User
GO

/* Test the permissions */
EXECUTE AS USER = 'SO_User'
GO

/* Confirm select on RowID and RowValue */
SELECT RowID, RowValue FROM TestSelect
GO

/* Confirm error message on RowProperty */
SELECT RowProperty FROM TestSelect
GO

/* Go back to regular user */
REVERT
GO

/* Cleanup */
USE master
GO

DROP DATABASE StackO
GO

And the results:

RowID       RowValue
----------- --------
1           A
2           A

Msg 230, Level 14, State 1, Line 3
The SELECT permission was denied on the column 'RowProperty' 
of the object 'TestSelect", database 'SO', schema 'dbo'.