April 25, 2018 at 6:39 pm
I'm guessing this is possible, but just checking.
We have an app that creates and deletes tables(not temp tables). I think it's currently using sa to do this.
Can I create a new user that has the ability to create and delete tables in a specific list of databases,
but does not have that ability in any databases that I don't explicitly grant that access for? I want to do this in
the default schema(dbo) of these databases. It seems like that would have to be possible, but just making sure.
Does the user have to be a member of the dbo_owner database role to be given this type of access?
The data in these databases is not sensitive at all, but we shouldn't be using sa.
One of our users is asking us to use a named schema instead of the dbo schema, for security reasons, but I
think their security people are worried about the use of the dbo user(sa), not the dbo schema. Is there anything
inherently dangerous about using the dbo schema as long as you're not using sa?
Sorry, total newbie in the security area.
Thanks.
April 26, 2018 at 12:40 am
You can create a specific login for the application to use, preferrably from an Active Directory account. Next create a corresponding database user in each database the application needs access to. If you make that user member of the "db_ddladmin" database role it gets all the permissions needed to create/modify/delete database objects.
April 26, 2018 at 8:56 am
HanShi - Thursday, April 26, 2018 12:40 AMYou can create a specific login for the application to use, preferrably from an Active Directory account. Next create a corresponding database user in each database the application needs access to. If you make that user member of the "db_ddladmin" database role it gets all the permissions needed to create/modify/delete database objects.
FYI, that does go quite a bit beyond just creating tables.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 26, 2018 at 9:53 am
ALTER ANY <Database Securable>, where Database Securable can be any securable at the database level.GRANT ALTER ANY TABLE TO AppUser;Confers the ability to CREATE, ALTER, or DROP individual instances of the Database Securable. For example, ALTER ANY SCHEMA confers the ability to create, alter, or drop any schema in the database.
April 27, 2018 at 10:16 am
Joe Torre - Thursday, April 26, 2018 9:53 AMALTER ANY <Database Securable>, where Database Securable can be any securable at the database level.GRANT ALTER ANY TABLE TO AppUser;Confers the ability to CREATE, ALTER, or DROP individual instances of the Database Securable. For example, ALTER ANY SCHEMA confers the ability to create, alter, or drop any schema in the database.
There's the answer! KAaaaa - BOOM ! Nicely done, Joe...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply