November 13, 2008 at 9:48 am
trying to get rid of sa account from all instances but before that how cud i check where this sa account is used on the server and who is using.
November 13, 2008 at 10:04 am
Change the password, if anyone is using it (they shouldn't be) then I am sure they will make themselves known fairly quickly
November 13, 2008 at 11:27 am
steveb (11/13/2008)
Change the password, if anyone is using it (they shouldn't be) then I am sure they will make themselves known fairly quickly
Not a good idea in a production environment. It would be good to make sure no production applications are using it to connect. If they are, even though they shouldn't be, you will be responsible for stopping production and it may not go over well with the bosses.
If you have a tool like Idera's Compliance Manager you can get a history of all login data. Or you can set up your own trace. Or you can run a scheduled job against sysprocesses to output to a table and capture the login info (not as foolproof).
November 13, 2008 at 11:42 am
On SQL Server 2005 SP2 you can setup a logon trigger and audit for an sa login via that means.
K. Brian Kelley
@kbriankelley
November 14, 2008 at 7:32 am
How can i script out all logins / DB's associated with SA login on my server.
November 14, 2008 at 8:00 am
If you mean who is a member of the sysadmin fixed server role,
EXEC sp_helpsrvrolemember 'sysadmin';
GO
With respect to finding databases owned by SA:
SELECT name
FROM sys.databases
WHERE owner_sid = SUSER_SID('sa')
K. Brian Kelley
@kbriankelley
November 17, 2008 at 1:51 pm
thanks.
that will script all server roles but how can i script logins associated to a user created DB role.
November 17, 2008 at 4:36 pm
EXEC sp_helprolemember 'role name';
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply