question

Katie 1 avatar image
Katie 1 asked

User privileges as db_datareader, db_datawriter

All, Can a user with the db_datareader and db_datawriter access drop or or alter a contraint??
sql-server-2008sqlsecurity
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
Take a look at the fixed db roles in books online: [ http://msdn.microsoft.com/en-us/library/ms189612(v=SQL.100).aspx][1] The roles grant select, insert and delete along with view database, nothing to do with DDL though. You will need to take a look at maybe granting access on an object level, or maybe the DDL Admin fixed role, although that allows alot more than just constraints. [1]: http://msdn.microsoft.com/en-us/library/ms189612(v=SQL.100).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

K. Brian Kelley avatar image
K. Brian Kelley answered
No. If a user has such ability and you're thinking they only have db_datareader and db_datawriter, check to see if they are coming into the database with a Windows group. Also check to see if they have explicit permissions granted that permit them to create tables or alter the schema containing the tables. Use the sp_helprotect stored procedure in SQL Server 2000 or query the sys.database_permissions catalog view in SQL Server 2005/2008/2008R2.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Like there are **db\_denydatareader** and **db\_denydatawriter** fixed roles which given to an individual user would override roles inherited from Windows Groups, I'd like to see a **db\_denyddladmin** fixed role, to solve the scenario: "Allow everyone in the Windows group **Statistic Creators** except user A and user B to create new views, alter table A, table B and table C"
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.