DDL Admin rights - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

DDL Admin rights

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2011-04-20 : 22:06:03

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

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-21 : 14:35:43

db_ddladmin is available in the User Mapping tab in the User windows of the Security

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

soni321
Starting Member

8 Posts

Posted - 2011-04-21 : 14:42:06

sir iam a new in this site please tell me how i post my problem

Bhupinder Dhatt

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-21 : 15:16:59

DDL_Admin means create, alter, drop any object.

You can grant DDL Admin and then deny create, alter, drop table to that user. Deny overrides grant so the net result is that the user will be able to create, alter, drop anything except a table.

DENY CREATE TABLE TO <username>

You can instead just grant the create, alter, drop for the various objects and then add the db_datareader and db_datawriter roles.

GRANT CREATE TABLE TO <username>

I do recommend, especially if this is for more than one user, create a custom role, grant the permissions to the role, then assign the user to the role.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources