Eduardo Olivera (1/28/2013)

---

We will soon be converting our existing production databases to a a system where we use certificates on the actual data holding databases and procedures on a sp...

kpwimberger (1/25/2013)

---

Success!

Thanks to you both for your input. Giving the database role CONTROL permission on the cert, and REFERENCE permission on the key, worked perfectly. We are good to go.

But...

According to what I found on the internet and tests, the lowest permission is CONTROL on a certificate and only REFERENCES on a symetric key. Then user can't drop or...

If the DB is in full recovery mode, then you could try use software that analyze transaction log eg. http://www.red-gate.com/products/dba/sql-log-rescue/

Ed Wagner (1/21/2013)

---

Just as a word of caution here, if you put your DECRYPTBYKEY and CONVERT functions to the left of the operator, they'll both have to run on every...

You can't filter on REG in this way, because it is encrypted, you can filter only on a decrypted value so:

CONVERT(nvarchar(200),DECRYPTBYKEY(REG)) like '%DY%'

kpwimberger (1/18/2013)

---

Our DBA has assigned my user account ALL permissions on the certificate, and reference permission on the symmetric key, but when I run this code:

USE AdventureWorks;

OPEN SYMMETRIC KEY TestSymmetricKey

...

Empty set add a grand total.

http://www.sqlsnippets.com/en/topic-13122.html

kpwimberger (1/17/2013)

---

...

But doesn't this mean I need the password inside EVERY proc I write that touches an encrypted column? isn't this adding insecurity into the system? What if the password...

Yes, if the DB must be online your only option is to increase size of the temdb. Then check what consume space eg:

http://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

maybe the tempdb size should be larger.

MSSQL_NOOB (1/15/2013)

---

Thank you all for the corrections.

Anyone with any comments on how do I archive the database? Since MSSQL doesn't support UNC path for MDF - getting error "the...

http://msdn.microsoft.com/en-us/library/ms189463.aspx

" PERCENT

Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer...

Use joins http://msdn.microsoft.com/en-us/library/ms189835.aspx section "C. Using joins and subqueries to data in one table to delete rows in another table"

delete from othertable

from othertable o join user u on u.uid=o.uid...

I'm glad I could help 🙂

Yes, but database must have Case Sensitive collation.