Protect table update using database role (server role is over)

Question

I have a database role named app_user with select, update, insert and delete rights on all tables except 2. On this 2 tables app_user role has only select right.

But, some users have right to create database users (using create login, sp_adduser, sp_addrolemember). To be able to execute this procedures they must have sysadmin server role. In this case sysadmin role is over database role app_user and they can update this 2 tables. Currently, I'm giving them securityadmin role but they can execute only create login while creating db users.

To summarize, I want to protect updating 2 tables for all users except my sysadmin account, and I want some users to be able to create database users (my app executes sp which grant db permission for that kind of users).

Answer 1

You might get a better answer on the DBA site, but it looks like you want to allow the users to execute 3 commands: CREATE LOGIN, CREATE USER and sp_addrolemember (because ALTER ROLE can't add members to a role in SQL Server 2008).

According to the documentation, the minimum permissions needed are:

• CREATE LOGIN - ALTER (ANY) LOGIN
• CREATE USER - ALTER ANY USER
• sp_addrolemember - ALTER permission on the role

So they shouldn't need either sysadmin or db_owner permissions, if you grant them the permissions listed above.