2

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

7

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.
1
  • 1
    Perfect explanation.. thanks! :) Now I just need to figure out how precisely to use the view filters for all tables :)
    – MicBehrens
    Feb 14 '12 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.