Error dropping database user...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Error dropping database user...

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-06-29 : 15:20:39

I have a user that ran the following statements:

GRANT VIEW DEFINITION ON SYMMETRIC KEY::<key name> TO PUBLIC;
GRANT CONTROL ON CERTIFICATE::<certificate name> TO PUBLIC;

Shame on him, shame on me for allowing the permissions to do this. To address this, I logged on as 'sa' and ran these statements:

REVOKE VIEW DEFINITION ON SYMMETRIC KEY::<key name> TO PUBLIC;
REVOKE CONTROL ON CERTIFICATE::<certificate name> TO PUBLIC;

Now when I attempt to drop the database user using DROP USER [<user name>] I get this error:

The database principal owns a certificate in the database, and cannot be dropped.

My question is this, what can I do to "transfer" the certificate to another use, say 'sa' or a service account?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-06-30 : 11:53:24

Shameless bump :)

Anyone have any thoughts on this?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-06-30 : 13:29:18

FYI

After mulling on this for a bit I think this is the solution (we have a symmetric key and a certificate created by a database user we are trying to drop):

USE [<mydatabase>];
GO
//This addressed the database user being the principal of the key.
ALTER AUTHORIZATION ON SYMMETRIC KEY::<key name> TO [dbo]; 
//This addressed the database user being the principal of the certificate.
ALTER AUTHORIZATION ON CERTIFICATE::<certificate name> TO [dbo];
GO
//This addressed the database user being the principal that granted the permissions on the key originally.
REVOKE VIEW DEFINITION ON SYMMETRIC KEY::<key name> TO PUBLIC;
//This addressed the database user being the principal that granted the permissions on the certificate originally.
REVOKE CONTROL ON CERTIFICATE::<certificate name> TO PUBLIC;
GO
//This simply reissued the permissions under an admin account.
GRANT VIEW DEFINITION ON SYMMETRIC KEY::<key name> TO PUBLIC;
GRANT CONTROL ON CERTIFICATE::<certificate name> TO PUBLIC;
GO

Of course in our implementation we grant the VIEW DEFINITION and CONTROL to specific accounts but for the sanity of an example this is what I tested with.

This appears to work. I ran some basic encrypt, decrypt and auto decrypt tests under a number of scenarios and they all work. And in the end, I am able to issue the DROP USER <user> statement without an error. My only question remaining is, is transferring the ownership to the [dbo] database user acceptable? What is the best practice?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-07-01 : 14:35:49

Hmmm... no encryption gurus around by chance?

prlfazal
Starting Member

1 Post

Posted - 2009-07-04 : 11:18:22

Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

I AM FACING THIS PROBLEM, COULD ANYONE HELP PLEASE ??

Fazal-e-Rabbi
Pakistan-Karachi

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-04 : 11:24:13

quote:
Originally posted by prlfazal

Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

I AM FACING THIS PROBLEM, COULD ANYONE HELP PLEASE ??

Fazal-e-Rabbi
Pakistan-Karachi

Oops, wrong thread?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources