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

reader role for user define db role brain freeze Expand / Collapse
Author
Message
Posted Friday, June 13, 2014 12:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:05 PM
Points: 59, Visits: 109
Ok trying to clean up security at new job. Helpdesk group needs read only access to a database along with the ability to update and delete records in one table. So I define the extendable security for the update/delete, thats easy, isnt' there a way to add the db_datareader option to this role
Post #1580696
Posted Friday, June 13, 2014 12:31 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
EXEC sp_addrolemember 'db_datareader', 'DOMAIN\HELPDESKGROUP'
GRANT UPDATE, DELETE ON onespecifictable TO [DOMAIN\HELPDESKGROUP]



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1580700
Posted Friday, June 13, 2014 12:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:05 PM
Points: 59, Visits: 109
i believe this adds read only functionality to a windows/ad group which is ok. What I was looking at doing was this

Add windows\ad group helpdesk as a login.
Give it public access to database
create a user defined database role that has the read only rights and the one table update
add this wind\ad group login to the user defined database role

AM I missing something
Post #1580707
Posted Friday, June 13, 2014 12:47 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
Of course you could do that. Your requirement was not clear to me.

CREATE LOGIN [DOMAIN\Helpdeskgroup] FROM WINDOWS
go
USE Helpdeskdb
go
CREATE USER [Domain\Helpdeskgroup]
CREATE ROLE Helpdeskrole
EXEC sp_addrolemember Helpdeskrole, 'Domain\Helpdeskgroup'
EXEC sp_addrolemember db_datareader, Helpdeskrole
GRANT UPDATE, DELETE ON Helpdeskrole

If you are on SQL 2012, use ALTER ROLE ADD MEMBER rather than sp_addrolemember.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1580710
Posted Friday, June 13, 2014 1:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:05 PM
Points: 59, Visits: 109
not a brain freeze, user told me error said did not have access, went to desk of user executing sql against master................
Post #1580727
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse