CODEWITHSUNDEEP
sql-servert-sql
Chris James
Chris James

Reputation: 11711

Programmatically set a DB user to be db_owner

How can I assign the db_owner role to a user that I have created?

I am able to create a login and add them to the database. I don't know how to change their permission to db_owner using a SQL query.

I have a feeling I am maybe missing something with my query where I add the user to the database?

Here is the query to add the user to the database

CREATE USER [Driver-SOC-ChrisTest] FOR LOGIN [Driver-SOC-ChrisTest] 
WITH DEFAULT_SCHEMA=[dbo]

Upvotes: 20

Views: 39767

Answers (2)

SPE109
SPE109

Reputation: 2951

I quite often go into the GUI, make the changes I need and then rather than saving by pressing OK, I press the Script button at the top of the dialog and send it to a new window.

enter image description here

This would give you the code the previous poster provided.

Upvotes: 24

Andomar
Andomar

Reputation: 238166

To give the user DBO permissions:

EXEC sp_addrolemember N'db_owner', N'[Driver-SOC-ChrisTest]'

To make the user owner of the database (not advised):

EXEC sp_changedbowner N'[Driver-SOC-ChrisTest]'

Upvotes: 35

Related Questions