locate users and the associated dbs...

Question

locate users and the associated dbs...

maertean esaeasner

Ten Centuries

Points: 1162
More actions
April 26, 2005 at 5:44 am

#163376

Hi all,
I am new to SQLServer so please excuse this question. I have a list of users that have left my company over the last while and I need to remove these from my DBs. Now I dont know their username or which database they are associated with so it will mean having to iterate through about 12 different instances of sqlServer to identify the correct names and then contact the people in charge of those dbs to see if they are the correct users... I was wondering if anyone could help me with such a script... I want the data in the following format.
select logs.name, dbs.name , del_users.first_name , del_users.last_name
from
sysxlogins logs, sysdatabases dbs , deleted_users del_users
where
logs.dbid = dbs.dbid
and
(logs.name like '%del_users.first_name%'
or
logs.name like '%del_users.last_name%')
The problem that I am encountering is when I want to compare the first_name and last name of the users that have left the company with the sqlserver users in the db. The reason for this is that when the user were being created there was no structure to the naming conventions as some users were created as first_name.last_name /first_namelast_name/last_namefirst_name and so on so forth. I think I will need a cursor of some sort, but these are quite inefficent.
Can anyone help me out.
Thanks in advance,
M

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Since there seems to be doubts about the format of names and such, you might consider another approach?

Maybe just print out all your users (sort how you see fit) and then just take your list and look for candidates among the db user names.

On the other hand, since you also need to take care of logins when cleaning up, perhaps listing logins would be a better way to go. After you have compiled a list and gotten it confirmed to be valid, then it's time to figure out how to actually remove them. Depending on how many you end up with, it might be different ways to solve it.

..anyway, it's just a though

/Kenneth

maertean esaeasner Ten Centuries Points: 1162 More actions · Answer 2

Hi Kenneth,

I would do that if it was a once off but I am going to put together a webapp so that the people in HR can do this when people leave rather than coming to me with such issues.

cheers for the reply.

Thanks

M

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

ummm.. are you really going to give away that kind of power to HR..?

imo one of the DBAs responsibilities is to be on top of the security regarding one's system(s).

Another, perhaps better way, is to implement Windows authentication logins and having the users as role members instead of SQL logins/users.

That way you can delegate the removal/disabling of NT accounts to those who administer the domain accounts. All you have to do then is to add/remove certain users from their roles, and as a bonus, their names will be the same as their NT usernames.

Just another idea anyways =;o)

/Kenneth

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 4

"ummm.. are you really going to give away that kind of power to HR..?"..Totally agree.

Why not get yourself/admin put in part of the workflow process that deals with employees leaving...so that you 'the expert' can deal with the requests properly. It's fine (and advised) to create an app/range of scripts that assist you getting there...but beware handing it over to HR....they may decide to retire 'sa' on you!!

maertean esaeasner Ten Centuries Points: 1162 More actions · Answer 5

Hi Lads,

That is quite a valid point alright, but since there are so many servers/users I would like to automate the process of identifying the users to a small number so that I won't have to go searching through each of the list of logins each time I get a list of users that have left.

There is a big number of users that use our databases all over the country so to implement some sort of procedure 'on leaving notify me' would take an awful lot of communication and I know for a fact would not be followed.

Security protocols in here won't allow us to use windows logins...Politics...

Thanks for the feedback folks, any more is greatly appreciated!

M

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 6

If a process is too cumbersome, then maybe you could be proactive...and go to HR...once a month (or just before your next audit visit???)...and ask for all 'leavers since dateX'. Keeping a lid on the interals will make the overall task more manageable.

It's probably daunting if it hasn't been cleaned up for a while.

"The only way to keep a server/desktop clean....is to keep it clean always"

maertean esaeasner Ten Centuries Points: 1162 More actions · Answer 7

True,

BUt it still doesn't get over the fact that I will have to manually go through a list of a few thousand users each time someone leaves. I would like to at least narrow down the list to a few. As after I have done that I have to go to people in charge of the applications associated with those databases and confirm if that is the user that has left.

However if I design an application to do that I can get HR to send me the names in a excel file, I can pick up the excel file into sql server, execute my stored proc and list the users and their associated databases. Then I can automate a script that will send an email to the right people asking them if that is the user that has left, upon receipt I can easily delete the user. Just trying to make life easier for all involved...

M