June 21, 2012 at 6:24 am
How can i assign dbo/db_owner permissions to a user ?
June 21, 2012 at 6:27 am
Look sp_addrolemember for script way of doing it
Or just use SSMS to alter the user in the security folder and grant db_owner on the user in the database,
But are you sure you want to give them DBO rights?
June 21, 2012 at 6:46 am
Will this script do:
sp_addrolemember 'db_owner', 'user_name'
I want to grant my database developers dbo permissions on development environment.
June 21, 2012 at 6:47 am
Yes looks fine to me.
June 21, 2012 at 7:08 am
Adding Database Users to the db_owner Role will work fine however this is a good time to start thinking about User-defined Database Roles. I like to only grant permissions, including via Fixed Database Role Membership, to a User-defined Database Role. Given that we are talking about db_owner Role (not the same as dbo) it may seem redundnant, but even in this case it is useful. Consider a case when you want to temporarily, or even permanently, downgrade what your developers can do in the database. You can simply remove your User-defined Role from the db_owner Role without affecting who belongs to your logical "developer" grouping, then easily add it back, or change their permissions as a group.
Might I recommend creating a new User-defined Database Role in your database called Developers, adding all your developers into the Developers Role, and then adding the Developers Role to the db_owner Fixed Role. Like so:
USE [YourDatabaseHere]
GO
-- create new role for your developers to belong to
CREATE ROLE Developers
GO
-- add Developers Role to db_owner
EXEC sys.sp_addrolemember
@rolename = N'db_owner',
@membername = N'Developers';
GO
-- add your developers to your Developers role
EXEC sys.sp_addrolemember
@rolename = N'Developers',
@membername = N'Sally';
EXEC sys.sp_addrolemember
@rolename = N'Developers',
@membername = N'Jim';
-- etc.
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2012 at 2:12 am
Hello opc.three. I agree with you. Even follow the same rules for providing DB permissions. This was required for temporary basis so it worked fine:
sp_addrolemember 'db_owner', 'user_name'
Thanks to all for helping me out.
July 23, 2012 at 5:59 am
Just seen this post but need to clarify.
There is a difference between the user\schema dbo and the database role Db_Owner.
A user who is the owner of a database will map into the db directly as the user dbo and will automatically inherit DB_owner permissions. There will be no database user for that particular login.
For example
Create login Myapp_user and set the login as the database owner.
The login now maps to the db via dbo, there will be no database user Myapp_user, the login Myaap_user will have have full owner permissions on the database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply