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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply