August 8, 2017 at 5:04 am
I'm using SQL Server 2014 Standard and starting to wonder if I have a bug.
I've created some logins with limited access to the SQL Server, by first creating them a public login and then specific user access to certain tables/views on a database.
But, when I set the user up in their application (e.g. Excel) and create the dsn, the only database they have access to is master and tempdb.
I solved it last week by setting the default database for the login to the database I wanted them to have access to, but even that's not working this week.
The only way around it I have found is to give them sysadmin when setting up the connection and then removing it once they are set up. This can't be right surely?
What else would affect this?
August 8, 2017 at 5:30 am
No, you shouldn't make them sysadmin.
Can you script out the login, the user and all their permissions? There's probably a deny somewhere, or a missing permission.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2017 at 6:42 am
Hi Gail,
I have solved it, but you did spark me to look deeper at all the deny permissions, so thank you for that.
One of my network admins had added the domain user group as a login (for recording software audit results) and then granted them deny view any definition, which of course meant no existing logins were affected...they could connect as usual....and new connections via ADO were fine......but creating a dsn from Excel meant you couldn't see the databases!
All good now, so thank you again for the reply.
Regards,
Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply