Bug in SQL Server Management Studio Adding Logins and Permissions
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:
- USE [master]
- CREATE LOGIN [TESTDOM\NewDeveloper]
- FROM WINDOWS
- WITH DEFAULT_DATABASE=[master]
- USE [AdventureWorks2014]
- USER [TESTDOM\NewDeveloper]
- FOR LOGIN
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.