Deploy error - user, group or role already exists in the current database

Question

Hi,


I am trying to get VSTS 2008 to deploy a database from the dbproj file. When I run the build and deploy on my local machine it works fine. However, on our build machine it fails with

Deploy error TSD151: .Net SqlClient Data Provider: Msg 15023, Level 16, State 1, Line 1 User, group, or role 'Xxx\xxxx' already exists in the current database

The reason for this is that it is adding this to the sql file generated on the build server -

CREATE USER [Xxx\xxxx] FOR LOGIN [Xxx\xxxx] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [IXxx\xxxx]

but the user gets created when the Create Database command is run. This line of code does not get generated on my local machine.

There is no obvious difference between the user profile in the database. I am an admin on my local machine, and the build user is an admin on the build machine.

Any ideas on what makes it add this line on the build machine?

Regards,
Dewang

Answer 1

I've got this exact same problem, for BUILTIN\Administrators.  I tried removing BUILTIN\Administrators from the toplevel security\logins in management studio but I'm still getting the error on deploy.  Is CREATE DATABASE automatically adding the login already?

 

Please post if you come up with an answer.

Answer 2

I was able to fix this by putting the following into Scripts\Pre-Deployment\Logins.sql

I know it's a kludge and I'd like to figure out how the login is getting in there in the first place...

 

Code Snippet

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BUILTIN\Administrators')
DROP USER [BUILTIN\Administrators]

 

 

Answer 3

That looks like a good hack to use until I can get to the bottom of this.

What I did was run just the Build target first. Then I deleted everything down from the Create database command and ran the Deploy target. I went in to Management Studio - the login was already there. So it does get added when you run a Create database.
In fact, like I've mentioned in the post - when I run the Build and Deploy on my local machine it does not even add the line in the script to create the user.

I've got a feeling it has something to do with the context in which it runs on the server but don't know exactly what!

Answer 4

Have a look at your 'model' database on your target SQL Server. Any new databases are created using the 'model' database settings. This includes Users, Roles etc. If you have a user defined in the model database, it will automatically be created on any new databases.

 

I had this problem with a user named 'sample'. When I removed the user from the 'model' database security, users, the deployment problem went away - no more need for a special drop statement hack!

 

BTW this one had me pulling out hair! This post led me in the right direction...Thanks

Answer 5

I'm glad this post nudged you in the right direction :-) You're right - I had the user in my 'model' database. Cheers!

Answer 6

Hi all,

I got the same problem but I solved it by a different way, because It's another reason to rise the same Error message;

The reason is [orphaned users] . 

sp_change_users_login 'report'

use this sql in you current database and you will see whether there is a orphaned user or not;

then Use this sql :

sp_change_users_login 'update_one', '_aa,'AA'

_aa is orphaned users accent;

AA is the accent you have created in your database server;

I found this page in PRC and I think it will help others people in PRC who got the same question but can not solve by your way;

sorry for my Poor English,have fun and enjoy in SQL Server:)

-------------------------------------------------------------------------------------------------------------------------------------------

The text blow is writen by Chinese, I think it will my more people like me in PRC.

如果你也收到了这个样的提示，可以先试试这个帖子的答案中提到的，在其他的数据库中看看是否存在这个账户，在这个帖子中，是model数据库中有同名账户导致了这个问题，所以你可以先去看看那个地方，类似的，还有其他数据库中是否也有这个账户；

如果你看了所有的数据库，但是都没有这个账户，但是你在赋给某个账户登录权限的时候报这个错误，然后你的这个数据库又是最近从别的机器上完整备份+还原过来的，那么可以考虑看看是不是遇到了【孤立账户】问题；

首先切到无法赋予权限的库，然后执行SQL 语句

sp_change_users_login 'report'

然后如果有返回值，那么说明这个库里面就是有孤立账户，解决的方法是运行下面这个SQL：

sp_change_users_login 'update_one', '_aa,'AA'

其中，_aa是孤立账户；

AA是你想要赋予权限的账户，如果没有，SQL Server会提示无效用户；

之后就应该可以了