August 6, 2009 at 8:30 am
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)
------------------------------
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
------------------------------
MCSE SQL Server 2012\2014\2016
August 6, 2009 at 9:12 am
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.
August 6, 2009 at 10:29 am
I know but how do i find out which ones? 😀
MCSE SQL Server 2012\2014\2016
August 6, 2009 at 2:31 pm
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.
August 6, 2009 at 3:00 pm
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:
MCSE SQL Server 2012\2014\2016
August 6, 2009 at 3:18 pm
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.
August 6, 2009 at 5:41 pm
To list all modules where an "execute as" has been specified, you can run the below SQL.
SELECTobjects.type_desc AS ObjectTypeDescription
,objects.type AS ObjectTypeCd
,SCHEMA_NAME(objects.SCHEMA_ID) AS SchemaName
,objects.nameAS ObjectName
,USER_NAME(sql_modules.execute_as_principal_id) ExecAsUserName
,objects.SCHEMA_ID
,sql_modules.execute_as_principal_id
FROMsys.objectsAS objects
JOINsys.sql_modulesAS sql_modules
ON sql_modules.OBJECT_ID= objects.OBJECT_ID
WHEREsql_modules.execute_as_principal_id IS NOT NULL -- Exec As is not "owner"
ANDsql_modules.execute_as_principal_id
COALESCE(objects.principal_id , objects.SCHEMA_ID )
SQL = Scarcely Qualifies as a Language
August 10, 2009 at 11:35 am
Nothing shows up. There has to be a way for force a drop user.
thanks gor you help.....
Leisha
MCSE SQL Server 2012\2014\2016
August 11, 2009 at 9:59 am
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 + '%'
August 12, 2009 at 2:17 pm
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
MCSE SQL Server 2012\2014\2016
July 5, 2014 at 8:32 pm
Hi Journeyman,
That is an excellent script and it will be useful for a number of things. Thanks for posting that. I wonder if you see this response, do you know how I can revoke the "with execute as ..." on an SP I have tried the revoke execute on object..." but that does not work. Or do I simply have to drop and re-create the SP, is that the only way.
Thanks again for the post
Derek
October 21, 2026 at 2:50 pm
Excellent script! It found the Stored Procedure referencing the user name I was trying to drop. However, the module had the user name in the comments, with directions to use EXECUTE AS during the initial run of the procedure.
My issue ended up being traced to an EXECUTE grant to a SCHEMA. There was also an UPDATE grant that likely would have caused a problem later.
My goal was to de-orphan Users on databases after a migration. Re-joining them to existing logins, or drop them if no matching login existed.
I found a post: How to Script Database Permissions which showed me how to use sys.database_permissions to detect Users with object privileges, and skip those users during the process.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply