April 20, 2011 at 8:04 pm
Guys,
Is there anyway to give ddl admin rights to the database user with out create/alter/drop table permissions.
The database user should only have
1. db writer
2. db reader
3. create/alter/drop view
4. create/alter/drop SP
5. create/alter/drop function
6. In addition should be able to access the view, sp, function DDL definition
Any suggestions/inputs would help.
Thanks
April 21, 2011 at 9:00 am
am-244616 (4/20/2011)
Guys,Is there anyway to give ddl admin rights to the database user with out create/alter/drop table permissions.
The database user should only have
1. db writer
2. db reader
3. create/alter/drop view
4. create/alter/drop SP
5. create/alter/drop function
6. In addition should be able to access the view, sp, function DDL definition
Any suggestions/inputs would help.
Thanks
As far as I can understand from your requirements, you can put this database user in db_reader and db_writer and ddl_admin database roles. While at the same time list all tables you want to exclude "create/alter/drop " actioins and explicitly deny "control" permission on them.
Bazinga!
April 21, 2011 at 10:19 am
db_dlladmin is available in the User_Mapping windows of a user in the security on a particular database
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 23, 2011 at 4:04 am
Yes, I do this with most of the linked server accounts.
After adding it to the DDL role, DENY whatever rights need to be denied.
(In my case I have a script denying everything possible.)
Cheers,
JohnA
MCM: SQL2008
April 23, 2011 at 12:46 pm
I tend to go the other way. I think I'd look to grant the CREATE VIEW to a role and add someone to that. I dislike DENY permissions as they get confusing quickly to me, and allow for holes. It's like a trigger that you might not think to look for.
Looks like GRANT should allow you to give those granular permissions to a role: GRANT - http://msdn.microsoft.com/en-us/library/ms178569.aspx
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply