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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy