Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Customize DB Role Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 12:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
Hi,

I want create one database customize role, that role should able to do only fetching the data from SQL view (rest of objects should not able to visible) in respective database.

Please provide scripts for customize DB role.

Thanks in advance.

Pradeep.


Pradeep
Post #1437363
Posted Monday, April 1, 2013 6:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:20 PM
Points: 14,184, Visits: 37,070
it's trivially ewasy, but the most important thing is to make sure noone has sysadmin rights; you cannot hide or deny access to a sysadmin.

sql is deny by default, so if permissions to an object (table/view/proc/etc) not given to someone, they cannot see or access the objects.

USE [SandBox] --my db for users to do stuff.
CREATE ROLE [OneViewForYou]
--give my new role READ permission to one specific view
GRANT SELECT ON dbo.MyView TO [OneViewForYou]

--finally, add our user bob to the role we created

EXEC sp_addrolemember N'OneViewForYou', N'bob'




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1437414
Posted Tuesday, April 2, 2013 3:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
Thanks.

Pradeep
Post #1437770
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse