How to provide full alter permissions on a db

Question

How to provide full alter permissions on a db

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
February 10, 2016 at 9:36 am

#308448

How to provide full dml/ddl permissions on a db (all schemas,all objects including [dbo]) with out providing [db_owner] rights?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 1

Not sure why you want to do that, surely setting all the same permissions as dbo would be the same as assigning the user to the db_owner fixed role?

Or is there something within dbo permissions that you want to omit?

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 2

Thanks. I would like to exclude the following:

Drop database

Alter/Drop db files (ndf/ldf)

View/edit/drop users/roles

Deny permissions on certificates/symmetric keys

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 3

SQL!$@w$0ME (2/10/2016)
Thanks. I would like to exclude the following:
Drop database
Alter/Drop db files (ndf/ldf)
View/edit/drop users/roles
Deny permissions on certificates/symmetric keys

what all permission you want to give??

datareader,datawriter,db_executor,ddladmin will work for you i guess

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 4

User get a warning/error when alter existing table with these rights.

I've attached the screenshots of the warnings.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 5

SQL!$@w$0ME (2/11/2016)
User get a warning/error when alter existing table with these rights.

What is the command that the user executes and what is the error/warning returned?

Membership of DB_DDLADMIN should give sufficient permissions to perform an ALTER TABLE statement.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 6

According to this:

https://technet.microsoft.com/en-us/library/ms189612(v=sql.90).aspx

ddl_admin does not have alter table, it does seem to have alter everything else though.

Tbh I would just give the user db_owner and tell them not to delete any users, or delete the database. They won't be able to delete any mdf or ldf files if sql server us running anyway, so that one is covered off.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

SQLSlammer (2/11/2016)
They won't be able to delete any mdf or ldf files if sql server us running anyway, so that one is covered off.

Are you sure about that? If you drop the database, you delete the files with it (assuming the database is online when you drop it).

John

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 8

John Mitchell-245523 (2/11/2016)
SQLSlammer (2/11/2016)
They won't be able to delete any mdf or ldf files if sql server us running anyway, so that one is covered off.
Are you sure about that? If you drop the database, you delete the files with it (assuming the database is online when you drop it).
John

Well yeah, but as the dropping of databases and the deleting of files were listed as separate items I assumed the OP meant browsing through the file system and manually trying to delete the files, which you can't do if SQL server is running.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Yes, I see what you mean, although I interpreted that requirement as referring to ALTER DATABASE...REMOVE FILE and ALTER DATABASE..MODIFY FILE statements.

John

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

SQLSlammer (2/11/2016)
ddl_admin does not have alter table, it does seem to have alter everything else though.

This is incorrect, DB_DDLadmin provides ALTER on any schema which provides ALTER and so TRUNCATE on any table in any schema

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 11

I not sure that alter schema permissions means alter any object within that schema, I think it refers to the following:

https://msdn.microsoft.com/en-us/library/ms173423.aspx

The OP has already tried to alter a table with ddl_admin permissions and still received an error.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

go test it yourself!!

ALTER ANY SCHEMA is implied by DB_DDLAdmin, this grants alter across any schema and so across any table.

I've just done exactly the same myself

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLSlammer Ten Centuries Points: 1045 More actions · Answer 13

Ok you're right, I just tried also with a test login with db_ddladmin only and I was able to truncate a table and add a column.

You have to admit the terminology "Alter Schema" is somewhat confusing though.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 14

It's at least all documented, whatever we may think of the terminology :-).

At https://msdn.microsoft.com/en-us/library/ms191291.aspx, it shows in the SQL Server Permissions chart that ALTER SCHEMA implies ALTER OBJECT, and https://technet.microsoft.com/en-us/library/ms189612(v=sql.105).aspx states that ddladmin implies ALTER ANY SCHEMA.

Cheers!