August 14, 2012 at 1:21 am
So hey guys again....Sorry If I'm asking stupid questions, but to be honest, I don't have the slightest idea how to fix this thing. I'm having a user in my database that is based on the login in the master database. I am able to delete the user from my database but from the master not.
USE [Abosystem4_Archiv_test]
GO
/****** Object: StoredProcedure [dbo].[DeleteUser] Script Date: 08/14/2012 08:57:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[DeleteUser]
@username nvarchar(100)
-- Add the parameters for the stored procedure here
with execute as 'sv'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Insert statements for procedure here
--EXEC sp_droplogin @username
--EXEC sp_dropuser @username
EXEC ('DROP USER '+ @username)
EXEC('DROP LOGIN ' + @username)
END
When I'm executing my code...with the exec drop user everything works, but with the drop login it says that it is possibly that the user doesn't exist or I dn't have the permission. The permission is set correctly, as system admin.
To get to the point the problem is
Msg 15151, Level 16, State 1, Line 1.
Please help! I've googled everything and can't seem to get to the bottom of this.
August 14, 2012 at 3:46 am
Hi, do these apply at all?
A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped.
Cheers
August 14, 2012 at 5:43 am
I figured it out....I just removed the execute as...and that was all..
Also I changed the commands, instead of Drop login- i used sp_droplogin...it works now...
It has been such a pain in the ***..
August 14, 2012 at 6:40 am
Cool 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply