Re: Same Server and DB - Copy users and roles

From: Kevin Evans (kevin_at_convenientcomputing.com)
Date: 01/11/04

  • Next message: pedro j.: "only 1 row from a join" 
    Date: Sun, 11 Jan 2004 11:33:35 -0800

    Thanks Dejan

    The script generator worked great with one exception. The permissions set on
    the roles were not transferred along with the role to the new database. Is
    there some additional setting on permission I need to complete this?

    I'm using SQL2K and I'm logged in as the administrator

    Thanks Again
    Kevin

    "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si> wrote in
    message news:eP$NdMF2DHA.3416@tk2msftngp13.phx.gbl...
    > > I have an application that requires a different database for each year
    of
    > > data. I have the same users for all years at a given time. Logins are
    not
    > a
    > > problem because they are on the same server but how do I automate the
    user
    > > and role updates made in the current years database to all other
    > databases.
    >
    > You can create scripts (in fact, you could change the permissions with a
    > script in the first place) with EM or DMO and implement them in other
    > databases. Check the "Documenting and Scripting Databases" topic in Books
    > OnLine.
    >
    > > I have hundreds of users and tables to set various securities on and
    have
    > to
    > > replicate the permission changes across all dbs. In fact, after a number
    > of
    > > years I need to change users to read only on most tables.
    >
    > You can generate a script using a cursor and dynamic SQL to automate the
    > permissions management. Instead of a cursor, you can help yourself with
    the
    > sp_MSforeachtable system SP. Check the article at
    > http://www.databasejournal.com/features/mssql/article.php/1490661.
    >
    > --
    > Dejan Sarka, SQL Server MVP
    > Please reply only to the newsgroups.
    >
    >

  • Next message: pedro j.: "only 1 row from a join"

    Relevant Pages

    • Re: Knowledge Base Article - 815154 Configure SQL Server Security for .NET Applications
      ... db_ roles were inclusive in this context and I'll find the script very ... >> Do I as the admin have to check each and every object one at a time? ... > your application references nor what permissions ... > the user to the fixed db_datareader database role. ...
      (microsoft.public.sqlserver.security)
    • Re: Same Server and DB - Copy users and roles
      ... > The script generator worked great with one exception. ... The permissions set ... > the roles were not transferred along with the role to the new database. ... >> Dejan Sarka, SQL Server MVP ...
      (microsoft.public.sqlserver.security)
    • Re: Permission denied
      ... Server), for example, installs by default without network support, meaning ... attempt to connect to the database. ... then it must be permissions. ... script manually and not as a scheduled task. ...
      (microsoft.public.windows.server.scripting)
    • Re: Copy database user
      ... What BP recommends is the right procedure to transfer objects from database ... object permissions are only transferred if the object ... existing object and the script will fail. ... > If you are using SQL Server 7.0, check out the section "Transferring Data ...
      (microsoft.public.sqlserver.security)
    • Re: List Users Permissions down to table.column action
      ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
      (microsoft.public.sqlserver.security)
    • Quantcast