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 12»»

Drop failed for user Expand / Collapse
Author
Message
Posted Thursday, August 6, 2009 8:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:03 PM
Points: 299, Visits: 593
How do I find out what it is connected to so I can drop it? Any help would be great!

See error message below when ever I try to delete it;


TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for User 'username'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped. (Microsoft SQL Server, Error: 15136)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15136&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


MCSA SQL Server 2012
Post #766249
Posted Thursday, August 6, 2009 9:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 1,143, Visits: 991
it sounds like you may have an execute as username in a stored procedure or function, and that is why it will not allow you to drop the user.
Post #766298
Posted Thursday, August 6, 2009 10:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:03 PM
Points: 299, Visits: 593
I know but how do i find out which ones?

MCSA SQL Server 2012
Post #766378
Posted Thursday, August 6, 2009 2:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 1,143, Visits: 991
I use a free utility called SQLDigger that allows you to search sprocs for a string. You can do a search for the username and it should find any sprocs that use that username.
Post #766548
Posted Thursday, August 6, 2009 3:00 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:03 PM
Points: 299, Visits: 593
I can't use the version because I am already on .net 3.5.

any other suggestions, please I really need help on deleting the user!


MCSA SQL Server 2012
Post #766568
Posted Thursday, August 6, 2009 3:18 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:48 AM
Points: 1,143, Visits: 991
I have .net 3.5 + sp1 and SQLDigger works fine, although it's possible that I installed it before I had that framework installed. I just confirmed that SQLdigger is functioning OK though. May be worth a try. You could also try searching syscomments for the user, but if you do that, some sprocs will span multiple records in that view so the username could be split in 2 if that makes sense, but it's worth a try as well.
Post #766586
Posted Thursday, August 6, 2009 5:41 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,285, Visits: 4,225
To list all modules where an "execute as" has been specified, you can run the below SQL.
SELECT	objects.type_desc 			AS ObjectTypeDescription
, objects.type AS ObjectTypeCd
, SCHEMA_NAME(objects.SCHEMA_ID) AS SchemaName
, objects.name AS ObjectName
, USER_NAME(sql_modules.execute_as_principal_id) ExecAsUserName
, objects.SCHEMA_ID
, sql_modules.execute_as_principal_id
FROM sys.objects AS objects
JOIN sys.sql_modules AS sql_modules
ON sql_modules.OBJECT_ID = objects.OBJECT_ID
WHERE sql_modules.execute_as_principal_id IS NOT NULL -- Exec As is not "owner"
AND sql_modules.execute_as_principal_id
<> COALESCE(objects.principal_id , objects.SCHEMA_ID )



SQL = Scarcely Qualifies as a Language
Post #766633
Posted Monday, August 10, 2009 11:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:03 PM
Points: 299, Visits: 593
Nothing shows up. There has to be a way for force a drop user.

thanks gor you help.....
Leisha


MCSA SQL Server 2012
Post #768024
Posted Tuesday, August 11, 2009 9:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:11 PM
Points: 96, Visits: 146
Try this script to find a term in just about any SQL object.

DECLARE  @searchFor VARCHAR(MAX) 

SET @searchFor = 'search term'

