SQL Server Security – Deny Delete to Active Directory Account

It would be confusing if you granted someone db_datawriter and then denied them delete rights. So if you can, remove them from the db_datawriter and then grant them the rights they need:

create role role_MyApp
grant select, insert on Table1 to role_MyApp
...
exec sp_adduserrole 'role_MyApp, 'User1'

The question is rather vague, or at least to me it is.

You state you had new user accounts 'appear' when you migrated, but then state that 'they can only SELECT and can't write to the database like they used to in 2000'. Did these same user accounts exist in the 2000 instance or did they appear only after creating 2008? Were they created by someone or did they literally just appear, like the service users typically do (such as DOMAINNAME\NTAUTHORITY).

Also when you migrated over, did you create new domain controllers? If so, your user SIDs no longer match. Did you rename your domain? If so, your sql server users are still referenced by the old domain which means you want to script their permissions out by right clicking on them, script user as, create. Then change the user name to reference the new one. Make sure you then include this user in all the databases it needs access to.

If you did in fact create a new domain, make sure you update your internal SQL Server name to reference the new Fully Qualified Host Name: http://blogs.msdn.com/b/cindygross/archive/2010/02/11/how-to-rename-sql-server.aspx

There's a lot of unknowns, please further clarify. Thanks!