Sharding and users

Category: sql server getstarted

Question

JanusKnudsen on Thu, 13 Feb 2020 18:50:27


Hello

Having a Sql Server with a ShardMapManager and 2 tenant db's, configured and working with a user.

I need to create a new user + login. Fairly trivial, the login is created in master and as a user with db_owner role in the tenants.

But when logging into the server with the new user, I can not see any data in the tenant-db, it is kind of hidden.

Is that be design and what should I do when I want the new dbo-user to access the data.

Replies

Cathy Ji on Fri, 14 Feb 2020 07:54:59


Hi JanusKnudsen,

>> Is that be design and what should I do when I want the new dbo-user to access the data.

No, it is not  the design. You may missed something when creating DB user. Please follow the steps from MS document. Please refer to Create a Database User and Create a Login.

Please check the db_owner option on Owned Shemas and Membership as below screenshot.  I test it in my environment, i can see the data in test2 database.

And you can check what database user is using your login with the following query:

use test2
go
SELECT su.name as DatabaseUser
FROM sys.sysusers su
join sys.syslogins sl on sl.sid = su.sid
where sl.name = 'cathy'

Best regards,
Cathy 


MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


Cathy Ji on Mon, 17 Feb 2020 06:29:25


Hi JanusKnudsen,

Did you resolve your issue? If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
In addition, if you have another questions, please feel free to ask.
Thanks for your contribution.


Best regards,
Cathy