Understanding REVOKE

This is one of the topics I was planning to write for a long time, but failed due to various reasons (and without valid reasons) So, this may sound too basic for some people; if so, please forgive me. I have seen much confusion over REVOKE statement. Some believe that revoke is a different word for DENY. As explaining the security model is worth a series of articles, in this post, I am going to explain only about REVOKE statement. For this, I am using AdventureWorks database. I created two logins which I am using throughout the post

CREATE LOGIN [Sam] WITH PASSWORD=N'MyPassw0rd!_', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Joe] WITH PASSWORD=N'MyPassw0rd!_', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Additionally I am adding these users to AdventureWorks. Two database roles also created for this purpose.

USE [AdventureWorks]

CREATE USER [Joe] FOR LOGIN [Joe] WITH DEFAULT_SCHEMA=[dbo]

CREATE USER [Sam] FOR LOGIN [Sam] WITH DEFAULT_SCHEMA=[dbo]

CREATE ROLE [Sales] AUTHORIZATION [dbo]

EXEC sp_addrolemember N'Sales', N'Joe'

EXEC sp_addrolemember N'Sales', N'Sam'

CREATE ROLE [Marketing] AUTHORIZATION [dbo]

EXEC sp_addrolemember N'Marketing', N'Sam'

Susantha's Presentation on Powershell

As you may know, Susantha Bathige, a fellow DBA at eCollege, gave a great presentation on Windows Powershell 2.0 at the last SQL Server User Group meeting. He had a few hick-ups when he was starting as the laptop couldn't communicate well with the projector. Gogula shared his laptop, but it refused to listen to his commands properly.

On content, presentation style and confidence level Susantha made an impression. I recommend it to all DBAs, developers and IT pro members.

For those who couldn't participate can view the sample code and presentation at here.  The pictures taken were available at here (If you are wondering what I was doing when he was presenting, I was working as his assistant during his presentation.  I hope it would have helped Susantha to concentrate on the presentation while I was controlling Gogula's laptop.)


Congratulations Susantha.  It is very difficult to say that it was your first presentation.  Keep it up.

"She" is coming. .. I am excited. Are you?

For a change I am writing some thing different this time. This is not a technical article. As you can below, I have no tags to it.

What is her name? I don't know yet. But I know she is coming and I am excited about it.

If you are wondering who she is, I can tell you this: Even though I haven't seen her, I am sure she will be in her best, sexiest appearance when she appears. You are going to wait anxiously to see her coming and working in your office. You may want to work closely with her, see her internal organs and have complete control over her. Initially your office management will be scared / hesitated to take her in, but there is a high possibility she will make her way in. When she comes she will come with much “hosanna” from some, while some others will grumble about her. Those who grumble will find fault with the time she has taken to respond to the calls they have made, the way she is working and the time she takes to deliver results, and even how her internal organs are made. Interestingly only a few will grumble about her sexy appearance as everyone knows that there is no point in talking about it.

Even if she failed to join your firm, you will try to have some quality time with her. You may use your office and after office hours to gather information about her, listen from people who know her already and even make appointments to work with her. Sometimes you will work with her but mostly you will sit and watch when she is doing the hard work for you. Even though she may work very closely with you, she will remain a mystery for some time. Gradually you will start learning some secrets about her. You may be looking for a day to say at least this: “I know many things including some secrets about her”.

I am not sure about you, but I bet many like to have illegal relationship with her, as starting a legal relationship costs a lot these days, you know. Even though her parents will try to avoid these illegal relationships and she too will resist illegal relationships some may know how to get hold of her.

Initially no one will know her actual name, and the actual date she is going to join your firm. People will keep on guessing. Many will refer her with her pet name until they are used to the actual name.

Excited? Now I share the first secret I know about her: Her pet name is Denali

I need to caution you on this: As it happen often in other cases, when time goes, you may lose the excitement and even start looking for a new one.

Now it is time to share the next secret: Denali is the code name for the next version of SQL Server, Microsoft’s flagship database management system. Most probably you will see her coming to your place by next year. So better be ready soon.

Constraints on Temp tables

Yesterday, I had an interesting conversation on temp tables:
All started with this question:  What is the best method to create constraints on temp tables? I never expected it to give me a chance to learn something new.
As you know Temp tables are created in tempdb. Local temp tables are visible only to the user, and it allows multiple users to have temp tables on their own. That means while user A has a temp table #MyFirstTable another user can have a table (at the same time) at the same name.
Generally all objects created in a database will have an entry in sys.objects. For the table, the entry will not be on the same name. SQL Server will add additional characters to create a unique name. The objects are dropped when the connection is dropped or reset.
But there is a difference when it comes to constraints. When a constraint is created, it created with the exact name specified and it will have an entry in sys.objects table. For example the following code is going to fail if multiple users are going to execute during the same period.

CREATE TABLE #MyFirstTable

(

MyFirstTable_PK int,

CONSTRAINT PK_#MyFirstTable PRIMARY KEY CLUSTERED (MyFirstTable_PK)

)

The reason for failure is nothing to do with syntax. When the first user executes it two entries into sys.objects: a row with system defined name for table and another row for name we specified for primary key. When the second user executes the code, it can add the row for table but it can’t add an entry for primary key. So it will fail for second user.