Re: User Termination

Thanks Hari,

I guess I was looking to create a stored proc that would do all this. So
all I would need to do is something line this

exec dropandremove username

and that would pass my parameter and remove all db access as well as the
login. The reason for this is if a user no longer works for the company, I
have to search through a couple dozen servers to see where that user had
access, and then remove it. So the plan is to drop the stored proc on
multiple servers, then just run the drop. Viola, user maintenance is
automated. But yes I agree with you, it is best done as a manual task.
Just too many servers and not enough DBA.

Thanks,
Aaron
"Hari Prasad" <hari_prasad_k@xxxxxxxxxxx> wrote in message
news:eSo0rKmHHHA.3616@xxxxxxxxxxxxxxxxxxxxxxx
Hello,

I prefer to do this manually; basically you need to transfer the ownership
using sp_changeobjectowner if you have this user owns any objects.

1. Execute sp_helplogins <Login_Name> to get all the database user mapping
in different
2. Go to each database and drop the user sp_dropUser <username>
3. Finally drop the login using sp_droplogin <loginname>

Thanks
Hari

"Agoo" <agoo@xxxxxx> wrote in message
news:eHG3SFiHHHA.4688@xxxxxxxxxxxxxxxxxxxxxxx
Hi All,

I'm a newby to SQL and I would like to create a SQL stored proc to drop a
given user from all databases, and the drop the login.

So if any given server has 10 or 12 databases, I was hoping the proc
would loop through whatever the databases are : Select name from
master..sysdatabases and drop the user if it exists. Then finally drop
the login from the server.

Anybody got something like this already? Much appreciated.
-Aaron
Agoo@xxxxxx







.

Relevant Pages

  • Need help with query
    ... I have a table containing listings of servers, databases, and users. ... CREATE TABLE Servers (ServerName sysname NOT NULL, DBName sysname NOT NULL, ... INSERT Servers (ServerName, DBName, Login) ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Proc rights and joinning to another database
    ... If the databases and tables has the same owner, ... As of sp3, ... I have a little problem with one of my Stored Proc. ... rights to my users on tables but I give execute rights on my Stored ...
    (microsoft.public.sqlserver.server)
  • Re: New to C# - DB question
    ... express installed and setup a stored proc for the login stuff using ... parms. ... My inital code was just to see if I could connect to a DB and ...
    (microsoft.public.dotnet.languages.csharp)
  • Remote Stored Procedures
    ... I have a stored proc on a server called lax-dd and the ... domain and the sa passwords are the same on both servers. ... have problems finding the store proc... ...
    (microsoft.public.sqlserver.connect)
  • Help with sp_helpmergepullsubscription
    ... This stored proc seems to have started running periodically on one of our ... servers and nobody can determine why. ... We are not using replication and have ...
    (microsoft.public.sqlserver.server)