Re: Same Server and DB - Copy users and roles

From: Ray Higdon (sqlhigdon_at_nospam.yahoo.com)
Date: 01/12/04

  • Next message: Kevin Evans: "Gen Script - Include Role Permissions" 
    Date: Sun, 11 Jan 2004 19:12:10 -0500

    This page may help:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;246133 

    -- 
Ray Higdon MCSE, MCDBA, CCNA
---
"Kevin Evans" <kevin@convenientcomputing.com> wrote in message
news:eJUZvmH2DHA.1396@TK2MSFTNGP10.phx.gbl...
> 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: Kevin Evans: "Gen Script - Include Role Permissions"

    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 on ... the roles were not transferred along with the role to the new database. ...
      (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