Create a New Login

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  September 3, 2010 at 10:14 am

  #144955

  Hello Everyone

  I am creating a couple new logins on a Database box. I want to limit what they can do, so I have added them only to the "dbcreator" database role. But there are hundreds of databases on this box. How can I script this to give dbo role membership for each database? While still granting the login as a "dbcreator" role?

  I have a few databases boxes like this, and I would rather not have to perform this task by hand in the future.

  Thanks in advance

  Andrew SQLDBA

• magasvs

  SSCertifiable

  Points: 7660

  More actions

  September 4, 2010 at 11:01 am

  #1216546

  You can generate script using this query:

  use master

  go

  SELECT 'EXEC [' + name + '].dbo.sp_grantdbaccess ''login'', ''userNameInDB''' from sysdatabases

  SELECT 'EXEC [' + name + '].dbo.sp_addrolemember ''db_owner'', ''SuperUserLogin''' from sysdatabases

  Run the output to add user to each database as db_owner.