0

I added my user to db_ddladmin role, but initially i had my own custom role with rights to create a table for that user. Either way creating a table using TSQL works, being part of both roles. Problem occurs when i try to create a table using GUI, tables -> new table, i get the Unspecified Error(MS Visual Database Tools)

However if i add that user as a db_owner , it works. I would just like to know if there is any right in particular that i might have missed which specifies whether user can create a table through GUI or not?

Note: I am using SSMS 17.4 and SQL Server 2016.User is part of a domain created login.

  • 1
    Try to see what code is sent to server by SSMS using SQL Server Profiler. If your user is able to create table using T-SQL it's NOT a permission issue. – sepupic Dec 14 '17 at 13:34
  • 1
    I create a user with pw (no login) in a partial contained database. Gave following permission. GRANT CREATE TABLE TO Billy GO GRANT ALTER ON SCHEMA::dbo TO Billy GO and was able to create table with tsql and GUI without any error. Using ssms 17.4, SQL 2017 CU2 – SqlWorldWide Dec 14 '17 at 13:52
  • @SqlWorldWide I did the same before and it worked. For this particular domain user it does not. However i have noticed that table creation through the gui works if the user is part of db_securityadmin role, as well as the ddl_admin role. which previously didnt work if he was only in ddl_admin role – user140492 Dec 14 '17 at 14:10
1

To answer my own question. After a some testing i noticed that the user needs to have Control permission on the database in order to be able to create tables using GUI. That is why db_securityadmin and db_owner worked.

Now i have tried before posted question to create a new login and user, that worked just fine. For some reason this domain user was problematic, so i explicitly granted him that permission. I dont think this happens occasionally, but if anyone encounters this issue that i have, i hope this helps. And some clarification if anyone has would be greatly appreciated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy