Trying to delete a user from a database but getting an error

• cFauvel

  SSC Enthusiast

  Points: 181

  More actions

  March 23, 2020 at 6:17 pm

  #3736387

  I had to create a new user for testing, but running the app using this user id is acting weird so wanted to start from scratch

  in order to delete the user from the db I am using the Sql Management UI and choose DELETE user, but get an error

  The database principal has granted or denied permissions to objects in the database and cannot be dropped.

   

   

  I am unable to find any secured objects to this user...

  Can someone help find these objects?

• cFauvel

  SSC Enthusiast

  Points: 181

  More actions

  March 23, 2020 at 6:41 pm

  #3736391

  found the issue...I had a couple of IMPERSONATE grants to this user as well as from this user

  found these two statements elsewhere

  select *

  from sys.database_permissions

  where grantor_principal_id = user_id ('newuser');

  SELECT

  N'REVOKE '

  + permission_name +

  N' ON '

  + QUOTENAME(SCHEMA_NAME(OBJECTPROPERTY(p.major_id, 'SchemaId')))

  + N'.'

  + QUOTENAME(OBJECT_NAME(p.major_id))

  + N' FROM '

  + QUOTENAME(USER_NAME(p.grantee_principal_id))

  + N' CASCADE AS user1;'

  FROM sys.database_permissions AS p

  WHERE

  grantor_principal_id = USER_ID('newuser')

  AND OBJECTPROPERTY(p.major_id, 'SchemaId') = SCHEMA_ID('sys');