Best practice dropping or disabling

Question

Post reply

Best practice dropping or disabling

ramana3327

SSCoach

Points: 19722
More actions
January 28, 2016 at 10:12 am

#307498

Hi,
I just wonder how the SQL experts do when a member leave the organization.
Do you drop the login or disable the login?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

in my case, I remove the users and logins from SQL Servers, but they stay forever in Active Directory.

I've got a nifty powershell i wrote that imports Active directory data into a SQL table.

that has whether the user is enabled or disabled rendered right in the table.

occasionally i'll run the logic below, but only on demand:

I have another script, that assumes linked servers to the server with that AD table exists, so i can use it in a Central Management Server session:

that script uses a simple cursor to loop through any databases with an if exists/drop user, and ends with an if exists/ drop login for any AD ser that is disabled, and the last login is older than 30 days.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Lowell (1/28/2016)
I've got a nifty powershell i wrote that imports Active directory data into a SQL table.
that has whether the user is enabled or disabled rendered right in the table.

That would be a great thing to write an article on. You should go for it.

In the meantime, I'd love to have a copy of the code. Any chance of you attaching it to this thread?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Just run

exec sp_validatelogins

This will identify any AD users or groups which have been removed from AD but not from the SQL Server instance

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Jeff Moden (1/28/2016)
Lowell (1/28/2016)
I've got a nifty powershell i wrote that imports Active directory data into a SQL table.
that has whether the user is enabled or disabled rendered right in the table.
That would be a great thing to write an article on. You should go for it.
In the meantime, I'd love to have a copy of the code. Any chance of you attaching it to this thread?

That would be handy to have. We're in the process of integrating data from two domains and dealing with so many different employee IDs has become rather cumbersome because one system has multiple IDs per employee. Having a table that could match the logins would make life easier. Lowell, if you have the inclination to write such an article, add me to the list of "encouragers".

Edit: Oh yeah, the subject of the OP. I drop the SQL Server logins and all associated database users. The AD accounts are disabled for a set period of time and then deleted later.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323518 More actions · Answer 5

I'm on the SQL cruise with limited internet, but i'll put together an article;

I tend to shy away from articles, hate getting criticism.

At my job, we just joined two AD forests, and permissions wise, i still can only query my own domain, I have not be able to Get-ADMember the other domain persons or groups yet. i'd like to see that work as well.

i've got a pretty complete solution, actually, all groups, all group members and of course all users/object/computers, all in a suite of SQL tables.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 6

Perry Whittle (1/29/2016)
Just run
exec sp_validatelogins
This will identify any AD users or groups which have been removed from AD but not from the SQL Server instance

Man. Thanks for posting this. I never knew about this command. Every so often I go through a list of users and find one or two that we missed getting rid of. This makes this process SO much simpler. 😀 🙂

ramana3327 SSCoach Points: 19722 More actions · Answer 7

Thank you all.

I disabled the login but the login has mapped to database as DB Owner still when I look for DB owner the user is still coming.

Do I need to disable at db level first?