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

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: 2 days ago @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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