0

I'm curious to know whether or not it is possible to create a table in Datamart using SQL Server Management Studio. Just a note, Power BI Datamart uses Azure SQL db. 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'.

 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

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 attempted to change my privilege I got the following error:

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

Jay2454643
  • 15
  • 4

1 Answers1

0

Could you please run the following statement on the Azure SQL database while logged in as the SQL Admin login of the logical server?

exec sp_addRoleMember 'dbmanager', 'powerbilogin'

After that powerbilogin will be able to create tables.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • I have just tried running the query, unfortunately I received the following error: `User or role 'powerbilogin' does not exist in this database.`. I ran a quick query, the users on the db are **Username**: _admin_user_ **Role**: _admin_ and **Username**: _dbo_ **Role**: _db_owner_. I currently log in as 'admin_user'. – Jay2454643 Nov 17 '22 at 14:37
  • Please change Powerbilogin by the login name you are using on PowerBI. Change it to admin_user. – Alberto Morillo Nov 17 '22 at 22:59