SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drop failed for user


Drop failed for user

Author
Message
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 919
How do I find out what it is connected to so I can drop it? Any help would be great! Crazy

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
Adam Angelini
Adam Angelini
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1177
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.
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 919
I know but how do i find out which ones? :-D

MCSA SQL Server 2012
Adam Angelini
Adam Angelini
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1177
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.
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 919
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!Crying

MCSA SQL Server 2012
Adam Angelini
Adam Angelini
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1177
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.
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3380 Visits: 4350
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
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 919
Nothing shows up. There has to be a way for force a drop user.

thanks gor you help.....
Leisha

MCSA SQL Server 2012
DavidH-768896
DavidH-768896
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 150
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 + '%'


lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 919
SOLUTION:

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

MCSA SQL Server 2012
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search