Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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