SELECT OBJECT.object_id,
OBJECT.name [object_name],
schemas.name [schema_name],
stored_procedures.type_desc [object_description],
parameters_types.name [parameter_data_type],
CASE
WHEN Len(parameters.name) = 0
THEN '(no name)'
ELSE parameters.name
END [parameter_name],
parameters.is_output [parameter_is_output],
parameters.has_default_value [parameter_has_default_value],
parameters.default_value [parameter_default_value],
CASE
WHEN parameters.max_length = -1
THEN parameters_types.max_length
ELSE parameters.max_length
END [parameter_max_length],
parameters.PRECISION [parameter_precision],
parameters.scale [parameter_scale],
CASE
WHEN parameters.parameter_id = 0
THEN 1
ELSE 0
END [parameter_is_return_value],
CASE
WHEN sql_modules.definition IS NULL
THEN 'encrypted'
ELSE sql_modules.definition
END [definition]
FROM sys.objects OBJECT
INNER JOIN sys.schemas schemas
ON schemas.schema_id = OBJECT.schema_id
LEFT OUTER JOIN sys.objects stored_procedures
ON stored_procedures.object_id = OBJECT.object_id
AND stored_procedures.TYPE = 'P'
LEFT OUTER JOIN sys.objects aggregate_functions
ON aggregate_functions.object_id = OBJECT.object_id
AND aggregate_functions.TYPE = 'AF'
LEFT OUTER JOIN sys.objects check_constraints
ON check_constraints.object_id = OBJECT.object_id
AND check_constraints.TYPE = 'C'
LEFT OUTER JOIN sys.objects default_constraints
ON default_constraints.object_id = OBJECT.object_id
AND default_constraints.TYPE = 'D'
LEFT OUTER JOIN sys.objects foreign_key_constraints
ON foreign_key_constraints.object_id = OBJECT.object_id
AND foreign_key_constraints.TYPE = 'F'
LEFT OUTER JOIN sys.objects primary_key_constraints
ON primary_key_constraints.object_id = OBJECT.object_id
AND primary_key_constraints.TYPE = 'PK'
LEFT OUTER JOIN sys.objects assembly_stored_procedures
ON assembly_stored_procedures.object_id = OBJECT.object_id
AND assembly_stored_procedures.TYPE = 'PC'
LEFT OUTER JOIN sys.objects scalar_functions
ON scalar_functions.object_id = OBJECT.object_id
AND scalar_functions.TYPE = 'FN'
LEFT OUTER JOIN sys.objects assembly_scalar_functions
ON assembly_scalar_functions.object_id = OBJECT.object_id
AND assembly_scalar_functions.TYPE = 'FS'
LEFT OUTER JOIN sys.objects assembly_table_functions
ON assembly_table_functions.object_id = OBJECT.object_id
AND assembly_table_functions.TYPE = 'FT'
LEFT OUTER JOIN sys.objects rules
ON rules.object_id = OBJECT.object_id
AND rules.TYPE = 'R'
LEFT OUTER JOIN sys.objects replication_filter_procedures
ON replication_filter_procedures.object_id = OBJECT.object_id
AND replication_filter_procedures.TYPE = 'RF'
LEFT OUTER JOIN sys.objects system_base_tables
ON system_base_tables.object_id = OBJECT.object_id
AND system_base_tables.TYPE = 'S'
LEFT OUTER JOIN sys.objects synonyms
ON synonyms.object_id = OBJECT.object_id
AND synonyms.TYPE = 'SN'
LEFT OUTER JOIN sys.objects service_queues
ON service_queues.object_id = OBJECT.object_id
AND service_queues.TYPE = 'SQ'
LEFT OUTER JOIN sys.objects assembly_dml_triggers
ON assembly_dml_triggers.object_id = OBJECT.object_id
AND assembly_dml_triggers.TYPE = 'TA'
LEFT OUTER JOIN sys.objects inline_table_functions
ON inline_table_functions.object_id = OBJECT.object_id
AND inline_table_functions.TYPE = 'IF'
LEFT OUTER JOIN sys.objects table_functions
ON table_functions.object_id = OBJECT.object_id
AND table_functions.TYPE = 'TF'
LEFT OUTER JOIN sys.objects tables
ON tables.object_id = OBJECT.object_id
AND tables.TYPE = 'U'
LEFT OUTER JOIN sys.objects unique_constraints
ON unique_constraints.object_id = OBJECT.object_id
AND unique_constraints.TYPE = 'UQ'
LEFT OUTER JOIN sys.objects VIEWS
ON VIEWS.object_id = OBJECT.object_id
AND VIEWS.TYPE = 'V'
LEFT OUTER JOIN sys.objects extended_stored_procedures
ON extended_stored_procedures.object_id = OBJECT.object_id
AND extended_stored_procedures.TYPE = 'X'
LEFT OUTER JOIN sys.objects internal_tables
ON internal_tables.object_id = OBJECT.object_id
AND internal_tables.TYPE = 'IT'
LEFT OUTER JOIN sys.all_parameters parameters
ON parameters.object_id = OBJECT.object_id
LEFT OUTER JOIN sys.types parameters_types
ON parameters_types.user_type_id = parameters.user_type_id
LEFT OUTER JOIN sys.all_sql_modules sql_modules
ON sql_modules.object_id = OBJECT.object_id
LEFT OUTER JOIN information_schema.routines routines
ON routines.routine_schema = schemas.name
AND routines.routine_name = OBJECT.name
LEFT OUTER JOIN information_schema.routine_columns routine_columns
ON routine_columns.table_schema = schemas.name
AND routine_columns.table_name = OBJECT.name
LEFT OUTER JOIN information_schema.view_column_usage view_columns
ON view_columns.view_schema = schemas.name
AND view_columns.view_name = VIEWS.name
WHERE sql_modules.definition LIKE '%' + @searchFor + '%'

Post #768763
Posted Wednesday, August 12, 2009 2:17 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:03 PM
Points: 299, Visits: 593
SOLUTION:

In my service Broker there where emails with Q's on them, after deleteing them I was able to drop the user.


MCSA SQL Server 2012
Post #769662
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse