Modifing the SYSUSER table

[EM1107]

I would like to be able to modify the SYSUSER table and delete old users, by default the configuration does not allow me to do this. I have to go in SQL server Enterprise Manager and change the option manually.

Where can I find the configuration to allow modification to be made directly to the system catalogs and what would be the best approach to modified it.

 

[tlbroadbent]

You should use the system stored procedures rather than updating system tables directly. It is safer to do so.

sp_revokedbaccess [ @name_in_db = ] 'name'

If you insist on updating the table directly, you can reconfigure the system as follows.

sp_configure 'allow updates', 1
Reconfigure with override

<update sysusers>

sp_configure 'allow updates', 0
Reconfigure with override
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[RobertT687]

DON'T alter system tables directly. In the target database use sp_dropuser 'username' to remove a user and in 'master' use sp_droplogin 'loginname' to remove a login id.

 

[Guest_imported]

I have tried all that and it does not do what a need.

Even if I use the store procedure to remove the user the information of the user remain in the sysuser and when I restore the database and try to have the application reconfigure with a new user it will not let me add the user as it already exist in the table.

 

[tlbroadbent]

If you backup a database, drop the user and then restore the database, the user will still be there. Why? The sysusers table is backed up with the database.

sp_revokedbaccess (SQL 2000) or sp_droplogin will drop the user from the sysusers table. However, if the user owns objects in a database, these procedures don't drop the user. Do you get any messages when you run the SPs?

If you delete a user from sysusers and the user owns objects, you'll have objects without a valid owner. In addition, related tables such as sysmembers, syspermissions and sysprotects will not be updated. That is just one reason NOT to update system tables directly.

Perhaps, there is another way to accomplish what you want. What exactly are you trying to do and what are the steps you are currently using? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Guest_imported]

What I am trying to do is very simple, I have created a script that will recreate a new database, take the backup files and restore it on the newly created database.

Once this is done, I change the owner of all the object in the database. This steps is been done properly. Once completed I drop all the users in the database and recreate new one. Than I reassign the object back to that new user. The script tell me that it as been completed with success. When I verify the object it is done properly. The next step is to assign the new user to that database, the script tell me that it as been done succesfuly but when I look at the permission in the database the user does not belong to the database. For that reason the application will not work. Than what I do is to allow change to the system table and delete the entry created for the old user under the sysuser table. At that point I reassign the right to the new user and it work.

What I want to do is to do this trought the script so I do not have to use the Enterprise Manager everytime.

 

[tlbroadbent]

It may be simple to you but I'm not quite with you on this. You said, &quot;Once completed I drop all the users in the database and recreate new one. Than I reassign the object back to that new user. The script tell me that it as been completed with success. When I verify the object it is done properly. The next step is to assign the new user to that database, the script tell me that it as been done succesfuly but when I look at the permission in the database the user does not belong to the database.&quot;

You mention adding two users &quot;recreate new one&quot; and &quot;assign the new user&quot;. Are there two users or just one? Could you post the part of the script that creates the users and changes the objects' owner? It would be helpful to see the script rather than try to guess what it does. Please tell the version of SQL Server you use.

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.