0

I had created a USER, named Sandra and I granted her a SELECT privilege to a TABLE with GRANT OPTION.

I had created another USER, named Jim.

Sandra then granted her SELECT privilege to Jim.

When I was trying to DROP USER Sandra, an error message came out saying:

Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped.

I'm wondering is there a CASCADE OPTION in DROP USER in SQL Server 2005 databases?

Thanks in advance :)

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
Willy Lazuardi
  • 1,806
  • 4
  • 26
  • 41

1 Answers1

0

no there is no such option. you have to wrote code for that to find and drop ownership owned by this user.

check the bwlow link.

MSDN BOL "Drop User"

"Users that own securables cannot be dropped from the database. Before dropping a database user that owns securables, you must first drop or transfer ownership of those securables"

Anup Shah
  • 1,256
  • 10
  • 15
  • _you must first drop or transfer ownership of those securables_ - **How this can be done?** – Boogier Jan 21 '14 at 06:35
  • I believe you meant to ask, how can we do that in code/script? right? yes it is neither efficient nor easy to wrote such code more generically because how would you deiced if specific ownership need to be dropped or just transfer? this is cases by case user deiced by looking at an ownership only. for example if the user is owner of DB or has owner ship of one or more schema in DB. bottom line either do it case by case manually or come up with case specific custom scripts depending on your need. – Anup Shah Jan 21 '14 at 18:42
  • In my case the user is grantor and he granted permission on object to other user. And I do not know how to revoke this permission or transfer it in order to drop the user. Pleas look at my question here: http://stackoverflow.com/questions/21251144/cannot-revoke-permission-granted-by-other-user – Boogier Jan 22 '14 at 06:07
  • I see your context now. i have posted the answer to your post as it is not 100% appropriate to this post. – Anup Shah Jan 22 '14 at 19:35