Wednesday, December 31, 2008

Default user of login mapped to role SQL Server Administrator

The post I m writing is slightly confusing. If a SQL server login is assigned to System Administrator Server role of SQL Server 2000, then it will have rights to perform any activity in SQL Server Installation. That is what, SQL Server says. This may be problematic some times.

 

Now, Lets say there is a login stcsa with mapped to role System Administrator. First, you login to ISQLW with login stcsa and create a table with name tbl_test as below.

 

create table tbl_test (t varchar(10))

 

Now, check the owner of tbl_test, it will be dbo not stcsa. Suprising Not? This is because stcsa is System Adminitrator. But, you can call the table as simply tbl_test without "dbo." in front of the object name. The following select query works.

 

select    * from            tbl_test

 

Now, lets say, you again created a table with same name but like below.

 

create table stcsa.tbl_test ( t varchar(200))

 

Then, there will be two tables with name tbl_test and owners as dbo and stcsa. Now, if you use simply tbl_test, which one object do you think will it call? No not, it will call dbo.tbl_test not stcsa.tbl_test despite you are logged in as stcsa. This is where problem comes. In normal case, if you are logged in with a sql login and that login is mapped with user of same name, then if you call sql objects without prefix username, then it will search for username.objectname.

 

I am not sure that whether this can be called a bug in SQL Server or not. I just want to specify that, it's always useful to call sql objects by using username.objectname. It becomes more useful, when the login used is mapped to the role like SQL Server Administrator.

 

2 comments:

Unknown January 7, 2009 at 2:59 AM  

The whole thing is that, login mapped to SQL Server Administrator has create permission with username dbo. So, it takes that username by default when username is not explicitly specified. For those who are not mapped to SQL Server Administrator, as they donot have create permission with username dbo, SQL Server will understand them as their username.objectname even if only objectname is given.