Programmatically set a DB user to be db_owner

Question

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]

See this answer for SQL Server 2008 http://stackoverflow.com/questions/3998634/sql-server-2008-how-do-i-grant-privileges-to-a-username — Richard Chambers, Nov 21 '13 at 02:02

score 33 · Accepted Answer · answered Jun 13 '11 at 14:22

33

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]'

answered Jun 13 '11 at 14:22

Andomar

232,371
49
380
404

score 24 · Answer 2 · edited Mar 25 '20 at 04:25

24

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.

This would give you the code the previous poster provided.

edited Mar 25 '20 at 04:25

G S

35,511
22
84
118

answered Jun 13 '11 at 14:47

SPE109

2,911
1
19
16

This is genius! How did I not know this? So useful. – AGB Sep 22 '16 at 11:14
Unfortunately cannot vote up more than once :P Too useful. – PM. May 15 '19 at 23:23
Very good tip. Not only does it solve the problem but now I will know how to execute many queries in SQL. – HenryGuillen17 Jun 20 '20 at 23:47

Programmatically set a DB user to be db_owner

2 Answers2