Post reply

browse DBs

  • September 15, 2005 at 1:29 pm

    #168736

    I am trying to create a sp that would jump from one DB to another using the USE command. I built a string and then I try to run EXECUTE(@SQLString) with no lock .  This is the sample code I am using:

    declare @DBName varchar(200)

    declare @SQlString varchar(300)

    set @DBName='model'

    set @SQlString = 'use '+@DBName

    execute(@SQlString)

    Is there any way I can accomplish that?

    Thanks

    Carlos

  • September 15, 2005 at 1:36 pm

    #590324

    check this out :

    exec sp_msforeachdb 'Select ''?'' as dbName, * from ?.dbo.SysObjets'

  • September 16, 2005 at 1:34 pm

    #590740

    I tried that but didn't serve my purpose. What I am actually trying to accomplish is to run a sp that is in the master Db so that I can assign database roles to all the dadabases at once. This is the sp :

    execute sp_addrolemember 'db_owner','adcnt\SQLAdmins'

    execute sp_addrolemember 'db_owner','adcnt\SQLFull'

    execute sp_addrolemember 'db_owner','adcnt\SQLRW'

    execute sp_addrolemember 'db_datareader','adcnt\SQLR'

    GO

    using sp_MSforeachdb only changes the first DB.

    Any suggestions?

     

     

  • September 16, 2005 at 1:40 pm

    #590742

    What statement did you try to run exactly?

  • September 16, 2005 at 1:44 pm

    #590743

    I run a sp that is in the Master DB with those 4 statements:

    execute sp_addrolemember 'db_owner','adcnt\SQLAdmins'

    execute sp_addrolemember 'db_owner','adcnt\SQLFull'

    execute sp_addrolemember 'db_owner','adcnt\SQLRW'

    execute sp_addrolemember 'db_datareader','adcnt\SQLR'

    GO

    and I run this from the query analizer pointing to the master db

    EXEC sp_MSforeachdb @command1="sp_Create_DBUsers"

    as I said before it only created the roles for the first DB

     

  • September 16, 2005 at 9:32 pm

    #590780

    execute that string in the foreach sp :

    'use ?

    execute sp_addrolemember ''db_owner'',''adcnt\SQLAdmins''

    execute sp_addrolemember ''db_owner'',''adcnt\SQLFull''

    execute sp_addrolemember ''db_owner'',''adcnt\SQLRW''

    execute sp_addrolemember ''db_datareader'',''adcnt\SQLR'''

  • September 17, 2005 at 9:20 am

    #590797

    THANKS SO MUCH !!! Worked like a charm.

  • September 19, 2005 at 7:13 am

    #590903

    HTH.

Viewing 8 posts - 1 through 7 (of 7 total)

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