Expiration date on a user in a specific database Some of our developers needs access to some of the databases in production for a short period. Is there a way for me to set an expiration date on a user in a specific database?
Just to clarify I do not want the login to expire, just the user in a specific database.

USE [Database1] 
CREATE USER [Developer1] FOR LOGIN [Developer1] WITH expirydate = '2018.01.01'
--Add some roles
GO
USE [Database2]
CREATE USER [Developer1] FOR LOGIN [Developer1] WITH expirydate = '2018.02.02'
--Add some roles


MSSQL 2014, 2016 &amp; 2017
To the best of my knowledge, there's no way to set an expiration date on a database user.

I believe the best solution would be what Edgar Allan Bayron suggested:


  you can set a sql server agent job to run daily which compares with today's date. If that date is equal to the "expiry date", then execute a query where it will disable the specific user


Here's a slightly more fleshed out version of that idea:


Create a new database for administrative use (if you don't already have one). We'll assume it's called dba_utilities.
In the dba_utilities database, create a table named db_user_expiration, with columns database (nvarchar(128)), username (nvarchar(128)), and expiration_date (datetime) (plus an id, integer, IDENTITY). You may want to have a removed bit or char(1) column as well.
In the same database, create a procedure to:


identify rows from the table where the user account has expired;
for each row, create a dynamic SQL statement to DROP the user from the indicated database
If the drop is successful, remove the row from the table (or, mark the row as removed).


If there will only be a few of these at a time, I'd simply use a STATIC LOCAL cursor to loop through the records; if the numbers will be very large, you could generate a single SQL statement to do all the accounts at once, but that's more complicated.

NOTE: If you don't create your users with a default schema, then they'll wind up having a schema with the same name as the user created for them. The schema must either be dropped or moved to a different owner before the user can be dropped. Unless your users need a schema of their own, I'd assign them to a default schema owned by the database owner ([dbo] may work well, in most cases), so this is not a problem.
Run this procedure from a SQL Server job as often as you like (from once a day to once an hour, if appropriate). The expiration_date column I suggest is a datetime, so you can specify the time of day when the expiration should take effect, and the next run after that time should see the user dropped.