Determine Report Permissions with T-SQL

Is there a way, using the system tables in the ReportServer or
ReportServerTempDB to determine which Active Directory groups have access to
which reports. I want to document what I would otherwise have to go
report-by-report in the Report Manager screen to see. I've looked at
several of the system tables (Users, DataSource, Policies, Roles,
ConfigurationInfo, Catalog) but have had no luck.You need to use the GetPolicies and SetPolicies methods on the web service.
You don't want to do anything directly to the database.
Here's a code snippet giving you an idea of how to use these methods:
private bool AddUserToFolderPolicy(string folder, string user, ref string
errMessage)
{
try
{
//Get the Browser role
Role[]roles = m_ReportingService.ListRoles();
Role browserRole = new Role();
foreach (Role r in roles)
{
if (r.Name == "Browser") browserRole = r;
break;
}
Role[] policyRoles = new Role[1];
policyRoles[0] = new Role();
policyRoles[0] =browserRole;
//Get the current policies of the folder in question
string path = "/" + folder;
bool inheritParent = false;
Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
inheritParent);
//If the user is currently in the current policy set just return
for(int i=0;i<currentPolicies.Length;i++)
if(currentPolicies[i].GroupUserName == user)
return true;
//Create the new policy array and add the new user
ArrayList arrPolicies = new ArrayList(currentPolicies);
Policy p = new Policy();
p.GroupUserName = user;
p.Roles = policyRoles;
arrPolicies.Add(p);
Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
//Set the policies
m_ReportingService.SetPolicies(path,finalPolicies);
}
catch (Exception e)
{
errMessage = e.Message;
return false;
}
return true;
}
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> Is there a way, using the system tables in the ReportServer or
> ReportServerTempDB to determine which Active Directory groups have access
> to
> which reports. I want to document what I would otherwise have to go
> report-by-report in the Report Manager screen to see. I've looked at
> several of the system tables (Users, DataSource, Policies, Roles,
> ConfigurationInfo, Catalog) but have had no luck.
>|||Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
developer and need to accomplish this task in T-SQL. I don't really want to
"do" anything to the tables, I just want to "get" something from them, just
as I would a system table in master or anywhere else in SQL Server. Does
anyone ([MSFT] people perhaps?) know if this is possible or if there is any
documentation on how to navigate these tables?
"Adrian M." wrote:
> You need to use the GetPolicies and SetPolicies methods on the web service.
> You don't want to do anything directly to the database.
> Here's a code snippet giving you an idea of how to use these methods:
> private bool AddUserToFolderPolicy(string folder, string user, ref string
> errMessage)
> {
> try
> {
> //Get the Browser role
> Role[]roles = m_ReportingService.ListRoles();
> Role browserRole = new Role();
> foreach (Role r in roles)
> {
> if (r.Name == "Browser") browserRole = r;
> break;
> }
> Role[] policyRoles = new Role[1];
> policyRoles[0] = new Role();
> policyRoles[0] =browserRole;
> //Get the current policies of the folder in question
> string path = "/" + folder;
> bool inheritParent = false;
> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
> inheritParent);
> //If the user is currently in the current policy set just return
> for(int i=0;i<currentPolicies.Length;i++)
> if(currentPolicies[i].GroupUserName == user)
> return true;
> //Create the new policy array and add the new user
> ArrayList arrPolicies = new ArrayList(currentPolicies);
> Policy p = new Policy();
> p.GroupUserName = user;
> p.Roles = policyRoles;
> arrPolicies.Add(p);
> Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
> //Set the policies
> m_ReportingService.SetPolicies(path,finalPolicies);
> }
> catch (Exception e)
> {
> errMessage = e.Message;
> return false;
> }
> return true;
> }
>
> --
> Adrian M.
> MCP
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> > Is there a way, using the system tables in the ReportServer or
> > ReportServerTempDB to determine which Active Directory groups have access
> > to
> > which reports. I want to document what I would otherwise have to go
> > report-by-report in the Report Manager screen to see. I've looked at
> > several of the system tables (Users, DataSource, Policies, Roles,
> > ConfigurationInfo, Catalog) but have had no luck.
> >
>
>|||Microsoft doesn't support directly access to the Report Server DB (including
queries). Supported access is through the tools provided (Report Manager,
web service, etc...)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
--
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>|||Even if we were to document the tables, there is no way to use TSQL to get
this (without some extended SPs). We use Windows APIs to resolve group
membership and determine effective permissions from the ACL we store in the
database.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>

