SQL Server Performance Forum – Threads Archive
Method of programmatically removing users
We have a table in a database on our server with employees. When an employee is no longer active, there is a flag in the table that is changed. We want to run a script that will delete any inactive users (based on that flag) from the server. The catch, however, is that the user may have access to other databases as well. I can scan through the DBs to determine which ones the user has rights to. However, in order to use sp_revokedbaccess, you have to be in that database. The original idea was to use a cursor to cycle through the DBs where the user had access and remove them. Then, once the process was done, we’d execute sp_droplogin. The problem is that while running that cursor, we have to use "use @dbname" to switch to that database to run sp_revokedbaccess. Unfortuantely, that doesn’t work. Does anyone have any ideas on how to go about doing this? Any help would be greatly appreciated. Yours truly, Zev SteinhardtTry executing sp_revokedbaccess, qualified with the database name that you want to run it on.
Ex: Exec dbname..sp_revokedbaccess
You know, I thought I had tried that and it didn’t work. But when I go back and do it now, it works. Thanks for your help ykchakri. Zev Steinhardt
]]>