May 27, 2020 at 4:50 pm
Hi all,
I have given to a domain user with windows authentication access to a specific database of an SQL Server 2016.
From the database permissions, I have given the option for Connect and Select, in order the user can see the database objects.
When the user tries to make a "Select top 1000 rows" of an object, he takes an error because he does not have permission to msdb database (he has permission only to one database of the SQL server and not in master, msdb etc).
Is there a way that he can make a "Select top 1000 rows" at the db objects (tables and views) without I give him permission to msdb database?
Also, the SQL intellisence is not working for this user. Is there an option in order I enable to him the SQL intellisence?
Thank you.
May 27, 2020 at 7:22 pm
If the views are accessing objects in another database, I would guess you are running into issues with ownership chaining across databases. It's something that used to be enabled in older versions of SQL Server and is now only enabled with the master and tempdb by default. The reason it changed is due to security issues when enabling database chaining but it is possible to enable it with other databases. The database chaining chapter in this article goes into a lot of detail on how it works, the risks, etc:
In terms of Intellisense and SSMS...there a lot of different things that can impact that. I'd start by going through the documentation that has a list of things that can impact this:
Identify issues with IntelliSense - SQL Server Management Studio (SSMS)
Sue
May 27, 2020 at 7:37 pm
Thanks for your answer.
There is only one database (not system) that is accessible, and it's objects are the ones that concern me, so no other database concerns me.
I want to preserve the select only access to this database, but the users to be able to make a "Select top 1000 rows" from all the objects (tables and views) without SSMS to error.
May 27, 2020 at 8:08 pm
I'm still not sure what the views accesses. If you have an error with msdb, I was wondering if you have objects in those views that access other databases but it seems you are saying that is not the case.
If the views are only accessing objects in that one database then the ownership chains need to be intact for the permissions on views only to work. So the the owner of the view needs to be the same as the owner of the table(s) used in the view. Permissions on the underlying tables aren't needed when all objects in the view have the same owner
Sue
May 28, 2020 at 3:45 pm
What object in msdb is he accessing?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 28, 2020 at 3:52 pm
No object in msdb he is accessing.
Just because the user is only mapped to one database (and not system) and only there he has access, he cannot make a selection Right Click to an object and select top 1000 rows.
May 28, 2020 at 4:18 pm
right click where? If you mean can you hide things in SSMS, then no.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 28, 2020 at 4:21 pm
No hide.
I need the user to be able to make a right click at a db object (table/view), press "Select top 1000 rows" and the SSMS query editor to bring the results (and not fail an error message).
May 28, 2020 at 6:34 pm
No hide.
I need the user to be able to make a right click at a db object (table/view), press "Select top 1000 rows" and the SSMS query editor to bring the results (and not fail an error message).
Why? The user can (and should) just write the SELECT TOP (nnnn) query in the query editor where they have full control over what columns are returned and how many rows.
Especially when considering that using that functionality could return a different set of 1000 rows each time they run it, since it does not include an ORDER BY statement in the scripted query.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2020 at 7:04 pm
I'm asking what object is causing the error. Most users cannot click any object in a database and get select 1000. It depends what the object is defined to be and what permissions are listed.
If you are having an error, please specify what is causing the error.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply