Hide schema from all users (except people with SA access)

Question

Hide schema from all users (except people with SA access)

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

richardmgreen1 - Wednesday, September 12, 2018 2:32 AM
Thanks Sue
I've just run SELECT has_perms_by_name(null, null, 'alter any linked server') for the relevant user and it's coming back as a zero (which I'm assuming means no permissions).
I've double-checked the user in question and it definitely has a tick in the Grant column for "Alter any linked server".
Anyone any ideas as to why it's not being recognised?

SQL Server doesn't see those permissions when the user runs the code or if checking with has_perms. Most likely SQL Server is recognizing things correctly.
Try using t-sql instead of the GUI to check permissions as well as to grant
--check permissions SELECT p.[name], sp.permission_name, sp.state_desc, sp.class_desc FROM sys.server_permissions AS sp JOIN sys.server_principals AS p ON sp.grantee_principal_id = p.principal_id WHERE p.[name] = 'YourTestLogin' --or --WHERE sp.[permission_name] = 'ALTER ANY LINKED SERVER'

--grant permissions GRANT ALTER ANY LINKED SERVER TO [YourTestLogin]

Sue

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Just to understand here, the purpose of the schema you're trying to hide is so you can export data to files? Is that correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

richardmgreen1 SSChampion Points: 10352 More actions · Answer 3

Hi Sue

Thanks for that, it's a handy bit of code.
I've just run it and got the following:-
name    permission_name    state_desc    class_desc
RDGH\z1    ALTER ANY LINKED SERVER    GRANT    SERVER
RDGH\z1    CONNECT SQL    GRANT    SERVER

Apologies for the dodgy formatting.

Still stumped as to why this user can't create a linked server.

richardmgreen1 SSChampion Points: 10352 More actions · Answer 4

Jeff Moden - Wednesday, September 12, 2018 7:36 AM
Just to understand here, the purpose of the schema you're trying to hide is so you can export data to files? Is that correct?

Hi Jeff

No, it's to hide some tables that we don't want users to see.
As has been mentioned before, we could move these tables to another database but that would be a lot of work.

The ability to use linked servers is to allow users to export data in either CSV or Excel format and to be able to point the stored procedure to any table/view they have access to and export the data..

I want to take away db_owner permissions (which some users currently have) in order to hide the tables but still be able to export the data using these stored procedures.

Hopefully that makes sense.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

richardmgreen1 - Wednesday, September 12, 2018 7:38 AM
Hi Sue
Thanks for that, it's a handy bit of code.
I've just run it and got the following:-
name    permission_name    state_desc    class_desc
RDGH\z1    ALTER ANY LINKED SERVER    GRANT    SERVER
RDGH\z1    CONNECT SQL    GRANT    SERVER
Apologies for the dodgy formatting.
Still stumped as to why this user can't create a linked server.

Yup, I like that little script to quick check those things.
Next I'd run the same script for just the permission itself to check on alter any linked server so that you can see if there are any explicit denies.
If that still looks fine, run a quick trace using extended events or profiler when the user runs the procedure and make sure to capture the name of the login to see if it's running under that context.

Sue

richardmgreen1 SSChampion Points: 10352 More actions · Answer 6

Sue_H - Wednesday, September 12, 2018 7:49 AM
richardmgreen1 - Wednesday, September 12, 2018 7:38 AM
Hi Sue
Thanks for that, it's a handy bit of code.
I've just run it and got the following:-
name    permission_name    state_desc    class_desc
RDGH\z1    ALTER ANY LINKED SERVER    GRANT    SERVER
RDGH\z1    CONNECT SQL    GRANT    SERVER
Apologies for the dodgy formatting.
Still stumped as to why this user can't create a linked server.
Yup, I like that little script to quick check those things.
Next I'd run the same script for just the permission itself to check on alter any linked server so that you can see if there are any explicit denies.
If that still looks fine, run a quick trace using extended events or profiler when the user runs the procedure and make sure to capture the name of the login to see if it's running under that context.
Sue

Thanks Sue

I've just run your code in two parts.

Part 1 - use the first WHERE clause to check permissions on the RDGH\z1 login
Alter any linked server appears

Part 2 - use the second WHERE clause to check permissions based on ALTER ANY LINKED SERVER
RDGH\z1 login appears

Now to move onto checking the code and see who's running it.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

richardmgreen1 - Wednesday, September 12, 2018 7:41 AM
Jeff Moden - Wednesday, September 12, 2018 7:36 AM
Just to understand here, the purpose of the schema you're trying to hide is so you can export data to files? Is that correct?
Hi Jeff
No, it's to hide some tables that we don't want users to see.
As has been mentioned before, we could move these tables to another database but that would be a lot of work.
The ability to use linked servers is to allow users to export data in either CSV or Excel format and to be able to point the stored procedure to any table/view they have access to and export the data..
I want to take away db_owner permissions (which some users currently have) in order to hide the tables but still be able to export the data using these stored procedures.
Hopefully that makes sense.

Probably me over simplifying a bit but I've done this quite successfully in the past. The first thing to do is what you're already identified.... removing privs from the users. The second thing is to change the stored procs to using WITH EXECUTE AS OWNER and then giving the users privs to EXECUTE those procs without having privs to view the content of the procs and without having any privs to the underlying tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

richardmgreen1 SSChampion Points: 10352 More actions · Answer 8

That sounds perfect.
I've looked at the procedure I need users to run and it's currently listed as Execute As Caller.
I can't see an owner for it in the properties (unless I'm looking in the wrong place.

Would I just put "EXECUTE AS OWNER" in the procedure code or do I need to do something a bit different?

::edit::
Got a bit further.
I've added WITH EXECUTE AS OWNER to the procedure I'm using which didn't seem to do a lot.
A quick Google said that I needed to make the calling database trustworthy.

Did that and now I'm getting a different error.
The new error is:-
Msg 7437, Level 16, State 1, Line 1
Linked servers cannot be used under impersonation without a mapping for the impersonated login.

The owner for the procedure (as far as I can see) is DBO, or am I looking at this wrong?