SQL Server and MS BI With a Geek Touch

Many times , it may be necessary to remove Not Null constraint used for a column in  a table. It is usually necessary to overcome unexpected integrity constraints ( rarely occurs, if designed well) against a table. We can remove the Not Null constaint by using  ALTER TABLE …. ALTER COLUMN … option.

Read more…

You might have come across the error as highlighted below.

Cannot execute as the database principal because the principal "sec_user" does not exist, this type of principal cannot be impersonated, or you do not have permission.

This problem usually occurs when You back up a database from an instance of SQL Server 2005 and  then, you restore the database to an instance of SQL Server 2005 that is installed on another computer. The reason for the error is because SQL Server cannot find a login that matches the security identifier of the impersonated user.

The easiest way to solve this is to create a login and alter the user name/ principal used in the restored database to point to this login. The login can be created using a simple statement as below

CREATE LOGIN SEC_USER  WITH PASSWORD = 'YourPassword'

You can get more info on creating logins from http://technet.microsoft.com/en-us/library/ms189751.aspx .

now alter the principal/User in the restore database using the alter user script. A sample is as given below.

Alter User Sec_User with login  = SEC_USER;

This should solve the error caused by restoring the database. The error can also be a result of lack of permissions to the user. In these cases check / modify user permissions.