June 24, 2022 at 10:38 am
I have a user 'Dev_User' in a new dev server which I am trying to drop and recreate. I generated a script using sp_help_revlogin proc from a an old dev server since SID doesn't match even though the password matches. This user has admin rights on the server and there are some applications which connect to SQL Server using this login. I have revoked sa rights, I have killed sessions, I have changed ownership to sa yet when I drop the user, I get one or 2 messages. 'User can't be dropped because it is currently logged in or user can't be dropped because it owns one or 2 DBs. Not sure what I am missing? Any help is highly appreciated.
June 24, 2022 at 10:52 am
Use something like this to generate the statements for changing DB ownership to sa
USE master;
GO
SELECT name
,SQL = CONCAT('ALTER AUTHORIZATION ON DATABASE::', name, ' to sa;')
FROM sys.databases
WHERE SUSER_SNAME(owner_sid) <> 'sa';
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 24, 2022 at 12:35 pm
I have a user 'Dev_User' in a new dev server which I am trying to drop and recreate. I generated a script using sp_help_revlogin proc from a an old dev server since SID doesn't match even though the password matches. This user has admin rights on the server and there are some applications which connect to SQL Server using this login. I have revoked sa rights, I have killed sessions, I have changed ownership to sa yet when I drop the user, I get one or 2 messages. 'User can't be dropped because it is currently logged in or user can't be dropped because it owns one or 2 DBs. Not sure what I am missing? Any help is highly appreciated.
The message tells you the issue, and exactly what you need to look for.
Phil's code will generate code that will change the owner of the database to 'sa'. That will fix one of the possible errors. Once that is corrected, if you still cannot drop the user, then this user has a connection to the system.
This code will find the connections for you.
DROP TABLE #TmpSP
GO
CREATE TABLE #TmpSP
(
SPID nvarchar(1000),
Status nvarchar(1000),
Login nvarchar(1000),
HostName nvarchar(1000),
BlkBy nvarchar(1000),
DBName nvarchar(1000),
Command nvarchar(1000),
CPUTime nvarchar(1000),
DiskIO nvarchar(1000),
LastBatch nvarchar(1000),
ProgramName nvarchar(1000),
SPID2 nvarchar(1000),
REQUESTID nvarchar(1000)
)
INSERT INTO #TmpSP
EXEC sp_who2
SELECT
*
FROM #TmpSP
WHERE Login <> 'The login you are trying to drop'
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply