How can I create a user with securityadmin role that can assign read/write rights on tables, execute rights on procedures?

Question

How can I create a user with securityadmin role that can assign read/write rights on tables, execute rights on procedures?

jaaxton

SSC-Addicted

Points: 465
More actions
November 11, 2013 at 8:54 am

#282244

Good day all,
How can I create a user with securityadmin role that can assign read/write rights on tables, execute rights on procedures?
I am a database security administrator in my organisation. I am created on databases as a user with securityadmin role and accessadmin and securityadmin privileges on databases. But whenever I try to create a login and user and grant read/write access to a database I get an error message saying that my user privilege is not enough to perform the action.
Tony

Viewing 9 posts - 1 through 8 (of 8 total)

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

arnipetursson SSCertifiable Points: 6557 More actions · Answer 1

Do you only have db_securityadmin role on the databases, or does your log have have the securityadmin fixed server role?

If you only have the db_securityadmin role on the databases, then you can only manage permissions of users within the databases,

and cannot create server logins

jaaxton SSC-Addicted Points: 465 More actions · Answer 2

Yes I have the securityadmin fixed server role and db_securityadmin role on the database. How do I go about this?

Regards.

arnipetursson SSCertifiable Points: 6557 More actions · Answer 3

From BOL: Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.

Exactly which task fails (login creation, Windows or SQL, adding users to database, granting roles or permissions?).

How are you connecting? via windows or SQL login. Could there be any "deny" permissions be applied to your login or windows group it belongs to?

jaaxton SSC-Addicted Points: 465 More actions · Answer 4

The task that fails is adding roles to users in databases e.g. datareader, datawriter, etc.

arnipetursson SSCertifiable Points: 6557 More actions · Answer 5

What error do you get?

Also, can you answer the other questions I asked

arnipetursson SSCertifiable Points: 6557 More actions · Answer 6

Is it possible your login has DENY "alter any user" on the database?

jaaxton SSC-Addicted Points: 465 More actions · Answer 7

jaaxton

SSC-Addicted

Points: 465

November 13, 2013 at 12:47 am

#1665936

Hello,

No it does not have a deny on it.

jaaxton SSC-Addicted Points: 465 More actions · Answer 8

I get an error that says:

"Add member failed for DatabaseRole db_datareader. An exception occurred while executing a Transact-SQL statement or batch. User does not have permission to perform this action."