1

I'm trying to add mydomain\myuser to the db_denydatawriter role but i can find a simple example of the query does anybody have a quick example?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

2 Answers2

2

3 steps, in case you haven't set up login + user already

  • CREATE LOGIN [mydomain\myuser] FROM WINDOWS; at the server level. MSDN
  • CREATE USER [mydomain\myuser] FROM LOGIN [mydomain\myuser]; at the db level. MSDN
  • Match user to role EXEC sp_addrolemember 'mydomain\myuser', 'db_denydatawriter'

Edit:

This only prevents INSERT, UPDATE and DELETE directly on the tables

It won't stop changing table design. That is ddl_admin or db_owner. db_owner rights override all other permissions so deny will have no effect.

If writes are via stored procs, ownership chaining means permissions are not checked on a table. So this answer won't work.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • I did this, the user can still modify the tables. –  Dec 30 '10 at 14:19
  • What other roles are they in? db_owner? – MartW Dec 30 '10 at 14:21
  • @Mike: please clarify "modify the tables". Do you mean data changes or add columns etc? Do they use stored procs to write data? – gbn Dec 30 '10 at 14:24
  • They are able to run insert and update commands, no, they are not using any stored procs. –  Dec 30 '10 at 14:27
  • I see the edit, but the user is still able to edit. i run sp_helpuser and the user is only listed as DB_denydatawriter –  Dec 30 '10 at 14:33
  • I'm thinking the same thing but i'm looking right at activity monitor and mydomain\myuser is listed right there looking at the DB –  Dec 30 '10 at 14:51
  • Nope mydomain\myuser doesn't have sysadmin rights, does it matter that the m in user is caps? so mydomain\Myuser? –  Dec 30 '10 at 14:57
  • Perhaps if case sensitive collation, but the security uses the SID not name to match things up. If you can run activity monitor, then you may have sysadmin rights, however by default you'll only see your *own* permissions (sp_helpsrvrolemember) and connections (activity monitor) because of "metadata visibility". There is a valid reason: we just haven't found it yet. You could add a trigger to the table that logs suser_sname() etc too – gbn Dec 30 '10 at 15:02
  • using sp_helpuser SID is (Excluded), which i think it's suppose to be, but i'm at this point lost Username = mydomain\Myuser groupname= db_denydatawriter loginname = mydomain\myuser –  Dec 30 '10 at 15:07
  • @Mike - Given what's been ticked off so far, I'm thinking that the user must be a member of a windows group, which (directly or indirectly) is sa on the server or dbo on the database. Unfortunately, there's no simple tool in SQL Server to explain *why* a particular user is receiving a particular set of permissions. – Damien_The_Unbeliever Dec 30 '10 at 15:22
  • I think this is an AD group membership issue. –  Dec 30 '10 at 15:22
  • @Mike: please let us know what you found later. Thanks for the rep. – gbn Dec 30 '10 at 19:46
0
EXEC sp_addrolemember N'db_denydatawriter', N'Foo'

Reference here.

Demitrius Nelon
  • 1,210
  • 1
  • 10
  • 29