How can I create a table in Power BI Datamart using SSMS?

[HamidBee]

I'm curious to know whether or not it is possible to create a table in Datamart using SQL Server Management Studio. I have tried to do it using the following query:

SELECT Role = r.name, Member = m.name 
FROM sys.database_role_members as rm
INNER JOIN sys.database_principals as r
    ON r.principal_id = rm.role_principal_id
INNER JOIN sys.database_principals as m
    ON m.principal_id = rm.member_principal_id

and I get the following error:

Msg 262, Level 14, State 1, Line 1 
CREATE TABLE permission denied in database 'db_powerbiprodgbr_20221028_10054574_fb38'.

Completion time: 2022-11-17T09:26:35.4237956+00:00

Here is an image:

I checked to see the roles available using the following query:

SELECT Role = r.name, Member = m.name 
FROM sys.database_role_members as rm
INNER JOIN sys.database_principals as r
    ON r.principal_id = rm.role_principal_id
INNER JOIN sys.database_principals as m
    ON m.principal_id = rm.member_principal_id

Here is an image:

I then wanted to change my role from admin to admin_user using the following query:

ALTER AUTHORIZATION ON DATABASE::db_powerbiprodgbr_20221028_10054574_fb38 TO admin_user

When I attemped to change my priviledge I got the following error:

Msg 15247, Level 16, State 1, Line 1 
User does not have permission to perform this action.

Completion time: 2022-11-16T23:39:52.9509280+00:00

Any help woul be greatly appreciated. 

[DivAlex]

Just checking to see if there was a solution to this.
I am trying to Create tables from SSMS as well and running into the exact same issues.

Was this resolved? Were there any other alternatives that worked?

[v-xiaosun-msft]

Hi @HamidBee 

Before you create a table in SSMS, please make sure you have CREATE TABLE permission in the database. You can check it using the below T-SQL statement, 1 in the output means, you have the permission to CREATE TABLE in the DemoDB database.

SELECT HAS_PERMS_BY_NAME ('DemoDB', 'DATABASE', 'CREATE TABLE');

And you can click the link below and search for GRANT CREATE TABLE To, in order to grant this permission.

Giving and removing permissions in SQL Server (mssqltips.com)

How to create a table using SQL Server Management Studio (mssqltips.com)

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[peteroest1978]

The answer I have gotten is that admin_user doesn't have the same rights as db_owner and is not allowed to be granted to create tables. But although a viewer that has the role public can be assigned the role db_owner I was not able to the last action because I did not have sufficient right to do this. So I am hoping someone replies an answer to this in the following link:

https://community.powerbi.com/t5/Service/Datamart-SQL-database-capabilities/m-p/2541428

[HamidBee]

Hi @v-xiaosun-msft,. I tried running the query:

SELECT HAS_PERMS_BY_NAME ('DemoDB', 'DATABASE', 'CREATE TABLE');

  I recieved the following:

I guess '0' means that I do not have permission so I tried to grant permission by using this query:

GRANT CREATE TABLE TO admin_user;

but I recieved the following:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

One thing I did notice is that the tables appear under 'System Views':

I'm really not sure what to do. Any help would be greatly appreciated.