18.09.2015

Bug in SQL Server Management Studio Adding Logins and Permissions

Technical Value

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:

  1. USE [master]
  2. GO
  3. CREATE LOGIN [TESTDOM\NewDeveloper]
  4. FROM WINDOWS
  5. WITH DEFAULT_DATABASE=[master]
  6. GO
  7. USE [AdventureWorks2014]
  8. GO
  9. CREATE
  10. USER [TESTDOM\NewDeveloper]
  11. FOR LOGIN
  12. [TESTDOM\NewDeveloper]
  13. GO

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.

  1. USE
  2. [AdventureWorks2014]
  3. GO
  4. ALTER ROLE [db_owner]
  5. ADD MEMBER [TESTDOM\NewDeveloper]
  6. GO

So if using the GUI to manage your SQL Server, please check twice, if all granted permissions are really there.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden