Results 1 to 2 of 2
  1. #1
    Join Date
    May 2016
    Posts
    1

    Unanswered: T-SQl Script Help Please

    Hello
    I would like to achieve the following .
    List all databases with the help of T - SQL , and then appropriate role ( with exec sp_addrolemember ) assign .
    Unfortunately it does not work as I want. Can someone help?
    my script

    DECLARE @LoopCounter INT = 8, @DatabaseID INT,
    @DatabaseName NVARCHAR(100)

    SELECT @DatabaseID = max(dbid)
    FROM master.dbo.sysdatabases

    WHILE(@LoopCounter IS NOT NULL
    AND @LoopCounter <= @DatabaseID)
    BEGIN
    SELECT @DatabaseName = name
    FROM master.dbo.sysdatabases WHERE dbid = @LoopCounter
    print @DatabaseName
    exec sp_addrolemember 'SPExecuter', [User] //here is my Problem, i think.
    SET @LoopCounter = @LoopCounter + 1
    END

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    DECLARE @LoopCounter INT = 8, @DatabaseID INT,
    @DatabaseName NVARCHAR(100)
    declare @commandstring nvarchar(1000)
    
    SELECT @DatabaseID = max(dbid)
    FROM master.dbo.sysdatabases
    
    WHILE(@LoopCounter IS NOT NULL
    AND @LoopCounter <= @DatabaseID)
    BEGIN
    SELECT @DatabaseName = name
    FROM master.dbo.sysdatabases WHERE dbid = @LoopCounter
    print @DatabaseName
    set @commandstring = 'use [' + @DatabaseName + '];exec sp_addrolemember ''SPExecuter'', [User]'  //here is my Problem, i think.
    print @commandstring -- printed for troubleshooting/testing purposes
    -- exec (@commandstring) -- uncomment to execute
    SET @LoopCounter = @LoopCounter + 1
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •