In SQL Server 2000, I think you'll have to drop and re-create the user, unless you want to reply on crazy hacks like this one:
I don't really recommend that, both because updating system catalogs is dangerous, and also because it uses undocumented, unsupported, and buggy sp_MSForEachDB
(see why I urge you to stay away from that one).
In versions newer than SQL Server 2000, it is as simple as:
ALTER USER foo WITH NAME = bar;
You don't have to perform any task (destructive or non-destructive) at all.
You can use the built-in function IS_SRVROLEMEMBER
to find if a certain LOGIN
is a memeber of the sysadmin
server role:
SELECT IS_SRVROLEMEMBER('sysadmin','<LoginName>');
Note that for roles other than sysadmin
the result will be positive (=1) for implicit membership as well.
You can use the built-in stored procedure sp_helpsrvrolemember
to get a list of all the explicit members of a certain role:
EXEC sys.sp_helpsrvrolemember @srvrolename = 'sysadmin';
Or a complete list of all explicit members of all server roles:
EXEC sys.sp_helpsrvrolemember;
For SQL Server 2000 there is also the option to sort or filter the result set using:
CREATE TABLE #ServerRoles
(
ServerRole VARCHAR(20),
MemberName sysname,
sid VARBINARY(85)
);
INSERT INTO #ServerRoles
(ServerRole, MemberName, sid)
EXEC sys.sp_helpsrvrolemember;
SELECT SR.MemberName, SR.ServerRole
FROM #ServerRoles SR
WHERE 1 = 1 -- Change this to any filter you want
ORDER BY SR.MemberName, SR.ServerRole; -- Change this to any sorting you want
DROP TABLE #ServerRoles;
GO
For SQL Server 2005+ there is already a system view to be queried directly:
sys.server_role_members which is a many-to-many relationship table between roles
and logins
.
Best Answer
If there are faults found since it dropped out of the extended support period then unless you have paid Microsoft for extended extended support you can be pretty sure there is not a fix available to you.
Obviously you can mitigate the risk considerably by following standard practise and making sure the only machines that can touch your SQL instance are a select few application servers within your firewall(s) and making sure all of them are fully patched up & so forth.