June 13, 2014 at 12:24 pm
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
June 13, 2014 at 12:31 pm
EXEC sp_addrolemember 'db_datareader', 'DOMAIN\HELPDESKGROUP'
GRANT UPDATE, DELETE ON onespecifictable TO [DOMAIN\HELPDESKGROUP]
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 13, 2014 at 12:38 pm
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
June 13, 2014 at 12:47 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 13, 2014 at 1:54 pm
not a brain freeze, user told me error said did not have access, went to desk of user executing sql against master................
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply