Microsoft® SQL Server® 2012 Bible

872

Part VI: Securing Your SQL Server (Jose)

RETURN @Approved;

END;

The next code fragment demonstrates how to call the function to test security within a

stored procedure. If the function returns a 0 , then the person does not have suffi cient

security, and the procedure terminates:

-- Check within a Procedure

IF dbo.fSecurityCheck( '118', '2', 3) = CAST(0 AS bit)

BEGIN;

RAISERROR('Security Violation', 16,1);

ROLLBACK TRANSACTION;

RETURN -100;

END;

Using the Windows Login

Some applications are designed so that the user logs in with the application, and, so far,

the row-based security code has assumed that the username is supplied to the procedures.

However, if the SQL Server instance is using Windows authentication, then the security

routines can use that identifi cation.

Rather than request the contact code as a parameter, the security procedure or function

can automatically use suser_sname(), the Windows login, to identify the current user.

The login name (domain and username) must be added to the Person table. Alternatively,

a secondary table could be created to hold multiple logins per user. Some wide area net-

works require users to log in with different domain names according to location, so a

PersonLogin table is a good idea.

The following function is modifi ed to check the user’s security based on her Windows login and

a PersonLogin table. The fi rst query demonstrates retrieving the login within T-SQL code:

SELECT suser_sname();

Result:

--------------

SHARK\David

The following code creates the secondary table to store the logins:

CREATE TABLE dbo.PersonLogin(

PersonLogin UNIQUEIDENTIFIER

PRIMARY KEY NONCLUSTERED DEFAULT NewId(),

PersonID INT NOT NULL

REFERENCES Person.Person(BusinessEntityID) ON DELETE CASCADE,

NTLogin NVARCHAR(128) UNIQUE CLUSTERED);

With the table in place, a simple insert can populate a single row using my login so that

the code can be tested:

c35.indd 872c35.indd 872 7/31/2012 9:59:41 AM7/31/2012 9:59:41 AM

http://www.it-ebooks.info