Monthly Archives: March 2017

db_owner permission

I was requested to give our development team db_owner permissions on every database. So I made this script this handle this request.

-- Create user on every database and add to the db_owner role
declare @LOGIN VARCHAR(50) = '<login_name, sysname, login_name>'
select 
 'use ['+name+'];'
+CHAR(10)+'CREATE USER ['+@LOGIN+'] FOR LOGIN ['+@LOGIN+'];'
+char(10)+ 'ALTER USER ['+@LOGIN+'] WITH DEFAULT_SCHEMA=[dbo];'
+char(10)+'use ['+name+']; exec sp_addrolemember @rolename = ''db_owner'', @membername = '''+@LOGIN+''''
+CHAR(10)
from sys.databases where database_id > 4