0

Newbie here. I've got an SQL Server database, and I want to give someone permission to do almost anything to it except access a particular schema or mess with my user and my user rights. I see how to protect my schema, but how do I protect my user's rights?

Thanks

0

Since you question is very broad (give someone permission to do almost anything to it except access a particular schema or mess with my user and my user rights) I suggest you get yourself acquainted with built in database roles. Then you will be able to decide yourself what to do. Meaning which ones you make your user a member. Details here.

  • db_owner: Members have full access.
  • db_accessadmin: Members can manage Windows groups and SQL Server logins.
  • db_datareader: Members can read all data.
  • db_datawriter: Members can add, delete, or modify data in the tables.
  • db_ddladmin: Members can run dynamic-link library (DLL) statements.
  • db_securityadmin: Members can modify role membership and manage permissions.
  • db_bckupoperator: Members can back up the database.
  • db_denydatareader: Members can’t view data within the database.
  • db_denydatawriter: Members can’t change or delete data in tables or views.

Be mindful that there are server roles that if a login is a member of and if there is a user corresponding to that login, user can do certain things in the context of the database. One example is:

  • BulkAdmin: Any member can run the bulk insert command.

To deny permission on a schema see this.

  • Not very helpful. – uncaged Jul 3 '17 at 0:00
0

You should setup a role with permissions you wanted, and assign user to that role,so it would be easier for you to manage that user and permissions in general.

"Almost anything" would include alter database, but you have to be aware what the user can do.

You could add a user permissions such as :

GRANT ALTER ON DATABASE::YourDatabase TO TestUser
DENY ALTER,SELECT,UPDATE,DELETE ON SCHEMA::ForbiddenSchema to TestUser
DENY ALTER ANY USER TO TestUser 

Note that specifying database name you are limiting user only on that particular database.

To build your own permissions on a user/role take a look at this pdf: SQL Server Permissions Map

  • Thanks S4V1N. How do I grant Alter Any User to Testuser, but deny them the ability to mess with ForbiddenUser (and ForbiddenUser's permissions)? – uncaged Jul 2 '17 at 23:58
  • In order to do that you would need to assign all users to a certain role/roles. And allow that user to alter any role, and then deny him to alter specific role. – S4V1N Jul 3 '17 at 6:31

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.