January 11, 2010 at 8:52 am
We have a db user which is used connect to database from front end application. Normal database operations performed by this user are execution of stored procedures and queries. The stored procedure contains statements to insert, update, selete and delete data, (some insertand update statement can invoke some triggers) creation of temp tables (# table) for temporary manipulation of data, creation of database user (using create login statement), deletion of database user (using drop login statement), changing password of database user (using alter login statement), adding and removing user to various groups (e.g. securityadmin group). The user is part of of db_owner and securityadmin group.
Can any body please advise if the user is removed from db_owner group what will the effects of it? and what precaution needs to taken if user is removed from db_owner group?
January 11, 2010 at 9:35 am
Removing that account from DB_Owner and SecurityAdmin is a good idea.
Most likely, you need to set up an Executor role that has the rights to execute stored procedures. That's pretty easy to do in SQL 2005. Then add that login to that role.
The way to work out the details would be to make the change in a development server or test server first, and test it thoroughly there, before making the changes on the live server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 11, 2010 at 9:44 am
what you want to do is create a few roles in your database first. once the roles are created, then you want to remove your user from the db_owner role, and put them in your new roles.
when a user is db_owner, there is not much they cannot do...including dropping the database, which is probably too much power. you'll want to prevent things like that.
you seemed a little unsure on rights for procedures, so remember this:
if you grant a user or role EXECUTE priviledges on a procedure, that person can run the procedure even if the proc inserts/updates/deletes data...
This is by design, and is very common in situations where no direct updates to tables are allowed, and only insert/update/delete is done thru procedures.
here's a code example of some roles; i'm creating a kind of "Readonly", an almost-admin that can modify tables and create procs, and a group that has read-write to the tables.
CREATE ROLE [ReallyReadOnly]
--give reader writes to this group
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReallyReadOnly]
--explicitly DENY access to writing to avoid multi roles giving more access than i want
ALTER AUTHORIZATION ON SCHEMA::[DB_DenyDataWriter] TO [ReallyReadOnly]
--logic to be aware of: by granting EXECUTE, the user can run any proc,
--even if that proc insert/deletes/truncates/updates.
--if you want them to not run procs, don't grant this.
GRANT EXECUTE TO [ReallyReadOnly]
--create the Role for my Dev guys
CREATE ROLE [DEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [DEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [DEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [DEVAdmins]
GRANT EXECUTE,ALTER TO [DEVAdmins]
--create role for my normal users
CREATE ROLE [DEVUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [DEVUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [DEVUsers]
GRANT EXECUTE TO [DEVUsers]
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply