Recently I found a bug in SQL Server Management Studio 2014. Database user permissions are not granted when creating a new login.
We want to add a new login and giving this account, let’s say dbowner permission.
In SSMS we could do this in one wizard:
So I have the account „NewDeveloper“ and I add this active directory account from my testdom-domain to my SQL Server and also granting the database user permission.
After clicking „ok“, the account has a login and a user in the database, but the dbowner permission is not there.
I checked the script that is produced by the wizard:
CREATE LOGIN [TESTDOM\NewDeveloper]
The DbOwner-permission part is missing in the script
It’s always the same no matter whether I click the dbowner permission or not.
Clicking again on the login and granting the permission again, it is working and the generated delta script is also correct.
ALTER ROLE [db_owner]
ADD MEMBER [TESTDOM\NewDeveloper]
So if using the GUI to manage your SQL Server, please check twice, if all granted permissions are really there.