Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I have a login that has sysadmin rights on the db server. However, it's not able to access database objects under a custom schema that was created (eg. it cannot select from tables with [custom_schema].[table_name]). Do I need to explicitly grant permissions for this login to the custom schema even though it is part of the sysadmin server role? If that is the case, do I simply run the following if I want it to have db_owner rights to my custom schema?

GRANT TAKE CONTROL ON SCHEMA :: [custom_schema] TO [login];

Thank you in advance!

share|improve this question
1  
Did you try it? Otherwise, do you have a reason to believe it won't work? If it's not working, post an error message. Otherwise, please try it instead of blindly asking "will this work (or not)?" –  Aaron Bertrand Jan 2 at 17:08
    
@AaronBertrand: yes, I've tried it... –  Chinesinho Jan 2 at 17:23
add comment

1 Answer 1

No, members of the Sysadmin role or the database owner role bypass the permission checking algorithm. So no matter what permission are granted or denied , they are able to always access the objects.

share|improve this answer
    
Exactly, that's what I thought too but apparently it isn't the case. There's no harm in me explicitly granting permissions, it's just weird and I wanted to confirm this phenomenon. And yes, I'm sure the tables in question don't also exist in the default dbo schema and the tables created under the custom_schema are present because I can see/access them with no problem. –  Chinesinho Jan 2 at 17:06
    
@Chinesinho if it's not working, please post an exact error message and also evidence that the user getting that error is in fact in the sysadmin role. –  Aaron Bertrand Jan 2 at 17:09
    
@AaronBertrand: The error is as follows - "SSIS Error Has Occurred: Microsoft.SqlServer.Dts.RunTime.TaskHost-1071610793 ADO_DST_[custom_schema.table_name] [3218] Failed to get properties of external columns. The table name you entered may not exist, or you do not have SELECT permission on the table object and an alternative attempt to get column properties through connection has failed. Detailed error messages are: Invalid object name [custom_schema].[table_name]. No column in" –  Chinesinho Jan 2 at 17:22
    
So how are you certain that your SSIS package is running in the context of a sysadmin account? Did you check> –  Aaron Bertrand Jan 2 at 17:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.