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.

Microsoft this week unveiled its newest version of SQL server code named DENALI most probably will be known as SQL server 2011. I have had a bit of play around with the new version and found some of the new features being added to the Database engine. The first coolest thing that I looked at was NEW server roles. Starting from Denali you can create user-defined server roles and add server-level permissions to the user-defined server roles.

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 dont 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 DBAs will be limited to the permission so she 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.

I have seen some questions in forums asking for the answer is it possible to block connections to SQL Server based on IP address. As far as now there is no official way in SQL Server to block the connections in SQL Server based on IP address. However this can be done from the OS end, we have the following three options available, refer HERE for more.

In my previous articles on TDE Ive gone through whats TDE and how to configure it. Now here in this article Im going to test whether Im 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. Ill also brief about how to copy move the database that is encrypted with TDE.