asked
SQL 2008 R2 Minimum permissions role to allow user to extract data
I am trying to give minimum permissions to allow a user (vendor) via Windows Authentication to my 2008 R2 server/database. I have also installed BIDS (not sure that is relevant to my two questions): 1) The vendor will login via RDP to the desktop of the Windows 2008 R2 server and they will be extracting data from my 2008 R2 SQL PRODUCTION database. Without knowing how they will extract, I can't test an extract with my test account. I want to make sure I am giving only enough permissions and not too much. I believe they need they need db_datareader role to my production database. The vendor says they need Data Reader access. Is this all they need to do an extract? (No Sysadmin role I assume.) 2) I do need to allow them access to a database where they can store their own packages for scheduled daily, weekly, monthly extracts. I planned on creating the database and giving the vendor account dbo ownership and sysadmin role. Is this what I need here?
You are basically there. Make sure you have sign-off from someone above you.
I'd be nervous of doing this without having quite a few sensible precautions. From what you've said, it is difficult to be specific about what these would be and I haven't had direct experience of this sort of security problem. However I'd guess that you need to give them a schema within a database within which they can create, execute and select procedures. I guess you'd need to set up a login-less user with just the permissions on base tables etc that you're comfortable with and assign the EXECUTE AS to that user. However, if they want to schedule their own stuff too then I would imagine it would be better to set up for them a 'tick' stored procedure that gets called at a 'low usage' time. I'd like to avoid giving them permissions to set up their own jobs. I would create a server login for them using windows security. I would create a database user associated with that login. I would then create a schema for them and make that the default schema for their USER. I'd then assign them Select and execute rights to that schema but nothing else. Then, I'd get them to specify the queries they'd need and create either a view, procedure or function for them as appropriate in their schema, using an account that has the select access to the required tables.
Thank you. Basically the vendor is a financial auditing consultant who does work with our large business (50k+ employees, multiple facilities). Our facility was added to this large business and we need to add similar access. I am going to see the queries they are running supposedly 3 that take from seconds to 7 minutes to run. So I should have a better idea of what they need soon.
16 People are following this question.