detach/attach loses permissions

Hi, I was wondering if anyone had insight on this. I have traditionally
always used detach/attach as a method of copying databases i.e.:
I have 2 servers running SQL 2000: serverA, serverB
My intention is to copy a database (call it DB1) from server1 to server2
I perform the following:
1. i detach DB1
2. copy it (call it DB2)
3. attach DB1 to Server1 and DB2 to server2
4. place both server1 and server2 in single user mode via the -T3608
parameter
5. detach model THEN msdb from Server1, copy them and move the copies to
server2
6. attach model THEN msdb to server1 and server2
7. remove the -T3608 parameter and restart both sql services
8. script out the logins on server1 and apply them to server2
Now that is all fine according to microsofts article:
http://support.microsoft.com/default...b;en-us;224071
However what i have noticed at times is that on server2, the logins are
missing permissions to DB2. Why does this happen?
Can things go missing if i perform it in this order:
1. place both server1 and server2 in single user mode via the -T3608
parameter
2. detach model THEN msdb from Server1, copy them and move the copies to
server2
3. attach model THEN msdb to server1 and server2
4. remove the -T3608 parameter and restart both sql services
5. i detach DB1
6. copy it (call it DB2)
7. attach DB1 to Server1 and DB2 to server2
8. script out the logins on server1 and apply them to server2
?
Missing permissions occured when i did the above for our greatplains
database.
Any help appreciated.
Cheers, john
SQL Server Logins are mapped to DB Users via an ID that can be different on
both servers, especially when they are not recreated in the exact order on
both servers. You need to use sp_change_users_login to fix the mapping
after the attach and after you recreate the logins. These links might be of
help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
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://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/colu...rdatabases.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/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"john clarke" <jclarke@.nospam.com> wrote in message
news:%23JAspriBGHA.3896@.TK2MSFTNGP09.phx.gbl...
> Hi, I was wondering if anyone had insight on this. I have traditionally
> always used detach/attach as a method of copying databases i.e.:
> I have 2 servers running SQL 2000: serverA, serverB
> My intention is to copy a database (call it DB1) from server1 to server2
> I perform the following:
> 1. i detach DB1
> 2. copy it (call it DB2)
> 3. attach DB1 to Server1 and DB2 to server2
> 4. place both server1 and server2 in single user mode via the -T3608
> parameter
> 5. detach model THEN msdb from Server1, copy them and move the copies to
> server2
> 6. attach model THEN msdb to server1 and server2
> 7. remove the -T3608 parameter and restart both sql services
> 8. script out the logins on server1 and apply them to server2
> Now that is all fine according to microsofts article:
> http://support.microsoft.com/default...b;en-us;224071
> However what i have noticed at times is that on server2, the logins are
> missing permissions to DB2. Why does this happen?
> Can things go missing if i perform it in this order:
> 1. place both server1 and server2 in single user mode via the -T3608
> parameter
> 2. detach model THEN msdb from Server1, copy them and move the copies to
> server2
> 3. attach model THEN msdb to server1 and server2
> 4. remove the -T3608 parameter and restart both sql services
> 5. i detach DB1
> 6. copy it (call it DB2)
> 7. attach DB1 to Server1 and DB2 to server2
> 8. script out the logins on server1 and apply them to server2
> ?
> Missing permissions occured when i did the above for our greatplains
> database.
> Any help appreciated.
> Cheers, john
>
|||Logins are in master table and you dont have copied it to other server
Use sp_help_revlogin by microsoft to transfer login
http://support.microsoft.com/default.aspx?kbid=246133
OR
If login already exists on Server 2 then you need to map user to logins
in the DB2., since the SIDs are different on both the servers for the
login.
Regards
Amish
|||thanks for the help Andrew and Amish.
I have always used the following article as my approach to scripting out the
logins:
http://www.support.microsoft.com/?id=246133
This has worked in the past.
The strange thing (i.e. when this was performed on the Greatplains database)
was that when i reattached all the databases back onto Server1 (and of
course the copies on server2), permissions went missing on server1 as well.
Is this particular to greatplains?
any help appreciated.
cheers, john
"amish" <shahamishm@.gmail.com> wrote in message
news:1135174050.693957.5380@.g14g2000cwa.googlegrou ps.com...
> Logins are in master table and you dont have copied it to other server
> Use sp_help_revlogin by microsoft to transfer login
> http://support.microsoft.com/default.aspx?kbid=246133
> OR
> If login already exists on Server 2 then you need to map user to logins
> in the DB2., since the SIDs are different on both the servers for the
> login.
> Regards
> Amish
>
|||I am not quite sure what you did but have you tried the
sp_change_users_login to see if that helps?
Andrew J. Kelly SQL MVP
"john clarke" <jclarke@.nospam.com> wrote in message
news:ujPILOkBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> thanks for the help Andrew and Amish.
> I have always used the following article as my approach to scripting out
> the logins:
> http://www.support.microsoft.com/?id=246133
> This has worked in the past.
> The strange thing (i.e. when this was performed on the Greatplains
> database) was that when i reattached all the databases back onto Server1
> (and of course the copies on server2), permissions went missing on server1
> as well.
> Is this particular to greatplains?
> any help appreciated.
> cheers, john
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1135174050.693957.5380@.g14g2000cwa.googlegrou ps.com...
>

detach/attach loses permissions

Hi, I was wondering if anyone had insight on this. I have traditionally
always used detach/attach as a method of copying databases i.e.:
I have 2 servers running SQL 2000: serverA, serverB
My intention is to copy a database (call it DB1) from server1 to server2
I perform the following:
1. i detach DB1
2. copy it (call it DB2)
3. attach DB1 to Server1 and DB2 to server2
4. place both server1 and server2 in single user mode via the -T3608
parameter
5. detach model THEN msdb from Server1, copy them and move the copies to
server2
6. attach model THEN msdb to server1 and server2
7. remove the -T3608 parameter and restart both sql services
8. script out the logins on server1 and apply them to server2
Now that is all fine according to microsofts article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
However what i have noticed at times is that on server2, the logins are
missing permissions to DB2. Why does this happen?
Can things go missing if i perform it in this order:
1. place both server1 and server2 in single user mode via the -T3608
parameter
2. detach model THEN msdb from Server1, copy them and move the copies to
server2
3. attach model THEN msdb to server1 and server2
4. remove the -T3608 parameter and restart both sql services
5. i detach DB1
6. copy it (call it DB2)
7. attach DB1 to Server1 and DB2 to server2
8. script out the logins on server1 and apply them to server2
?
Missing permissions occured when i did the above for our greatplains
database.
Any help appreciated.
Cheers, johnSQL Server Logins are mapped to DB Users via an ID that can be different on
both servers, especially when they are not recreated in the exact order on
both servers. You need to use sp_change_users_login to fix the mapping
after the attach and after you recreate the logins. These links might be of
help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
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://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
Andrew J. Kelly SQL MVP
"john clarke" <jclarke@.nospam.com> wrote in message
news:%23JAspriBGHA.3896@.TK2MSFTNGP09.phx.gbl...
> Hi, I was wondering if anyone had insight on this. I have traditionally
> always used detach/attach as a method of copying databases i.e.:
> I have 2 servers running SQL 2000: serverA, serverB
> My intention is to copy a database (call it DB1) from server1 to server2
> I perform the following:
> 1. i detach DB1
> 2. copy it (call it DB2)
> 3. attach DB1 to Server1 and DB2 to server2
> 4. place both server1 and server2 in single user mode via the -T3608
> parameter
> 5. detach model THEN msdb from Server1, copy them and move the copies to
> server2
> 6. attach model THEN msdb to server1 and server2
> 7. remove the -T3608 parameter and restart both sql services
> 8. script out the logins on server1 and apply them to server2
> Now that is all fine according to microsofts article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
> However what i have noticed at times is that on server2, the logins are
> missing permissions to DB2. Why does this happen?
> Can things go missing if i perform it in this order:
> 1. place both server1 and server2 in single user mode via the -T3608
> parameter
> 2. detach model THEN msdb from Server1, copy them and move the copies to
> server2
> 3. attach model THEN msdb to server1 and server2
> 4. remove the -T3608 parameter and restart both sql services
> 5. i detach DB1
> 6. copy it (call it DB2)
> 7. attach DB1 to Server1 and DB2 to server2
> 8. script out the logins on server1 and apply them to server2
> ?
> Missing permissions occured when i did the above for our greatplains
> database.
> Any help appreciated.
> Cheers, john
>|||Logins are in master table and you dont have copied it to other server
Use sp_help_revlogin by microsoft to transfer login
http://support.microsoft.com/default.aspx?kbid=246133
OR
If login already exists on Server 2 then you need to map user to logins
in the DB2., since the SIDs are different on both the servers for the
login.
Regards
Amish|||thanks for the help Andrew and Amish.
I have always used the following article as my approach to scripting out the
logins:
http://www.support.microsoft.com/?id=246133
This has worked in the past.
The strange thing (i.e. when this was performed on the Greatplains database)
was that when i reattached all the databases back onto Server1 (and of
course the copies on server2), permissions went missing on server1 as well.
Is this particular to greatplains?
any help appreciated.
cheers, john
"amish" <shahamishm@.gmail.com> wrote in message
news:1135174050.693957.5380@.g14g2000cwa.googlegroups.com...
> Logins are in master table and you dont have copied it to other server
> Use sp_help_revlogin by microsoft to transfer login
> http://support.microsoft.com/default.aspx?kbid=246133
> OR
> If login already exists on Server 2 then you need to map user to logins
> in the DB2., since the SIDs are different on both the servers for the
> login.
> Regards
> Amish
>|||I am not quite sure what you did but have you tried the
sp_change_users_login to see if that helps?
--
Andrew J. Kelly SQL MVP
"john clarke" <jclarke@.nospam.com> wrote in message
news:ujPILOkBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> thanks for the help Andrew and Amish.
> I have always used the following article as my approach to scripting out
> the logins:
> http://www.support.microsoft.com/?id=246133
> This has worked in the past.
> The strange thing (i.e. when this was performed on the Greatplains
> database) was that when i reattached all the databases back onto Server1
> (and of course the copies on server2), permissions went missing on server1
> as well.
> Is this particular to greatplains?
> any help appreciated.
> cheers, john
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1135174050.693957.5380@.g14g2000cwa.googlegroups.com...
>> Logins are in master table and you dont have copied it to other server
>> Use sp_help_revlogin by microsoft to transfer login
>> http://support.microsoft.com/default.aspx?kbid=246133
>> OR
>> If login already exists on Server 2 then you need to map user to logins
>> in the DB2., since the SIDs are different on both the servers for the
>> login.
>> Regards
>> Amish
>

detach/attach loses permissions

Hi, I was wondering if anyone had insight on this. I have traditionally
always used detach/attach as a method of copying databases i.e.:
I have 2 servers running SQL 2000: serverA, serverB
My intention is to copy a database (call it DB1) from server1 to server2
I perform the following:
1. i detach DB1
2. copy it (call it DB2)
3. attach DB1 to Server1 and DB2 to server2
4. place both server1 and server2 in single user mode via the -T3608
parameter
5. detach model THEN msdb from Server1, copy them and move the copies to
server2
6. attach model THEN msdb to server1 and server2
7. remove the -T3608 parameter and restart both sql services
8. script out the logins on server1 and apply them to server2
Now that is all fine according to microsofts article:
http://support.microsoft.com/defaul...kb;en-us;224071
However what i have noticed at times is that on server2, the logins are
missing permissions to DB2. Why does this happen?
Can things go missing if i perform it in this order:
1. place both server1 and server2 in single user mode via the -T3608
parameter
2. detach model THEN msdb from Server1, copy them and move the copies to
server2
3. attach model THEN msdb to server1 and server2
4. remove the -T3608 parameter and restart both sql services
5. i detach DB1
6. copy it (call it DB2)
7. attach DB1 to Server1 and DB2 to server2
8. script out the logins on server1 and apply them to server2
?
Missing permissions occured when i did the above for our greatplains
database.
Any help appreciated.
Cheers, johnSQL Server Logins are mapped to DB Users via an ID that can be different on
both servers, especially when they are not recreated in the exact order on
both servers. You need to use sp_change_users_login to fix the mapping
after the attach and after you recreate the logins. These links might be of
help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
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://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/col...se
s.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/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"john clarke" <jclarke@.nospam.com> wrote in message
news:%23JAspriBGHA.3896@.TK2MSFTNGP09.phx.gbl...
> Hi, I was wondering if anyone had insight on this. I have traditionally
> always used detach/attach as a method of copying databases i.e.:
> I have 2 servers running SQL 2000: serverA, serverB
> My intention is to copy a database (call it DB1) from server1 to server2
> I perform the following:
> 1. i detach DB1
> 2. copy it (call it DB2)
> 3. attach DB1 to Server1 and DB2 to server2
> 4. place both server1 and server2 in single user mode via the -T3608
> parameter
> 5. detach model THEN msdb from Server1, copy them and move the copies to
> server2
> 6. attach model THEN msdb to server1 and server2
> 7. remove the -T3608 parameter and restart both sql services
> 8. script out the logins on server1 and apply them to server2
> Now that is all fine according to microsofts article:
> http://support.microsoft.com/defaul...kb;en-us;224071
> However what i have noticed at times is that on server2, the logins are
> missing permissions to DB2. Why does this happen?
> Can things go missing if i perform it in this order:
> 1. place both server1 and server2 in single user mode via the -T3608
> parameter
> 2. detach model THEN msdb from Server1, copy them and move the copies to
> server2
> 3. attach model THEN msdb to server1 and server2
> 4. remove the -T3608 parameter and restart both sql services
> 5. i detach DB1
> 6. copy it (call it DB2)
> 7. attach DB1 to Server1 and DB2 to server2
> 8. script out the logins on server1 and apply them to server2
> ?
> Missing permissions occured when i did the above for our greatplains
> database.
> Any help appreciated.
> Cheers, john
>|||Logins are in master table and you dont have copied it to other server
Use sp_help_revlogin by microsoft to transfer login
http://support.microsoft.com/default.aspx?kbid=246133
OR
If login already exists on Server 2 then you need to map user to logins
in the DB2., since the SIDs are different on both the servers for the
login.
Regards
Amish|||thanks for the help Andrew and Amish.
I have always used the following article as my approach to scripting out the
logins:
http://www.support.microsoft.com/?id=246133
This has worked in the past.
The strange thing (i.e. when this was performed on the Greatplains database)
was that when i reattached all the databases back onto Server1 (and of
course the copies on server2), permissions went missing on server1 as well.
Is this particular to greatplains?
any help appreciated.
cheers, john
"amish" <shahamishm@.gmail.com> wrote in message
news:1135174050.693957.5380@.g14g2000cwa.googlegroups.com...
> Logins are in master table and you dont have copied it to other server
> Use sp_help_revlogin by microsoft to transfer login
> http://support.microsoft.com/default.aspx?kbid=246133
> OR
> If login already exists on Server 2 then you need to map user to logins
> in the DB2., since the SIDs are different on both the servers for the
> login.
> Regards
> Amish
>|||I am not quite sure what you did but have you tried the
sp_change_users_login to see if that helps?
Andrew J. Kelly SQL MVP
"john clarke" <jclarke@.nospam.com> wrote in message
news:ujPILOkBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> thanks for the help Andrew and Amish.
> I have always used the following article as my approach to scripting out
> the logins:
> http://www.support.microsoft.com/?id=246133
> This has worked in the past.
> The strange thing (i.e. when this was performed on the Greatplains
> database) was that when i reattached all the databases back onto Server1
> (and of course the copies on server2), permissions went missing on server1
> as well.
> Is this particular to greatplains?
> any help appreciated.
> cheers, john
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1135174050.693957.5380@.g14g2000cwa.googlegroups.com...
>

Detach and attaching changing file permissions

Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks Ellie
I've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>
|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.

> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>

Detach and attaching changing file permissions

Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.
> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>> Hi Ellie,
>> The change in permissions on the mdf file after detaching and attaching
>> is by design in SQL Server 2005. This Books Online topic explains what
>> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Ellie" <nospam@.nospam.net> wrote in message
>> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> We are running Sql Server 2005. We have the mdf file permissions
>> including Administrator and Authenticated Users. After a detach, copy of
>> the mdf file by a VB6 exe program running from a computer as an
>> Authenticated User, then a attach of the mdf file, the Authenticated
>> Users group gets removed from the individual mdf file permissions. Any
>> help would be greatly appreciated.
>> Thanks Ellie
>>
>

Detach and attaching changing file permissions

Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks EllieI've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/...r/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.

> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>