Extracting Users for restore

I'm refreshing (restoring) a database and would like to keep the users from
the original database. Is there a efficient way to extract users, restore
the database and then re-apply the users. I'm not talking about logins as
I'm familiar with sp_help_revlogin.
Thanks.
Ron
The Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron
|||The Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron
|||Ron,
You can get the information from the database prior to the restore and save
it somewhere for scripting back into the database later.
For SQL 2000:
sp_helpuser
sp_helprotect
Although these still work in SQL 2005, they miss some securables. You can
also look at the new dynamic management views. Start by examining:
sys.database_permissions
sys.database_principals
sys.database_role_members
Here are a couple of queries that may help you get started.
-- Permissions
select u.name, p.permission_name, p.class_desc,
object_name(p.major_id) ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
-- Role Memberships
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u
on m.role_principal_id = u.principal_id
join sys.database_principals u2
on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron
|||No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from a
database, then 2) have that database refreshed from another databases, then
3) re-apply the users from step 1.
"Andrew J. Kelly" wrote:

> The Users stay with the db when you back it up and will be there after the
> restore.You may need to remap the login to the users an can do that with
> sp_change_users_login. Here is a bunch of info related to that topic.
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
> system dbs 2005
> http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
> system DB's 2000
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> Moving Users
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
> http://www.support.microsoft.com/?id=320125 Moving a Diagram
> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
> http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
> Roles and Permissions
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
>
>
|||You can script the users with Enterprise Manager. Right click on the db and
choose All tasks - Generate SQL Scripts. One of the tabs has an option to
script users and permissions for the db.
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...[vbcol=seagreen]
> No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from
> a
> database, then 2) have that database refreshed from another databases,
> then
> 3) re-apply the users from step 1.
> "Andrew J. Kelly" wrote:
|||Thanks Andrew - it seems to work fine in SQL2000. However in SQL2005, I
don't see where the database roles get extracted.
For example here's the output from SQL2000 extracting users and database
roles:
if not exists (select * from dbo.sysusers where name = N'abrown')
EXEC sp_grantdbaccess N'abrown'
GO
exec sp_addrolemember N'db_datareader', N'abrown'
GO
exec sp_addrolemember N'db_datawriter', N'abrown'
GO
Here's the output from SQL2005 extracting users and schemas (no mention of
extracint db roles):
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'abrown')
EXEC sys.sp_executesql N'CREATE SCHEMA [abrown] AUTHORIZATION [abrown]'
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'abrown')
CREATE USER [abrown] FOR LOGIN [abrown] WITH DEFAULT_SCHEMA=[abrown]
GO
I don't see how in SQL2005 users get added to datareader?
Thanks
Ron
"Andrew J. Kelly" wrote:

> You can script the users with Enterprise Manager. Right click on the db and
> choose All tasks - Generate SQL Scripts. One of the tabs has an option to
> script users and permissions for the db.
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...
>
>