August 12, 2013 at 5:25 pm
Hi Folks,
I got one interesting question here....
I have a server called 123 and DB called XYZ and we have 5 tables in the DB.
I created some logins and one login should not see the tables in XYZ but he should access by selecting.
For Ex: XYZ has 5 tables. A B C D E. The user should not see that tables under XYZ but when he selects from XYZ.A...he should get the data.
I gave Public access to the login. The login then not able to see the tables but if he selects...error showed up...no access to table.
I gave public and dbreader access. The login can see the tables and can access only select.
I want to know.....is there any way the login should not see the tables but he should select data from the table.
Appreaciate your help.
Thanks,
SK
August 13, 2013 at 12:14 am
I don't think that is possible. Kind of a weird request by the way.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 7:32 am
You do know that the 3 part naming convention is only a way to identify which object you are referring to right?
In other words, your request contradicts itself. You are saying that if a user is connected to database XYZ they are unable to select data from table A but if they refer to the object with three part naming then they can. This just doesn't make any sense. You can't control access to an object based on how the reference is made. I can't figure out what it is you are really trying to accomplish.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 14, 2013 at 10:14 am
I think the OP may be asking whether a user can have SELECT permission on a table but will not be able to "see" the table when logged in to the SQL Server instance with SSMS or some similar tool. If so, I think this can be done by denying the user VIEW DEFINITION permissions. VIEW DEFINITION permission allows the user to see the metadata of the securable (the sys.objects row for a table, for example). VIEW DEFINITION permissions can be managed on the server, database, schema, and object levels, which should give the OP some flexibility in accomplishing his goal.
Jason Wolfkill
August 14, 2013 at 10:52 am
You should create procedures which do the select and only grant EXECUTE to those procedures (preferably in a dedicated schema) to that user / role
Same would work with a View or even Synonym (that's the simplest version). Then you would grant the SELECT-Permission onto the schema containing just the synonym/view, and that's it.
Condition: the schema containing the tables has to have the same owner as the schema containing the procedure/view/synonym. - Probably "dbo" anyways.
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
August 14, 2013 at 4:00 pm
GRANT SELECT and DENY VIEW DEFINITION on the object:
CREATE USER nisse WITHOUT LOGIN
go
CREATE TABLE pelle (a int NOT NULL)
GRANT SELECT ON pelle TO nisse
DENY VIEW DEFINITION ON pelle TO nisse
go
EXECUTE AS USER = 'nisse'
go
SELECT a FROM pelle
SELECT object_id FROM sys.objects WHERE name = 'pelle'
go
REVERT
go
DROP TABLE pelle
DROP USER nisse
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply