June 30, 2009 at 2:18 pm
Initial problem - attempting to drop a database user for a SQL login that is no longer valid. When the statement DROP USER is executed an error is generated stating the user cannot be dropped because it has granted permissions.
So, after researching this, there appears to be several issues nested within another (3 to be exact). The root cause is we had someone use this SQL Server login execute the following statements (shorted for security and brevity):
CREATE MASTER KEY ENCRYPTION BY PASSWORD...;
CREATE CERTIFICATE [name] WITH SUBJECT...;
CREATE SYMMETRIC KEY [name] ... ENCRYPTION BY CERTIFICATE
GRANT VIEW DEFINITION ON SYMMETRIC KEY::[name] TO PUBLIC;
GRANT CONTROL ON CERTIFICATE::[name] TO PUBLIC;
As it turns out, this introduced several issues. First the due to the GRANT statements being executed under this user account, the original error was generated. I resolved that by issuing corresponding REVOKE statements and reissuing the GRANTs under an admin service account.
Now, DROP USER still failed with a second and third error, both related to the CREATE CERTIFICATE and then the CREATE SYMMETRIC KEY statements (these error messages are my best of recollection):
- The database principal owns a certificate in the database, and cannot be dropped.
- The database principal owns a symmetric key in the database, and cannot be dropped.
Now, to resolve all of these I performed the following:
USE [mydatabase];
GO
//This addressed the database user being the principal of the key.
ALTER AUTHORIZATION ON SYMMETRIC KEY::[name] TO [dbo];
//This addressed the database user being the principal of the certificate.
ALTER AUTHORIZATION ON 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::[name] TO PUBLIC;
//This addressed the database user being the principal that granted the permissions on the certificate originally.
REVOKE CONTROL ON CERTIFICATE::[name] TO PUBLIC;
GO
//This simply reissued the permissions under an admin account.
GRANT VIEW DEFINITION ON SYMMETRIC KEY::[name] TO PUBLIC;
GRANT CONTROL ON CERTIFICATE::[name] TO PUBLIC;
GO
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 statement without an error. My only question remaining is, is transferring the ownership to the [dbo] database user acceptable? What is the best practice?
July 2, 2009 at 2:08 pm
No encryption / permission gurus out there?
July 7, 2009 at 10:18 am
FYI
These changes have been running for several days on 3 of our environments without any problems. I think this solved the issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply