Grant dbo_owner role

Question

Post reply

Grant dbo_owner role

gary1

SSC-Insane

Points: 21152
More actions
August 16, 2011 at 5:16 pm

#255672

Hi,
We have SQL Server 2008 R2 instance with 85 database and I need to grant dbo_owner database role to user abc\scott for all 85 databases.
What is the easy way to do this?
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

JeremyE SSCoach Points: 16200 More actions · Answer 1

select 'USE ['+name +']' + CHAR(13)+'GO'+ CHAR(13)+

'CREATE USER [abc\scott] FROM LOGIN [abc\scott]'+ CHAR(13)+'GO'+ CHAR(13)+

'USE ['+name +']' + CHAR(13)+'GO'+ CHAR(13)+

'EXEC sp_addrolemember N''db_owner'',N''abc\scott''' + CHAR(13)+'GO'+ CHAR(13)

FROM sys.databases

WHERE database_id>4

Run the above with results to text and then run the script it creates.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

That is a fast and easy enough way to do it.

I would suggest creating a domain group and adding users to that domain group. Then add the domain group to the dbo_owner role in each of the databases. That will reduce admin time later.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

uncle_boris SSCommitted Points: 1588 More actions · Answer 3

I am not sure if it works on R2 but SSMS Tools provides an option to run a script across multiple databases on the same server very easily.

The script produced is all but the same as the previous post but if you like GUI's then it would do the trick as long as your initial script is correct.

gary1 SSC-Insane Points: 21152 More actions · Answer 4

select 'USE ['+name +']' + CHAR(13)+'GO'+ CHAR(13)+
'CREATE USER [abc\scott] FROM LOGIN [abc\scott]'+ CHAR(13)+'GO'+ CHAR(13)+
'USE ['+name +']' + CHAR(13)+'GO'+ CHAR(13)+
'EXEC sp_addrolemember N''db_owner'',N''abc\scott''' + CHAR(13)+'GO'+ CHAR(13)
FROM sys.databases
WHERE database_id>4

In above script why we are using the line CHAR(13)+'GO'+ CHAR(13)+ ?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Create a return line

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JeremyE SSCoach Points: 16200 More actions · Answer 6

CHAR(13) is used for carriage return.

http://msdn.microsoft.com/en-us/library/ms187323.aspx

gary1 SSC-Insane Points: 21152 More actions · Answer 7

select 'USE ['+name +']' + CHAR(13)+'GO'+ CHAR(13)+
'CREATE USER [abc\scott] FOR LOGIN [abc\scott]'+ CHAR(13)+'GO'+ CHAR(13)+
'USE ['+name +']' + CHAR(13)+'GO'+ CHAR(13)+
'EXEC sp_addrolemember N''db_owner'',N''abc\scott''' + CHAR(13)+'GO'+ CHAR(13)
FROM sys.sysdatabases
WHERE dbid>4

I'm able to generate the script using the above script in SQL Server 2008. But when executing the generated script, I'm getting the below error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'GO'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'GO'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'GO'.

Please advice

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Change your query results options from "results to grid" to "results to text"

That should fix it for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JeremyE SSCoach Points: 16200 More actions · Answer 9

Guess it needs the line feed also. This should do it.

select 'USE ['+name +']' + CHAR(13)+CHAR(10)+'GO'+ CHAR(13)+CHAR(10)+

'CREATE USER [abc\scott] FOR LOGIN [abc\scott]'+ CHAR(13)+CHAR(10)+'GO'+ CHAR(13)+CHAR(10)+

'USE ['+name +']' + CHAR(13)+CHAR(10)+'GO'+ CHAR(13)+CHAR(10)+

'EXEC sp_addrolemember N''db_owner'',N''abc\scott''' + CHAR(13)+CHAR(10)+'GO'+ CHAR(13)+CHAR(10)

FROM sys.sysdatabases

WHERE dbid>4

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

It will work either way (with or without the linefeed) - query results options play a difference though.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JeremyE SSCoach Points: 16200 More actions · Answer 11

SQLRNNR (8/22/2011)
It will work either way (with or without the linefeed) - query results options play a difference though.

I double checked in SSMS 2008R2 and it appears correct, however, if you copy paste the results when run as "Results to text" is selected it will error. You can just parse the results and SSMS will throw the syntax error.

gary1 SSC-Insane Points: 21152 More actions · Answer 12

Guess it needs the line feed also. This should do it.
select 'USE ['+name +']' + CHAR(13)+CHAR(10)+'GO'+ CHAR(13)+CHAR(10)+
'CREATE USER [abc\scott] FOR LOGIN [abc\scott]'+ CHAR(13)+CHAR(10)+'GO'+ CHAR(13)+CHAR(10)+
'USE ['+name +']' + CHAR(13)+CHAR(10)+'GO'+ CHAR(13)+CHAR(10)+
'EXEC sp_addrolemember N''db_owner'',N''abc\scott''' + CHAR(13)+CHAR(10)+'GO'+ CHAR(13)+CHAR(10)
FROM sys.sysdatabases
WHERE dbid>4

Thanks Jeremy..The above script worked..