June 16, 2012 at 5:43 pm
Is there any procedure to where prevent users from accessing ALL the tables (I,U,D) on database.
I tried out this trigger script. It worked out fine. But, the problem is it works for single table. I have more than 200 tables to do this kind, but looking for better process.
The existing database has "DB_DATAREADER AND DB_DATAWRITER".
I know we can make the database option as "DB_DATAREADER". But, there are some people in the group which do not need the existing access and per company policy I can't create a separate group for some of the users.
So, can some one help me with this process, that would be great. Thank you.
-- Create trigger to prevent user A to execute insert/update/delete on table test.
CREATE TRIGGER [Triggername]
ON dbo.Test_Table
FOR INSERT, UPDATE,DELETE
AS
IF SYSTEM_USER = 'Domain\Username'
BEGIN
RAISERROR ('User can''t insert/update/delete into table', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
GO
June 17, 2012 at 3:42 am
DML Triggers are per-table
Security is best done with security settings. Remove the people who don't need to update from db_datawriter or give then db_denydatawriter. Why can't you make a separate role?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2012 at 6:17 am
you do not have create a seperate group in Active directory.
go a head and create a role that has db_denydatawriter as Gail suggested.
roles in SQL Server are not an Either this / Or That group kind of thing, your users can(and should) belong to multiple roles.
after you create that role , then add the specific users from the AD group that should not be allwoed to update sutff.
here's as specific example:
CREATE ROLE [DenyWriteOnly]
--give reader writes to this group
EXEC sp_addrolemember N'db_datareader', N'DenyWriteOnly'
--explicitly DENY access to writing
EXEC sp_addrolemember N'DB_DenyDataWriter', N'DenyWriteOnly'
--now add some specific users to this role to prevent their other permissions form letting them write
EXEC sp_addrolemember N'DenyWriteOnly', N'MyDomain\Lowell'
EXEC sp_addrolemember N'DenyWriteOnly', N'MyDomain\Gail'
EXEC sp_addrolemember N'DenyWriteOnly', N'MyDomain\Bob'
Lowell
June 17, 2012 at 8:37 am
Thank you. Basically, I have a situation where when users log on through SSMS, they should have only "Read" access. But, the same users when they work through front-end applications, they should have "I,U,X" permissions. For this scenario, I am working in different ways, but nothing worked out so far..:(...
The reason is the front-end application team requires I,U,X permission in order to function there application. They work through there Windows authentication. I can application reader and writer permissions though, but there is chance of users accessing database directly and might spoil the other tables which might not require for the users. So, the company want us to give write access onto front end application and Read access only on back end applications.
Did, anyone faced this kind of situation and able to resolved the issue. I tried using application role, triggers. Nothing worked out properly. Please help me.
Thank you
June 17, 2012 at 9:37 am
These lotsnof examples of logon triggers trying to prevent any access at all if they user tries to connect with ssms/access/excel.
Any logom trigger that tries to block bases on application name can be wormed around however.
An untested possibility for a logon trigger would be to add users to to db _denydbwriter if it is a winows login and in a certain group but remove them from the role if they are using the specif application
Once again that could be worked around but its an idea.
Lowell
June 17, 2012 at 10:16 am
What you need there is to set up an application role (if you can change the app to use it). There are other better solutions, but that's the easiest
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2012 at 8:28 pm
Gila,
I have created an application role and given write permissions. But, I am not understanding how to assign this role to particular group. Can you help me with the steps please. Thank you
June 18, 2012 at 1:47 am
You don't assign application roles to groups. The application just invokes the roles.
I suggest reading up on app roles in BoL, they're not the same as traditional roles and have some behaviour quirks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply