0

My scenario: I have some groups with full - and inappropriate - access to all Sql Server instances. I want to change that.

My Plan: Give full and automatic access to all databases (it is customer databases and this groups must be allowed to change any property if necessary).

Restrict permissions on Instance Level, because this groups should not be allowed to do any unexpected change on instance properties.

I don't want to create a user in databases, because our external customer could drop the user ( they have db_owner permission )

Someone have any idea? Tks

5
  • Are you familiar with the db_owner.database role? See the SQL Server permissions poster: go.microsoft.com/fwlink/?LinkId=229142.
    – Dan Guzman
    Feb 3 '17 at 11:04
  • Yes, I do. But to use this role, I have to create the user into the database and I'm trying to avoid this.
    – Jose Rocha
    Feb 3 '17 at 11:14
  • How are new databases created and by whom? Without a database user, only the database owner (authorization) will have full database permissions.
    – Dan Guzman
    Feb 3 '17 at 11:29
  • New databases are created by stored procedure, which is called by an internal system...
    – Jose Rocha
    Feb 3 '17 at 22:33
  • So customers will not be able to create new databases, except via the stored procedure that will not run as the user's login, correct? And is there anything else about my answer that won't work for your situation? I can update my answer if you critique it.
    – T.H.
    Feb 6 '17 at 12:29
2

You can create a user that has certain non-db_owner fixed database roles assigned, and then you can create a user-defined role which contains additional permissions. (See https://msdn.microsoft.com/en-GB/library/ms189121.aspx)

So in effect, you can create a user that has all the rights of db_owner except say, managing users and dropping database. But I don't think there's a way to directly deny such privileges, you have to instead add everything but them.

Your database creation SP can configure new databases in this way, and you’d have to do it for each existing database.

1

I think this is the closest you can get:

  1. Create a login that has only Public and DBcreator Server roles.
  2. Map the new login to user DBO in all existing databases.

If your customers use the new login, they will have full control of all pre-existing databases (including being able to drop them!), as well as any new ones created with that login (since creator login gets mapped to dbo). And they will not be able to drop any dbo user.

2
  • Is there any other way to give access to a login in a database without having to create a user?
    – Jose Rocha
    Feb 6 '17 at 21:16
  • @Jose No. A instance-restricted login can only have database access if mapped to a database user, so if you don't want to give them the dbo user mapping then you must create another user. But see my second answer I've just added.
    – T.H.
    Feb 7 '17 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.