Sql-server – SQL Server drop and create all users in every DB

Use a DDL trigger to prevent the drop if the login name doesn't match what you expect. Modify the database name and login name as appropriate.

USE myDB
GO

CREATE TRIGGER no_drop ON DATABASE FOR DROP_TABLE
AS
IF SUSER_SNAME() != 'DOMAIN\username'
BEGIN
    RAISERROR('No table dropping.', 16, 1)
    ROLLBACK TRANSACTION
END

To be clear. When talking about a DSN-less connection what you are really saying is that you are using a connection string that uses the native OLE DB driver to talk to SQL as opposed to ODBC. Just want to clarify.

Usually with a tenant data model the really critical thing to solidify is the process that authenticates users and issues them a connection to the tenant database. Most tenant database models don't use windows authentication as they are hosted or an SaaS product. Your question makes it sound like you are using domain accounts however.

If you were assigning sql logins to each tenant or each windows login was viewed as a unique tenant then the easiest thing to do would be to define the TenantID and the sql login used in the connection to be the same value. Then you wouldn't have to worry about a mapping table. Why? Because SUSER_SID() (the login id) IS the TenantID. You could also use SUSER_SNAME() if you wanted.

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

If you are not using sql logins or each tennant is associated with more than one login (such as with windows authentication) or business rules preclude you from using the login as the TenantID then yes. The next best thing would be a mapping table. Something like this for example that stores logins and the tenantID they are associated with.

UserKey   |  Tenant       |  TenantID  |  LoginName    |  LoginID
------------------------------------------------------------------------
1         |  Bills Bolts  |  111       |  billbolt     |  0x010050
2         |  Sams Screws  |  111       |  samscrew     |  0x010010
3         |  Nicky Nuts   |  222       |  nickynut     |  0x010000

In this case the view would look like this:

CREATE VIEW TenantEmployees AS 
SELECT Employees.* 
FROM Employees E INNER JOIN TenantMapTable T
    ON E.TenantID = T.TenantID
WHERE T.LoginName = SUSER_SNAME()  /* or T.LoginID = SUSER_SID() if preferred */

Another critical step however is that rights to the actual table are revoked. Only grant access to the table through the view - or this was all for nothing.

Don't forget to remove access to anything but the views from the tenant user login.

Last - keep in mind that you can query a view just like a table. So nothing is stopping you from filtering it further based on the needs of your application via procs or your ORM or where-ever.

select [firstname] from tenantemployees where [dob]='12/25/1980'