We have a test database and some test logins that we would like to give db_owner access to through a script. Usually we would have to go into logins and right click on the username and go to user mapping and select the database to associate it with and give it owner access and click OK.
Creating a user requires alter any user permission, or membership of the db_accessadmin or db_owner database roles. A database owner is a member of the db_owner role by definition. Creating a login requires the alter any login privilege.
A member of the db_owner fixed database role will have SELECT , INSERT , UPDATE , and DELETE permissions on the database. Amongst many other permissions, but yes, that's correct.
Click the Database Access tab. In the list at the top, in the Permit column, select the check box for the database to which you want to assign the owner role for the CES administrative account. In the Permit in Database Role list, select db_owner. Click OK.
You need to do two things, both running in the context of the target database (i.e., execute USE (database)
first):
CREATE USER [LoginName] FOR LOGIN [LoginName]
EXEC sp_addrolemember N'db_owner', N'LoginName'
In general, if you have SQL Server Management Studio 2005 or higher, you can go into the UI for an operation, fill out the dialog box (in this case, assigning the user to the database & adding roles), and then click the "Script" button at the top. Instead of executing the command, it will write a script for the action to a new query window.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With