Error while creating login

Question

Error while creating login

Joy Smith San

SSC-Insane

Points: 24877
More actions
July 22, 2015 at 3:05 am

#398780

Hi All,
SQL Server 2012 Ent. Two node cluster with SP2.
Getting the attached error while creating login.
Login gets created even though the error pops up.
Whats the fix for this ? Thanks !.
San.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

In your message you wrote that you are creating a new login but in the error message it seems that you are trying to add a user as a member to the database role db_owner. If you just created a login, then there is a good chance that you have server DDL trigger for create_login event that generates this error.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Adi Cohn-120898 (7/22/2015)
In your message you wrote that you are creating a new login but in the error message it seems that you are trying to add a user as a member to the database role db_owner. If you just created a login, then there is a good chance that you have server DDL trigger for create_login event that generates this error.
Adi

While adding login I was mapping to a user DB. Instead of the default db_owner, removed and gave only data_reader.

Then this error.

I tried creating another one without removing that db_owner and it just works fine and no error.

I installed this server recently and yesterday only we made it live. I have not created any DDL trigger as such.

Thanks.

JManuelN SSCommitted Points: 1708 More actions · Answer 3

Hi

It seems that the SSMS thought that the new login was already created and mapped to a user that was member of db_owner group. So, when you set it to db_readonly it tried to delete this user from the db_owner role and, since it did not have a proper user name, it tried to use an empty string.

Regards

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Thank you all.

Issue resolved.

It was happening only for one particular user database which was automatically created by some vendor application, and was working fine for other DBs.

Owner was not set for that particular DB, changed to SA and all working fine now.

Thanks again.