This morning I was migrating Microsoft SQL databases from an old server to a new server. After migrating the database, I needed to delete an existing login user from the database and add a new one. When I tried to delete the user I got the following error thrown at me.

I opened the Database Roles node under Security and found a long list of roles. Following image shows partial list of those roles.

I remember that user login had db_owner role on old server. So I click on that role and found the user name there. I deleted the user from that role. I tried to delete the user, again the same error. This means we need a way to list all the roles that are owned by this user. Lets see some underlying system tables where this information is available.

sys.database_principals Database Table

You will find all the principals in your server in sys.database_principals table. You can do a quick Select query on this table to see what all is there. Following image shows part of the information from that table.

As you can see it lists all the roles in your server and principals associated with it. The column of interest in this table is owning_principal_id. This is the field that contains the ID of user who owns that schema. So you just need to run a query that will list all the roles in this table that has owning_principal_id same as principal_id and you have all that you need.

select dbp1.name as DbRole, dbp2.name as DbRoleOwner 
from sys.database_principals as dbp1 
inner join sys.database_principals as dbp2 
on dbp1.principal_id = dbp2.owning_principal_id 
where dbp1.name = 'dbuser'

To run this query in your database, replace dbuser with whatever user you are trying to delete. This query will give you all the roles owned by this user. Then you can go to each of those roles in Database Roles node and replace it with some universal role. Replacing it with dbo will be a safe bet. Once you have changed owner of all those roles, you can simply delete that user from security node of that database. After that you can change the role owner to new user if you need to.

Today I was debugging some issue in some application. I looked in event log on my machine and saw that log was full of Sql Server Errors as shown below.

As you can see that error message is not of much help. It tells me that SQL Server login is failing but it does not tell me what application is causing it and more important what database is failing. At that point I turned to SQL Server that was in question. I brought up SQL Profiler. By default the profiler does not show failed login entries. So before you launch sql profiler instance, make sure you configure it to include Audit: Login Failed. Another important thing you will need to configure is the columns that you want to see in the profiler. By default name of the database or database ID is not configured to be included in the report. So you will have to enable that as well in profiler configuration. Following image shows how I configured it on my machine.

After running the profiler, I could see that login was failing for MASTER database. And the application name was showing up as .Net Sql Data Provider. At this point I had following pieces of information.

• Login is failing every one minute
• Login is failing on master database
• Login is failing because of access by .Net framework

Based on this information I could deduce that is some windows or web service that is pinging MASTER database to get some metabase information. Considering it was related to .Net framework, suspect was some Microsoft service or component. After looking at the databases in server, I saw that I have TFS installed on same server. When I went to TFS configuration console the error was right there that the whole service was failing with SQL errors. After I fixed the TFS configuration, these errors went away.

The point I am trying to stress in this post is that if you run into this error, quickly launch SQL Profiler and find out whata database is in question and what application is causing it. And you will have solution to the problem quickly.

Some times when you execute a SQL query to insert or update data into a table, you may see the following error.

String or binary data would be truncated.
The statement has been terminated. 
Description: An unhandled exception occurred during the execution of the current web request. 
Please review the stack trace for more information about the error and where it 
originated in the code. 
Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

What this error means is that you are trying to insert more data into a field than the schema specified for. For example if you have a varchar field and limited its size to 100, if you try to insert a string with length more than 100, then you will end up with this exception. In older version of SQL, the server used to silently let this case go through and truncate the data and you would not know it. And then you will notice it when trying to display these fields in some form.

I have been doing lot of database development for some years. Some time I got the design right and some time I made a mess that I had to clean up. Some time I will miss some requirements and some time I will get the normalization wrong. Like any other development process I went through iterations to finally get to the point where I am comfortable with database design.

Recently I got tasked with database rearchitecture of a large scale system. Since I had some time on hand (for a change) I decided to read up on some literature and books on database design and best practices. I asked a good friend of mine who is an excellent DB engineer about recommened books. He recommended only two book. One was by Joe Celko. After readin abstract and some reviews on the book, i decided that this book is not something I want to pick up. It is too abstract and theoraticak. Second book he recommended was Pro SQL Server 2008 Relational Database Design and Implementation. It definitely looked like something I may enjoy reading.

This book is definitely a very good resource on database design and architecture. It is just the right kind of book, not too abstract not to targtted towards just showing you sample scripts and things like that. Book starts you with basic concepts on database design and build from there. First four chapters are definitely worth reading if you are just starting in world of database design. I really liked how in Chapter 3 he goes through step by step process of requirement gathering and shaping the design and making chnages as you go along. I will definitely recommend this book to any one who wants to grasp some concepts about what is database design, process to follow and how to implement it effectively.

While creating a new web application, as part of user management feature I was added bunch of tables and some stored procedures. When I executed my stored procedure sp_adduser I kept getting the error Procedure or function sp_adduser has too many arguments. This drove me nuts. I checked every single line of code, looked at execution of stored procedure in profiler. Everything looked good. I have 4 parameters defined for my stored procedure and one is of type OUTPUT. Then I thought since I am using SQL Server 2008, there may be something different about how OUTPUT parameters are handled. I could not find anything different about it in SQL Server 2008 documentation. Then I decided to create my database in SQL Server 2005 and executed my code against it. Same error from SQL Server 2005 as well. At this point I figured that there is something else going on with this stored procedure. Since SQL Server only tells you name of parameter if there is one missing. So there was no way I was going to get name of parameter that was extra from the error message. So I decided to remove OUTPUT parameter from my call. Then I get following error.

Procedure or function 'sp_adduser' expects parameter '@loginname', which was not supplied

Now this error message was interesting. My stored procedure does not have @loginname parameter defined. That hinted me that my call is colliding with some other stored procedure in database and that stored procedure's name is same as what I have created. I looked at System Stored Procedures. Thats where I found stored procedure sys.sp_adduser defined as below.

procedure [sys].[sp_adduser]
@loginame       sysname,	    -- user's login name in syslogins
@name_in_db     sysname = NULL, -- user's name to add to current db
@grpname sysname = NULL  -- role to which user should be added.

After I changed name of my stored procedure, everything worked fine. This was kind of interesting debugging exercise. And it showed me that SQL Server query engine first matches count of parameters before it checks individual stored procedure names and other related metadata. And that perfectly makes sense from optimization point of view. Anyways, when you get these kidn of weired errors, do check if there is some system stored procedure or function name that may be colliding with yours.

For the first time I tried to write a stored procedure in SQL Server 2008 query editor in SQL Management studio. I was impressed to see how complete intellisense support has been implemented in SQL Management studio. It event recoganized alias table name. And when i entered DOT after alias name, it immediately gave me name of all the columns in the table. Made my life very easy. In the past I had to keep tree view open for a given table in left side to see all the column names. Good work Microsoft team!

Finally I started using SQL Server 2008 on my Windows 7 work station. I created a new database. Created a new table to test some code. Then i decided to change schema of my table. Made a simple change and marked one field to be Identity field. Moment I hit OK, i got the following error message.

This is fresh install of SQL Server 2008 and I did not recall of enabling such option. These minor changes in SQL2005 never used to trigger such warnings. After clicking on few options menu options i figured out where this option is enabled. Goto to Tools > Options menu. It will bring up wizard for database options. Click on Designers link in left pane. It will bring up following options in right pane. Notice that Prevent saving changes that require table re-creation option is checked. You can uncheck this option to enable these table schema changes.