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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy