June 4, 2014 at 2:37 pm
Hi All,
Disable logins on access expiry date(Not windows password expiry). we grant access to users on databases only for 60 days. So access is only valid for 60 days. Then the user should again request access to the database going thru security clearance. Thn the DBA's enable the login. Maintaining all these logins of users manually is causing more confusion.
As we know, we dont have any inbuilt functionality to automatically disable logins in SQL Server.
I have a table where the logins and expirydate were recorded in a DB.
Using this table and SQL Server agent. Can i achieve this process automated ? If yes can someone guide me
CREATE TABLE [dbo].[LoginsExpiry](
[LoginName] [varchar](50) NULL,
[ExpiryDate] [date] NULL,
[Roles] [varchar](500)
) ON [PRIMARY]
GO
June 4, 2014 at 9:17 pm
Not a problem. You just need to string (pun intended) a few concepts together to arrive at a solution. Look for rows in the table where the expiry date is less than the current date. With the list of Login Names build the commands to disable those Logins in a variable and then execute the SQL stored in the variable.
USE YourDatabaseName;
GO
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'ALTER LOGIN ' + QUOTENAME(LoginName) + ' DISABLE;
'
FROM LoginsExpiry;
PRINT @sql;
-- uncomment when happy with the value of @sql
--EXEC(@sql);
Some comments about your table definition:
I would alter the table to accommodate any SQL Server Login Name. A SQL Server Login Name has a data type of NVARCHAR(128), or you can use the built-in type SYSNAME. I would also consider disallowing NULL in the ExpiryDate column and the LoginName column, which SYSNAME does for you. What is the Roles column used for? If it is meant to store a delimited list of Roles then I would consider altering it to handle Unicode values as well (NVARCHAR instead of VARCHAR) since Server and Database Role Names are both of type SYSNAME as well meaning. This means 500 characters could only theoretically accommodate a list of 3 Role names considering a 1 character delimiter and Role names with a length 128. Consider making Roles an NVARCHAR(MAX).
CREATE TABLE dbo.LoginsExpiry
(
LoginName SYSNAME, -- SYSNAME is NOT NULL by definition
ExpiryDate DATE NOT NULL,
Roles VARCHAR(500)
);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 4, 2014 at 9:39 pm
And just to build on the answer provided so far, throw that command into an agent job that runs on a regular schedule.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 9:44 am
Thank you very much for detailed explaination
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply