Who’s who in the server: "sp_rv_helplogin"
Suppose a DBA needs to remove the login "jbrown" from the server, because this person has left the company. The DBA executes the command "sp_droplogin jbrown", but gets the error message "User exists or is an alias or is a database owner in at least one database". Unfortunately, this message doesn’t indicate in which of the, say, 35 databases in the server this login still owns something, so the DBA has no choice but to check each database individually until the "sp_droplogin" command succeeds. Needless to say, this isn’t the most fascinating of tasks.
For this type of problem, "sp_rv_helplogin" is probably useful. This SSP, also built on top of "sp_rv_exec", displays how a specific login will access each database in the server, i.e. to which database user this login corresponds in every database. Furthermore, it shows whether this login still owns any objects in every database. From this output, it becomes clear immediately where "jbrown" still had some objects, datatypes or aliases left, so the DBA doesn’t have to waste time on working this out.
To display the database users for "jbrown", the following command should be used:
exec sp_rv_helplogin "jbrown"
The main virtue of "sp_rv_helplogin" is that, traditional SSPs, it shows a complete picture of how (i.e. as which database user) a login will access every database.
This message is intended only for the stated addressee(s) and may be confidential. Access to this email by anyone else is unauthorised. Any opinions expressed in this email do not necessarily reflect the opinions of Fidessa. Any unauthorised disclosure, use or dissemination, either whole or in part is prohibited. If you are not the intended recipient of this message, please notify the sender immediately.
Fidessa plc - Registered office:
Dukes Court, Duke Street, Woking, Surrey, GU21 5BH, United Kingdom
Registered in England no. 3781700 VAT registration no. 688 9008 78
Fidessa group plc - Registered Office:
Dukes Court, Duke Street, Woking, Surrey, GU21 5BH, United Kingdom
Registered in England no. 3234176 VAT registration no. 688 9008 78
