Tag Archives: Security

What happens when permission is revoked in middle of work?

This is one of the question asked by my friend and it seems very simple. I had a discussion with him and said that whatever the transaction it has started it will succeeded, however what I told him is not correct. I told him I’ll test it out and the answer which I gave it to him is partially correct. If the transaction gets completed with in begin tran (before commit or rollback) you can close the transaction even if the permission is revoked however if the transaction is not completed within begin tran then it will fail stating that the user don’t have access. Read more »

VN:F [1.9.13_1145]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

Use xp_logininfo to retrieve AD group members list

I was discussing with one of my colleague and during the discussion he told me that he is not able to view the list of members available in a AD group since he don’t have permission on AD forest. Normally to retrieve this he will sent the request to the AD team and they will be verifying or sending him the list. I informed him that we have an alternate (happy news to him) to use xp_logininfo SQL extended stored procedure to retrieve the list, he has used it and got the desired result. I then thought of putting it in blog since in most of the big companies DBA’s will be limited to the permission so s\he need to check with the respective team to retrieve the settings. This actually needed when an AD group is added to SQL with necessary permission and when a particular user belongs to that group is not able to access SQL, you need to cross verify the AD group members list, so at that time you can utilize this procedure. Read more »

VN:F [1.9.13_1145]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.13_1145]
Rating: +1 (from 1 vote)

Change \ Transfer Schema for all Objects

In my working environment we will be using different schema for production and development environments. Our development apps are configured to Dev schema and Production apps are configured to Prod schema, so when we restore the db from production to development environment, all our dev apps stopped working since the objects \ securable will be using prod schema. Here I need to change the schema to dev from prod, so the syntax will be like below

Read more »

VN:F [1.9.13_1145]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.13_1145]
Rating: +1 (from 1 vote)

How to Copy \ Move a Database that is encrypted with TDE

In my previous articles on TDE I’ve gone through what’s TDE and how to configure it. Now here in this article I’m going to test whether I’m able to restore \ attach the database without encryption key since the main property of TDE is not to allow to use the physical files of the database without certificate. I’ll also brief about how to copy \ move the database that is encrypted with TDE.

Testing TDE without Certificate in the Instance

In the previous article I’ve encrypted the database TDE_Testing, lets try to use the physical files of this database with different methods to restore \ attach in another instance which doesn’t have this certificate and lets check whether SQL Server 2008 detects this.

Read more »

VN:F [1.9.13_1145]
Rating: 5.0/5 (16 votes cast)
VN:F [1.9.13_1145]
Rating: +14 (from 14 votes)

Implementing \ Configuring Transparent Data Encryption (TDE)

In my previous article we have discussed what is TDE, how it works, its pros and cons. In this article I’m going to cover how to implement transparent data encryption to your server. It’s very simple to implement TDE, below are the steps that needs to be completed to enable TDE.

  • Create a master key
  • Create or obtain a certificate protected by the master key
  • Create a database encryption key and protect it by the certificate
  • Set the database to use encryption

Read more »

VN:F [1.9.13_1145]
Rating: 5.0/5 (7 votes cast)
VN:F [1.9.13_1145]
Rating: +5 (from 5 votes)
  • Page 1 of 2
  • 1
  • 2
  • >