locked
DB owner permission RRS feed

  • Question

  • Hi,

    I have around 1000 databases, I need to give db_owner permission to all databases, How to give it without logging into each db. I need short method that should apply for all db's 

    Saturday, December 14, 2013 8:29 AM

Answers

  • What version are you using ? http://msdn.microsoft.com/en-us/library/ms189775.aspx

    Sorry ,cannot test it right now

    SELECT

    ' USE '+ name + ' EXEC sp_addrolemember N''db_owner'', N''LoginName''' FROM


    sys

    .databases


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Saturday, December 14, 2013 8:58 AM
    Answerer
  • I have around 1000 databases, I need to give db_owner permission to all databases, How to give it without logging into each db. I need short method that should apply for all db's 

    Below is a tweaked version of Uri's script which will generate a script to change the owner of all non-system databases on the server and enclose database names in case the name doesn't conform to normal identifier naming rules.  This also uses a cursor to run the script for each row.

    If the databases are on different servers, you could use the multi-server query feature of SSMS to run against all the registered servers in an SSMS group.  Select the parent group in the SSMS registered servers list then New Query.  The script can then be run against all servers in the group in a single operation.

    DECLARE @DatabaseOwnerChangeScript nvarchar(MAX);
    
    DECLARE DatabaseOwnerChangeScripts CURSOR STATIC FOR
    	SELECT
    		N'USE '+ QUOTENAME(name) + N'; EXEC sp_addrolemember N''db_owner'', N''LoginName'';'
    	FROM sys.databases
    	WHERE
    		name NOT IN(N'master', N'model', N'tempdb', N'msdb');
    
    OPEN DatabaseOwnerChangeScripts;
    
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM DatabaseOwnerChangeScripts INTO @DatabaseOwnerChangeScript;
    	IF @@FETCH_STATUS = -1 BREAK;
    	RAISERROR (@DatabaseOwnerChangeScript, 0, 0) WITH NOWAIT;
    	EXEC sp_executesql @DatabaseOwnerChangeScript;
    END;
    
    CLOSE DatabaseOwnerChangeScripts;
    DEALLOCATE DatabaseOwnerChangeScripts;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Saturday, December 14, 2013 3:24 PM
    Answerer

All replies

  • What version are you using ? http://msdn.microsoft.com/en-us/library/ms189775.aspx

    Sorry ,cannot test it right now

    SELECT

    ' USE '+ name + ' EXEC sp_addrolemember N''db_owner'', N''LoginName''' FROM


    sys

    .databases


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Saturday, December 14, 2013 8:58 AM
    Answerer
  • I have around 1000 databases, I need to give db_owner permission to all databases, How to give it without logging into each db. I need short method that should apply for all db's 

    Below is a tweaked version of Uri's script which will generate a script to change the owner of all non-system databases on the server and enclose database names in case the name doesn't conform to normal identifier naming rules.  This also uses a cursor to run the script for each row.

    If the databases are on different servers, you could use the multi-server query feature of SSMS to run against all the registered servers in an SSMS group.  Select the parent group in the SSMS registered servers list then New Query.  The script can then be run against all servers in the group in a single operation.

    DECLARE @DatabaseOwnerChangeScript nvarchar(MAX);
    
    DECLARE DatabaseOwnerChangeScripts CURSOR STATIC FOR
    	SELECT
    		N'USE '+ QUOTENAME(name) + N'; EXEC sp_addrolemember N''db_owner'', N''LoginName'';'
    	FROM sys.databases
    	WHERE
    		name NOT IN(N'master', N'model', N'tempdb', N'msdb');
    
    OPEN DatabaseOwnerChangeScripts;
    
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM DatabaseOwnerChangeScripts INTO @DatabaseOwnerChangeScript;
    	IF @@FETCH_STATUS = -1 BREAK;
    	RAISERROR (@DatabaseOwnerChangeScript, 0, 0) WITH NOWAIT;
    	EXEC sp_executesql @DatabaseOwnerChangeScript;
    END;
    
    CLOSE DatabaseOwnerChangeScripts;
    DEALLOCATE DatabaseOwnerChangeScripts;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Saturday, December 14, 2013 3:24 PM
    Answerer
  • Dan, check out this forum thread: http://social.technet.microsoft.com/Forums/sqlserver/en-US/64ad4f52-2fd8-4266-b4a4-5657c8870246/needed-more-answerers?forum=sqlgetstarted

    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, December 20, 2013 12:30 AM