Using View filters for tenants in SQL-Server 2008

Question

I'm currently creating a Multi-tenant data architecture in a SaaS BI-product.

The product are using shared database and shared schema, but with tenant view filters and tenant data encryption (Inspiration found here: http://msdn.microsoft.com/en-us/library/aa479086.aspx)

When creating the view filter, I can do both these:

CREATE VIEW TenantEmployees AS
SELECT * FROM Employees WHERE TenantID = SUSER_SID()

CREATE VIEW TenantEmployees AS
SELECT * FROM Employees WHERE TenantID = SUSER_ID()

Using SID() I am using the Security Identifier that looks something like this:

0x3425F1595A67774ABF485CDC30285443

Using ID() I am using the normal Identifier that looks something like this:

265

Which one is the absolute best to use and why? :) I expect that both are absolutely unique and will never be reused in newer user-logins

Answer 1

I'd use SUSER_SID for this reason:

• SUSER_ID is the principal_id column in sys.server_principals
• SUSER_SID is the SID column in sys.server_principals and be generated by Active Directory (Windows login) or SQL Server

If you move the database to another server

• then SUSER_ID will almost certainly be different.
• SUSER_SID will be the same for Windows logins, but can be made the same for SQL logins by specifiying SID for CREATE LOGIN.