February 1, 2018 at 11:14 am
Running SQL Server 2014
I can add a windows user to the SQL server and the user is able to login to the SQL server.
However, when I add user mapping, the user can not see the databases that are mapped.
Example
DOMAIN/user1 is mapped to [DataBase1] as db_owner with the default schema set to dbo
When user1 accesses the SQL server via SSMS, they are unable to see any of the databases they are mapped to.
If I grant DOMAIN/user1 the server role of sysadmin, they can then see all of the databases
I have a server running SQL Server 2016 and this approach works fine. I've checked user settings and user mappings and they are configured identically.
Not sure what's going on with this server but need help. I do not want to grant sa to other users.
February 1, 2018 at 11:40 am
zack.soderquist - Thursday, February 1, 2018 11:14 AMRunning SQL Server 2014I can add a windows user to the SQL server and the user is able to login to the SQL server.
However, when I add user mapping, the user can not see the databases that are mapped.
Example
DOMAIN/user1 is mapped to [DataBase1] as db_owner with the default schema set to dbo
When user1 accesses the SQL server via SSMS, they are unable to see any of the databases they are mapped to.
If I grant DOMAIN/user1 the server role of sysadmin, they can then see all of the databases
I have a server running SQL Server 2016 and this approach works fine. I've checked user settings and user mappings and they are configured identically.
Not sure what's going on with this server but need help. I do not want to grant sa to other users.
My first guess would be that the permissions for the public role were changed on the server where you are having the problems. The default is public having view any database permissions. You can check for a difference by executing the following on both: SELECT
l.name as Account,
p.state_desc,
p.permission_name
FROM sys.server_permissions as p
INNER JOIN sys.server_principals as l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE'
Sue
February 1, 2018 at 11:45 am
Sue,
That was it. Thank you!!!
Zack
February 1, 2018 at 11:32 pm
zack.soderquist - Thursday, February 1, 2018 11:45 AMSue,That was it. Thank you!!!
Zack
Nice !!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply