locked
Permissions to be able to script database RRS feed

  • Question

  • Hello,
    we have a server running a SQL 2000 database and some user's (who belong to an AD group) need to be able to Script the database but their current permissions won't allow it. What is the minimum level of access I need to grant tso they will be able to use it? I cannot just grant sysadmin in this case. They are alrady in dbcreator role but this doesn't allow them to script a database.

    Any help greatly appreciated

    Ded
    In Vegas, you get a million mexicans standing in a line flipping "lady" cards at you.
    Friday, August 28, 2009 12:57 PM

Answers

  • db_datareader is a single database role. I have hundreds of databases on this particular server and I would like to grant a server role - not just specific to a single database
     
    By considering the above case, You can implement the below action plan

    Action Plan:

    1. Create a windows group from OS side (Go to Local Users and Groups)
    2. Add this particular windows group SQL Server Login.
    3. Take the outout of the below query and run, This will grant the db_datareader database level role to all the user databases in your server.     
    SELECT 'USE '+ NAME + CHAR(13)+ 'GO' + CHAR(13) + 'exec sp_addrolemember ''db_datareader'', ''DBScripter''' + CHAR(13) + 'GO' FROM master..SYSDATABASES WHERE DBID >4
    If you want to give access to new user you can add that particular user to windows group so that he will get access.

    Here after if new databases added to server you need to access to that particular database (the above query you can filter with where clause) and run.

    The above process is very simple for maintenance.

    Please let me know if you have any questions.


    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    Sunday, August 30, 2009 7:37 PM

All replies

  • Hi Ded

    Granting db_datareader database role allows the scripting of objects. I don't know if there's a less powerful option

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Friday, August 28, 2009 2:51 PM
  • db_datareader is a single database role. I have hundreds of databases on this particular server and I would like to grant a server role - not just specific to a single database
    In Vegas, you get a million mexicans standing in a line flipping "lady" cards at you.
    Friday, August 28, 2009 3:03 PM
  • There isn't a server level role for that.  The only server level role with access to perform operations in a database is sysadmin.  Sysadmin really only has this authority, because sysadmin is automatically mapped to the db_owner role in every database on the instance.  So, you are going to have to grant the permissions at a database level.  In SQL Server 2000, the minimum level of permission would be db_datareader.  In SQL Server 2005 and above, it would be VIEW DEFINITION.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Sunday, August 30, 2009 6:38 PM
  • db_datareader is a single database role. I have hundreds of databases on this particular server and I would like to grant a server role - not just specific to a single database
     
    By considering the above case, You can implement the below action plan

    Action Plan:

    1. Create a windows group from OS side (Go to Local Users and Groups)
    2. Add this particular windows group SQL Server Login.
    3. Take the outout of the below query and run, This will grant the db_datareader database level role to all the user databases in your server.     
    SELECT 'USE '+ NAME + CHAR(13)+ 'GO' + CHAR(13) + 'exec sp_addrolemember ''db_datareader'', ''DBScripter''' + CHAR(13) + 'GO' FROM master..SYSDATABASES WHERE DBID >4
    If you want to give access to new user you can add that particular user to windows group so that he will get access.

    Here after if new databases added to server you need to access to that particular database (the above query you can filter with where clause) and run.

    The above process is very simple for maintenance.

    Please let me know if you have any questions.


    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    Sunday, August 30, 2009 7:37 PM