Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

user permissions to only for view Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 11:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 10:24 PM
Points: 48, Visits: 370
I need to give user 'dhii' select permissions only to a view not for all tables. The view has columns from two tables from the same database (ABC). 'dhii' should only see the columns in this view, not any underlying tables.

what are the steps to follow?
Post #1502916
Posted Wednesday, October 9, 2013 1:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:24 AM
Points: 13,293, Visits: 11,080
You should give the user only SELECT permission on the view.

GRANT SELECT ON viewname TO username;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1502940
Posted Wednesday, October 9, 2013 1:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 8:10 AM
Points: 4, Visits: 66
Use the below query:


Use [Databasename]
go

Grant SELECT ON Viewname TO dhii;
Post #1502941
Posted Wednesday, October 9, 2013 2:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 10:24 PM
Points: 48, Visits: 370
after login with taht user i can see systemtables,system stored procedures,another users(even i can modify it) under particular database...but i want to restrict that..
Post #1502966
Posted Wednesday, October 9, 2013 2:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:24 AM
Points: 13,293, Visits: 11,080
$w@t (10/9/2013)
after login with taht user i can see systemtables,system stored procedures,another users(even i can modify it) under particular database...but i want to restrict that..


You'll need to check which permissions to user already has and then deny them.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1502969
Posted Wednesday, October 9, 2013 2:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:27 AM
Points: 5,430, Visits: 10,097
Granting SELECT on a view does exactly that. It doesn't revoke any other permissions that the user already has. Query sys.database_permissions to find out what those permissions are.

John
Post #1502971
Posted Wednesday, October 9, 2013 3:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 10:24 PM
Points: 48, Visits: 370
it has connect,select,execute permissions only..
Post #1502975
Posted Wednesday, October 9, 2013 6:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:20 AM
Points: 554, Visits: 1,624
Is this user in any of the Database Roles? If so remove it, and it will have only the permission you explicity added.
Post #1503073
Posted Thursday, October 10, 2013 12:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 10:24 PM
Points: 48, Visits: 370
it doesn't have any permission on any other database...i want to disable security,managment to that user

please check the attachment user can see 'sa'(i want to restrict it)


  Post Attachments 
Capture1.JPG (10 views, 30.87 KB)
Post #1503380
Posted Thursday, October 10, 2013 12:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:24 AM
Points: 13,293, Visits: 11,080
$w@t (10/10/2013)
it doesn't have any permission on any other database...i want to disable security,managment to that user

please check the attachment user can see 'sa'(i want to restrict it)


Just because he can see sa doesn't mean he can do anything with it.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1503387
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse