Re: Security Script

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 08/21/03 

Date: Thu, 21 Aug 2003 10:50:09 -0500

Restoring or attaching a database does not alter permissions in any way.
Logins (which are stored in the master database) are not created or
altered during a database restore/attach. However, users (stored in
your user database) are restored with the database. This can result in
a mismatch between logins and users on the new server and be symptomatic
of permission loss.

sp_change_users_login can correct the login/user mapping problem. Check
out MSKB 240872 for more info:
http://support.microsoft.com/default.aspx?kbid=240872 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Sammy The Bull" <NoOne@NoWhere.com> wrote in message
news:e7TKCM$ZDHA.652@tk2msftngp13.phx.gbl...
> Don't think that's it.
>
> You when you restore a database, or attach it, SQL Server maintains
the
> logins but removes all permissions to databases and their objects.
There
> apparently is a script that assists in restoring access to databases,
and
> permissions to the objects.
>
> "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> news:unobRQ#ZDHA.2032@TK2MSFTNGP10.phx.gbl...
> > Are your referring to sp_change_users_login?  You can find
documentation
> > for this procedure in the Books Online
> > <tsqlref.chm::/ts_sp_ca-cz_8qzy.htm>.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > -----------------------
> > SQL FAQ links (courtesy  Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > -----------------------
> >
> > "Sammy The Bull" <NoOne@NoWhere.com> wrote in message
> > news:eZYGmF%23ZDHA.1872@TK2MSFTNGP12.phx.gbl...
> > > This has probably been asked a million times but...apparently
there is
> > a
> > > script available on microsoft.com that will assist in re-setting
up
> > security
> > > on SQL Server after a restore/sp_attach_db...does anyone know
where I
> > can
> > > find it, or what to search under?
> > >
> > > Thanks.
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: object level permissions being lost when migrating
    ... While I understand that the permissions are at the ... Database level, that is why I am not understanding why I have an issue. ... Moving Users ... Errors After Restoring Dump ...
    (microsoft.public.sqlserver.security)
  • Re: Application Role
    ... >database except through the application role. ... have created logins ... >then you need to remove permissions to access the ... >can be cut and pasted into Query Analyzer is appreciated. ...
    (microsoft.public.sqlserver.security)
  • Re: loss of permissions
    ... When you restore your database, you lose the permissions that had been ... Your problem is that your database's permissions (users, roles, and grants) ... are not the same as the database that you are restoring from. ...
    (microsoft.public.sqlserver.dts)
  • Re: Application Role
    ... One additional point--application roles are also members of the public ... database role, so they will have any permissions granted to public. ... >have created logins ...
    (microsoft.public.sqlserver.security)
  • SQLDMO: howto obtain logical file names from a BAK file
    ... what, how, when does a logical file name is determine for a database? ... If I am restoring a db from Enterprise Manager, under the Options tab, ... the actual BAK file, but I haven't figured that out yet. ... file comes from another SQL server, was created in another folder, so ...
    (microsoft.public.sqlserver.programming)