SQL Server 2005 db_denydatawriter example query

问题:

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?

回答1:

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.

回答2:

EXEC sp_addrolemember N'db_denydatawriter', N'Foo' 

Reference here.