February 5, 2009 at 7:56 pm
Dear all,
I need to create a new SQL user : secu_opr, it has the db_securityadmin role because it is only used for database role and user login maintenace.
I don't want this secu_opr user able to select, insert, delete, update, execute any tables and stored procedures.
I typed : REVOKE ALL FROM secu_opr, but it can still SELECT and INSERT data to any tables.
I couldn't un-check its public role.
As I will use the sqlcmd command to allow our non-IT colleagues to add SQL users :
sqlcmd -S "???" -U "???" -P "???" -d "???" -Q "EXEC new_dbrole"
Of course, I will grant the EXECUTE right on the new_dbrole sp to secu_opr, but other than this, I don't want it has any db access rights.
Is it possible ?
February 5, 2009 at 8:44 pm
What rights does the public role have?
Is it a member of db_datareader and db_datawriter?
Is it a member of db_owner?
Does it have rights at the schema or database level?
The REVOKE syntax must include an object. For instance:
REVOKE ALL ON MyTable FROM secu_opr
The ALL just means SELECT, INSERT, UPDATE, and DELETE.
K. Brian Kelley
@kbriankelley
February 5, 2009 at 9:32 pm
Just for this reason you never want to assign rights to public. Create new roles for every group of users.
As Brian suggested, check and be sure that this group doesn't have other rights being assigned.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply