I was recently given a task to give 20 people read permissions on a dev server. My organization uses AD groups to manage these on the production side of things but on the dev side, it’s a bit like the wild west.
I wasn’t sure if these users were already in the database or if they were new. I figured this was a good time to play around and create some sort of auto inserter for me. I wanted to be able to put the person’s user name in and wanted it to return if they were in the system or not. If they were, great, add them to this DB, if not, add them to the server and then DB.
I ran into the situation where there was a name on the list of people I had who’s username was wrong. Well…how can I check AD to make sure they were there without it blowing up? I couldn’t find a solution so I decided to use TRY…CATCH and grab the error.
Before anyone says anything about automatically entering users, this was just for my personal use and is not intended to be placed in any application.
Check it out:
USE MASTER SET ansi_nulls ON GO SET quoted_identifier ON GO -- ============================================= -- Author: Adam Mikolaj-- Create date: 12 May 2011-- Description: Little stored procedure for entering in users -- ============================================= CREATE PROCEDURE uSP_AutIinserter @USER VARCHAR (100), @dbname VARCHAR(100), @DOMAIN VARCHAR(100) AS BEGIN DECLARE @LiveUser BIT,@Sqls VARCHAR(500) IF EXISTS (SELECT *FROM syslogins WHERE name LIKE '%' + @USER + '%') BEGIN SET @LiveUser = 1 END ELSE SET @LiveUser = 0 IF @LiveUser = 0 BEGIN SET @Sqls = 'CREATE LOGIN [' + @DOMAIN + '\' + @user +'] FROM WINDOWS WITH DEFAULT_DATABASE=' +@DBName+'' BEGIN TRY EXEC (@Sqls) END TRY BEGIN CATCH SELECT Error_message() AS errormessage; END CATCH PRINT 'USER IS now IN the system' END ELSE PRINT 'USER already IN the system'END GO
To call this procedure just do this:
uSP_AutoInserter ‘Dude’, ‘DB’, ‘domain’