Restrict data rows but not SELECT for users across databases RRS feed

  • Question

  • If there are two tables in two database have the same schema, is there a way to set up permissions for USER1 so that if this user tried do a UNION ALL as so:

    SELECT col1, col2 FROM DatabaseOne.dbo.table1


    SELECT col1, col2, FROM DatabaseTwo.dbo.table1;

    It would only return data from the first table and not the second? If I tried to implement DENY SELECT permissions on DatabaseTwo.dbo.table1, the above query would throw an error. 

    I know that there's methods to allow views to filter records out by adding an additional column to filter out the records, but is there any INHERENT security method I can use to allow a SELECT without allowing a user to view the data?

    Wednesday, August 13, 2014 11:50 PM


All replies