CODEWITHSUNDEEP
sql-serversql-server-2005
user222427
user222427

Reputation:

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?

Upvotes: 1

Views: 1024

Answers (2)

gbn
gbn

Reputation: 432471

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.

Upvotes: 2

Demitrius Nelon
Demitrius Nelon

Reputation: 1248

EXEC sp_addrolemember N'db_denydatawriter', N'Foo'

Reference here.

Upvotes: 0

Related Questions