Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: Access to all databases on a Server

    We have a requirement to run a script using generic account to confirm that all databases on various SQL Servers are up and running.

    I can write the vbscript for this requirement. The issue is what min priv can I give to the generic account used for the script so that it is able to connect to every database in the Server ? In Db2 I could have given the account connect authority to each database.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You can create a user with no specific permissions in each database. This user would inherit whatever permissions are granted to the public role. Typically (by best practices, anyway), this will be the system catalog, and that's it. To ensure that new databases are set up correctly, make sure this user is added to the model database, as well.

  3. #3
    Join Date
    Oct 2003
    Posts
    93
    MCrowley,
    Thanks for your response.
    I am exactly trying to avoid the solution you proposed. I do not want to create a user in each database as I have like 50+ database on each instance.

    Is there any possibility to do something on the server level ?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The only thing to be done at the server level is even worse. You have to add them to the sysadmin server group.

  5. #5
    Join Date
    Oct 2003
    Posts
    93
    Yep. The original report was running using an account with sysadmin priv. Now Audit doesn't want us to use that account. So trying to figure the alternatives.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Edit the following script to use the appropriate Windows Login, then log into SQL Server as a sysadmin and execute the script.
    Code:
    DECLARE @cWindowsLogin		sysname
    DECLARE @cCmd				NVARCHAR(999)
    
    SET @cWindowsLogin = 'YourDomain\YourLogin'
    SET @cCmd = 'EXECUTE [?]..sp_grantdbaccess ''' + @cWindowsLogin + ''''
    
    IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = @cWindowsLogin)
       EXECUTE master.dbo.sp_grantlogin @cWindowsLogin
       
    EXECUTE master.dbo.sp_MSforeachdb @cCmd
    This script will give the specified Windows Login the ability to log into the server, then grant it only the ability to connect to each of the databases on that server... Connect privleges, and nothing more. Because the script grants access to the model database which is the template for creating new databases, the windows login will automatically have access to every database created after the script is run.

    -PatP

  7. #7
    Join Date
    Oct 2003
    Posts
    93
    Pat,
    Thanks a ton for the script.
    As Mcrowley pointed out, the only 2 options are :
    a) use a sysadmin account for the server
    b) or Use a generic account for the server and have a corresponding user in each database with min priv like public role.

    Your script helped a lot. I ended up creating a vbscript which takes a list of 40 servers as input. It goes ahead and creates a login if not there and then it creates a user in every non system database.

    Instead of sp_grandbaccess, I used the "create User" syntax for 2 reasons. Sp_grantdbaccess wont work in future versions and it creates a schema with the same name as the user in SQL 2005. With "Create User" syntax, the default schema is dbo.

    thanks again.

Posting Permissions

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