Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot delete a SQL Server login Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 7:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:46 PM
Points: 4, Visits: 17
I've got the following error when I try to delete a login using MS SQL Server Management Studio:

Drop failed for Login 'test'. (Microsoft.SqlServer.Smo)

Additional information:

Cannot drop the user 'test', because it does not exist or you do not have permission.

I've checked there's no session with this login, no SQL jobs or objects are owned by this login. Can anyone help with this? Thanks.
Post #1450861
Posted Wednesday, May 8, 2013 10:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
Where is the login located, within the database or within SQL Server? I've seen where a user has permissions on one server, the database is backed up and moved to another server, and that login still exists with rights for the database, even though they don't actually exist on that server.

Second question: which user are you logged in under when trying to delete the test user?


--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1450880
Posted Wednesday, May 8, 2013 11:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:46 PM
Points: 4, Visits: 17
THe login is within SQL Server, I logged in under a user which belongs to sysadam fixed server role, I expect I can remove any login if I belong to sysadmin role.


Post #1450885
Posted Wednesday, May 8, 2013 11:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
Does the test user have any securables? If so, those need to be removed first before the user can be removed.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1450890
Posted Wednesday, May 8, 2013 11:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:46 PM
Points: 4, Visits: 17
do you mean if the test login owns any objects? I used the following query to check if the login 'test' owns any object, the result is it doesn't own any objects.

SELECT
so.name AS 'ObjectName'
,su.name AS 'OwnerName'
FROM sysobjects so
JOIN
sysusers su
ON so.uid = su.uid
WHERE xtype <> 's'
ORDER BY 2
Post #1450891
Posted Thursday, May 9, 2013 12:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,890, Visits: 2,329
Try this--

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

DROP LOGIN [Test2]



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1450899
Posted Thursday, May 9, 2013 1:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:32 AM
Points: 34, Visits: 70
Hi eddy,

Please Use sp_msloginmappings and check test user detail and then delete from that database.

or post details for test user.
Post #1450901
Posted Thursday, May 9, 2013 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:46 PM
Points: 4, Visits: 17
With the Login 'test', DBName=null, UserName=null, AliasName=null

One more question, is the deletion of the sql server login managed by Windows Domain security as well? is it possible the delete permission is disabled by Windows Domain Security? Thanks.
Post #1450913
Posted Thursday, May 9, 2013 3:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:32 AM
Points: 34, Visits: 70
hi eddy,
i don't think that Windows Domain security restrict to drop sql server user.

Its a dumb thought but can u please refresh server and see that user is exist or not.
Post #1450957
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse