Creating a User Account on a database
-
Hi
Im a bit rusty with SQL and Im moving over into administration a little more now.
Ive created a user for my Adventureworks Test account
Use [AdventureworksDW]
Go
-- Create the user with Password for users that authenticate at the database
Create USER POC_AdventureworksDWPowerBI WITH PASSWORD = 'Examplepw';
--Im going to create a role because I only want to grant access against fact and dim
CREATE ROLE db_AdventureworksDW_read;
--Execute the Stored Procedure addrole member. Here the Org_powerbi user is being added to the above role
EXEC sp_addrolemember 'db_AdventureworksDW_read', 'POC_AdventureworksDWPowerBI';
--Hopefully you have dim and fact schemas and then you can only add these schemas for Power BI By granting to dim and granting to fact
GRANT SELECT ON SCHEMA :: fact TO db_AdventureworksDW_read
GRANT SELECT ON SCHEMA :: dim TO db_AdventureworksDW_read
this all seems to go OK, However when I try and log into SSMS with these details, It errors
I thought you could have Users without Logins (Server)
Is it because I'm not actually using it correctly? The hope is I can use this user ID and password for people connecting with Power BI (And I take it they must add the database for it to work)
-
Is your database a contained database? If not, you can't use a WITH PASSWORD clause in your CREATE USER statement.
John
-
I dont know. How can I tell if its a Contained Database?
The database in in Azure and is a standard SQL Database
-
For an Azure database assuming its Azure SQL Database the PaaS offering "database.windows.net" you need to set the Database to connect to as part of the connection string or options in SSMS.
Open the Options>>> button then put in the database that you need to connect to and it should work.
-
Check in SSMS.
-
or master.sys.databases.containment
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply