Deleting Users - Safety considerations 2

[tadd]

I've been asked to delete some users from my new client's live production database - user that they perceive are no longer needed. SQL Server admin is not my strength. What precautions should I take? I don't want to inadvertently disable some process because the user that owns it no longer exists, etc. Will SQL warn me before I delete any users that own other objects in the database? Is it possible to easily "disable" a user rather than delete it?

Thanks for any help.

 

[ptheriault]

What version of SQL are you using? You can disable users in SQL 2005. If it is SQL 2000 I would just change the password for a few days. If nothing breaks then delete them. You won't be able to drop logins that own objects. You will get an error message.

 

[Catadmin]

The best way to disable a user is to go into its properties and remove access for all databases. If someone screams, you know they needed it for something.

If you want to keep the old login permissions, there's a way to pull them up (Do a search on this section and look in the FAQ section. Someone's documented it), but if you're sure there's no granular permissions, just go into the properties and write down the Server Roles, Database access, DB Roles and create a new user called User_Archive with those same permissions. Then delete the old user and wait for the screaming to begin.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[tadd]

Thanks everybody for quick reply. This is very helpful, I will give a try...

 

[tadd]

PS: This is a SQL 2000 database.

 

[tadd]

ptheriault states that: "You won't be able to drop logins that own objects. You will get an error message."

However, I just created a temporary login, created a DTS object, changed the owner to the new login I created, and then deleted the new login. SQL 2000 let me delete the login without a peep! When I look at the DTS in Enterprise Manager, it shows that the login I created still owns the DTS package, but that login is gone!

Try it.

 

[ptheriault]

tadd,
Try that with a table or stored procedure from with a database. It will fail. DTS packages don't follow the same rules. In fact I have never been able to change the owner of a DTS package.

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

DTS packages don't "exist" in the same spot other database objects exist. There are three different ways to store the DTS package, none of which are in the same location as tables/views/jobs/etc. Because of this, it's quite probable that MS didn't design a search for DTS package owners for the drop login routine.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[tadd]

Thanks everybody.

I did notice that I cannot delete a *Job* owned by a logon.

BTW, I have a script that allows me to change the owner of a DTS Package. I found it on this site. I will post it later when I get to work...

 

[tadd]

Thanks to EdwinGene for posting this earlier:
----------------------------------------------

You can change a DTS Package owner by executing the following stored procedure from Query Analyzer.

Execute msdb..sp_reassign_dtspackageowner
@name = 'packagename',
@id = 'packageid',
@newloginname = 'newloginname'


You can get the package id by executing the following query:

Select Distinct name, id, owner From msdb..sysdtspackages Where name = 'packagename'


There is a short article on the web about changing DTS Package owner name if you want to read it:

http://databasejournal.com/features/mssql/article.php/1461511

 

[tadd]

Is there a way to link to a previous post so that I don't duplicate information?

 

[ptheriault]

Yes you just put the thread number in the post.
That is a great script to change DTS ownership.

- Paul
- Database performance looks fine, it must be the Network!