Stefan Grigat

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]
GO
CREATE
LOGIN [TESTDOM\NewDeveloper]

FROM WINDOWS
WITH DEFAULT_DATABASE=[master]
GO
USE
[AdventureWorks2014]
GO
CREATE

USER [TESTDOM\NewDeveloper]

FOR LOGIN
[TESTDOM\NewDeveloper]
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.

USE
[AdventureWorks2014]
GO
ALTER
ROLE [db_owner]

ADD MEMBER [TESTDOM\NewDeveloper]
GO

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