April 6, 2016 at 4:22 am
Hi there,
Very sorry if I am posting this in the wrong sections. I am stuck on a reporting issue with permissions.
I'll start of with structure..
MYSQLSERVER2012
-ReportDatabase {I am the owner of the database}
--Procedures
---ProcedureA with EXECUTE AS OWNER
---ProcedureB with EXECUTE AS OWNER
---ProcedureC with EXECUTE AS OWNER
--ReportDatabase SECURITY
---SYSADMINSGROUP
---ReportingGroup {Has db_executor permission}
-ProductionDatabase {This database is restored to our server on a daily schedule... The owner of this database someone who is not in our server users.} 2008 compatibility
--ProductionDatabase SECURITY
---SYSADMINSGROUP
-SERVER SECURITY
---SYSADMINSGROUP - I am in here.
---ReportingGroup - End Users
OK my users are calling the procedure from Excel Data Connection.
The Procedure is querying the ProductionDatabase manipulating and return data.
I would have assumed that once procedure is executed... the procedure then excutes with my permissions.
Not needing ReportingGroup to be mapped to ProductionDatabase. Unfortunately... this is not the case I get a error...
Msg 916, Level 14, State 1, Procedure ProcedureA, Line 42
The server principal "domain\myuser" is not able to access the database "ProductionDatabase" under the current security context.
I don't understand what does this mean? Am I doing something wrong?
If I don't use EXECUTE AS OWNER.. I can execute and it works fine, but then the for the end users they get the same error, but with their user
The server principal "domain\enduser" is not able to access the database "ProductionDatabase" under the current security context.
By the way... I even tried creating a localsqluser mapped to all the databases... and EXECUTE AS 'localsqluser'.. still get the same error.
The server principal "localsqluser" is not able to access the database "ProductionDatabase" under the current security context.
P.S the reason I don't want ReportingGroup mapped to the ProductionDatabase is because I don't want them to be able to connect to the server and make their own queries, and because they will be able to see all objects in ProductionDatabase.
I really hope I get a answer here... at least a "cant be done" would be good to so I can give up and move on.
April 6, 2016 at 7:02 am
OK so figured it out its because the database needs to be trustworthy.... So you need
ALTER DATABASE ReportDatabase SET trustworthy ON
GO
Now can I ask why is this turned off by default... and in my case this ReportDatabase will only contain StoredProcedures that I have made for end users to execute using MS Excel, and the users connecting to the database will all only have db_executor permission. So that they can only execute stored procedures...
So is there really a risk in my case that a user could somehow impersonate a sysadmin user in the database and do any damage?
April 6, 2016 at 7:28 am
Yep there is. If DBO = sa as is the case in many cases, and trustworthy is on, what if you have a procedure set to execute as owner which sneaks in the code:
ALTER SERVER ROLE sysadmin ADD MEMBER Joe
DBO = sa means that this code would work in that execution context. The reason TRUSTWORTHY defaults to off is to prevent things like this from slipping into code during large deploys and giving people back doors into the database.
April 6, 2016 at 7:45 am
But as long as the procedures doesn't have code like that its fine right? because all the procedures in this database will be created by me and they are procedures to return data to end users... so as long as end user cannot alter the procedure only has execute rights to the stored procedures ... I should be safe yes?
April 6, 2016 at 7:49 am
If you can account for all of your code and this doesn't exist, you may be safe. In a case like this I will never say you WILL be safe, but it looks promising. The question about why the default setting though, is because it's protecting against malicious code from internal developers and users.
April 6, 2016 at 7:54 am
jeff.mason (4/6/2016)
If you can account for all of your code and this doesn't exist, you may be safe. In a case like this I will never say you WILL be safe, but it looks promising. The question about why the default setting though, is because it's protecting against malicious code from internal developers and users.
Great... our server is used for reporting soo its only a few of us who have sysadmimn access to this server.. trustworthy colleagues!!... apart from colleagues... end users who only receive data at the max they have db_reader permission on some databases.. in my case my end user group has only db_executor permission.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply