permissions problems

Hi,
I've made a mistake that is causing big problems and hope someone can help
me solve this problem:
When I reattached my database files after a rebuild to the server, somehow
the permissions for accounts, including sa, are not right. Here are two
examples: one database is maintained through an MS Access front end. All
the code needed to be changed to add the owner qualifier to the table names
in order to work. I am working with a second database today. If I access
it through Enterprise Mgr I can insert records into any tables fine, but if
I link the tables in access using same username and pw, I can only read, not
insert or update.
I'm very new at SQL Server admin. can someone help me to figure out what I
did wrong when I reattached the databases or reinstalled the new instance of
SQL Server on the machine?
Thanks,
CherylWow.. I'm not sure I completely follow, but let me give you a quick rundown
on the SQL Server side and maybe it will help you with your problems.
In SQL Server, you have a system login ID which is stored in the master
database in a table called sysxlogins. This id is created by you in the
Security folder in Enterprise Manager.
This allows you access to SQL Server, but not to any databases. To gain
access to a database, that login ID must be mapped into a database. Every
database has a table in it called sysusers. This sysusers table will have
the mapped login id from the master databases's sysxlogins table.
When you rebuild the system and recreate your databases, or reattach them,
you can run in to problems. This is especially true when the master
database has been recreated for some reason and you are not supplying a
backup.
If you create the following login ID's in SQL Server in the following order:
1. Frogger
2. Kermit
3. BigBird
They will have sysxlogin ids of 1, 2 and 3 (just an example folks)
If you then map these into your LilyPad database as the users:
Frogger
Kermit
BigBird
Then the sysusers table will have 1, 2 and 3 respectively.
You then assign permissions to those database users.
If you then detach your databases and move them to a different server and
reattach them, the following happens.
1. The database users are still there, however they no longer map to login
id's from sysxlogins because sysxlogins is in the old master database on the
other server.
You recreate the ID's on your new server, but do so in the following order:
1. BigBird
2. Frogger
3. Kermit
The ID's are now there, but they still do not match your database user ids.
In order to complete the process, you have to match up these new ID's from
sysxlogins with the database users.
To do so, you need to run sp_change_users_login for each ID and in each
database.
HTH
Rick Sawtell|||An excellent explanation by Rick.
If you have created the logins with the same names as before the rebuild,
you can use the following code to link the orphaned users. Run the code in
QA from the DB of your concern.
exec master.dbo.sp_configure @.configname = 'allow updates', @.configvalue =1
RECONFIGURE WITH OVERRIDE
GO
update sysusers set sid = l.sid
from sysusers u
inner join master.dbo.syslogins l
on u.name = l.name
exec master.dbo.sp_configure @.configname = 'allow updates', @.configvalue =0
RECONFIGURE WITH OVERRIDE
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eOyBINomEHA.2616@.tk2msftngp13.phx.gbl...
> Wow.. I'm not sure I completely follow, but let me give you a quick
rundown
> on the SQL Server side and maybe it will help you with your problems.
> In SQL Server, you have a system login ID which is stored in the master
> database in a table called sysxlogins. This id is created by you in the
> Security folder in Enterprise Manager.
> This allows you access to SQL Server, but not to any databases. To gain
> access to a database, that login ID must be mapped into a database.
Every
> database has a table in it called sysusers. This sysusers table will have
> the mapped login id from the master databases's sysxlogins table.
> When you rebuild the system and recreate your databases, or reattach them,
> you can run in to problems. This is especially true when the master
> database has been recreated for some reason and you are not supplying a
> backup.
> If you create the following login ID's in SQL Server in the following
order:
> 1. Frogger
> 2. Kermit
> 3. BigBird
> They will have sysxlogin ids of 1, 2 and 3 (just an example folks)
> If you then map these into your LilyPad database as the users:
> Frogger
> Kermit
> BigBird
> Then the sysusers table will have 1, 2 and 3 respectively.
> You then assign permissions to those database users.
> If you then detach your databases and move them to a different server and
> reattach them, the following happens.
> 1. The database users are still there, however they no longer map to
login
> id's from sysxlogins because sysxlogins is in the old master database on
the
> other server.
> You recreate the ID's on your new server, but do so in the following
order:
> 1. BigBird
> 2. Frogger
> 3. Kermit
> The ID's are now there, but they still do not match your database user
ids.
> In order to complete the process, you have to match up these new ID's from
> sysxlogins with the database users.
> To do so, you need to run sp_change_users_login for each ID and in each
> database.
>
> HTH
> Rick Sawtell
>|||Yikes. I think you're right - that's exactly what I did (or failed to do!).
I'll try your suggestion and see if it fixes the mess I've created.
Thanks very much for your help,
Cheryl
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eOyBINomEHA.2616@.tk2msftngp13.phx.gbl...
> Wow.. I'm not sure I completely follow, but let me give you a quick
rundown
> on the SQL Server side and maybe it will help you with your problems.
> In SQL Server, you have a system login ID which is stored in the master
> database in a table called sysxlogins. This id is created by you in the
> Security folder in Enterprise Manager.
> This allows you access to SQL Server, but not to any databases. To gain
> access to a database, that login ID must be mapped into a database.
Every
> database has a table in it called sysusers. This sysusers table will have
> the mapped login id from the master databases's sysxlogins table.
> When you rebuild the system and recreate your databases, or reattach them,
> you can run in to problems. This is especially true when the master
> database has been recreated for some reason and you are not supplying a
> backup.
> If you create the following login ID's in SQL Server in the following
order:
> 1. Frogger
> 2. Kermit
> 3. BigBird
> They will have sysxlogin ids of 1, 2 and 3 (just an example folks)
> If you then map these into your LilyPad database as the users:
> Frogger
> Kermit
> BigBird
> Then the sysusers table will have 1, 2 and 3 respectively.
> You then assign permissions to those database users.
> If you then detach your databases and move them to a different server and
> reattach them, the following happens.
> 1. The database users are still there, however they no longer map to
login
> id's from sysxlogins because sysxlogins is in the old master database on
the
> other server.
> You recreate the ID's on your new server, but do so in the following
order:
> 1. BigBird
> 2. Frogger
> 3. Kermit
> The ID's are now there, but they still do not match your database user
ids.
> In order to complete the process, you have to match up these new ID's from
> sysxlogins with the database users.
> To do so, you need to run sp_change_users_login for each ID and in each
> database.
>
> HTH
> Rick Sawtell
>|||Cheryl,
Also, search KB for sp_help_rev_logins. With this you can create logins on the destination server
with the same SID as on the originating server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cheryl" <cr1ce@.comcast.net> wrote in message news:ci7heu$in7$1@.scrotar.nss.udel.edu...
> Yikes. I think you're right - that's exactly what I did (or failed to do!).
> I'll try your suggestion and see if it fixes the mess I've created.
> Thanks very much for your help,
> Cheryl
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:eOyBINomEHA.2616@.tk2msftngp13.phx.gbl...
>> Wow.. I'm not sure I completely follow, but let me give you a quick
> rundown
>> on the SQL Server side and maybe it will help you with your problems.
>> In SQL Server, you have a system login ID which is stored in the master
>> database in a table called sysxlogins. This id is created by you in the
>> Security folder in Enterprise Manager.
>> This allows you access to SQL Server, but not to any databases. To gain
>> access to a database, that login ID must be mapped into a database.
> Every
>> database has a table in it called sysusers. This sysusers table will have
>> the mapped login id from the master databases's sysxlogins table.
>> When you rebuild the system and recreate your databases, or reattach them,
>> you can run in to problems. This is especially true when the master
>> database has been recreated for some reason and you are not supplying a
>> backup.
>> If you create the following login ID's in SQL Server in the following
> order:
>> 1. Frogger
>> 2. Kermit
>> 3. BigBird
>> They will have sysxlogin ids of 1, 2 and 3 (just an example folks)
>> If you then map these into your LilyPad database as the users:
>> Frogger
>> Kermit
>> BigBird
>> Then the sysusers table will have 1, 2 and 3 respectively.
>> You then assign permissions to those database users.
>> If you then detach your databases and move them to a different server and
>> reattach them, the following happens.
>> 1. The database users are still there, however they no longer map to
> login
>> id's from sysxlogins because sysxlogins is in the old master database on
> the
>> other server.
>> You recreate the ID's on your new server, but do so in the following
> order:
>> 1. BigBird
>> 2. Frogger
>> 3. Kermit
>> The ID's are now there, but they still do not match your database user
> ids.
>> In order to complete the process, you have to match up these new ID's from
>> sysxlogins with the database users.
>> To do so, you need to run sp_change_users_login for each ID and in each
>> database.
>>
>> HTH
>> Rick Sawtell
>>
>|||Ohhhh...
Forgot about that one Tibor.
Thanks!
Rick
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Pg0TMpmEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Cheryl,
> Also, search KB for sp_help_rev_logins. With this you can create logins on
the destination server
> with the same SID as on the originating server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Cheryl" <cr1ce@.comcast.net> wrote in message
news:ci7heu$in7$1@.scrotar.nss.udel.edu...
> > Yikes. I think you're right - that's exactly what I did (or failed to
do!).
> > I'll try your suggestion and see if it fixes the mess I've created.
> >
> > Thanks very much for your help,
> > Cheryl
> >
> >
> >
> > "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> > news:eOyBINomEHA.2616@.tk2msftngp13.phx.gbl...
> >> Wow.. I'm not sure I completely follow, but let me give you a quick
> > rundown
> >> on the SQL Server side and maybe it will help you with your problems.
> >>
> >> In SQL Server, you have a system login ID which is stored in the master
> >> database in a table called sysxlogins. This id is created by you in
the
> >> Security folder in Enterprise Manager.
> >>
> >> This allows you access to SQL Server, but not to any databases. To
gain
> >> access to a database, that login ID must be mapped into a database.
> > Every
> >> database has a table in it called sysusers. This sysusers table will
have
> >> the mapped login id from the master databases's sysxlogins table.
> >>
> >> When you rebuild the system and recreate your databases, or reattach
them,
> >> you can run in to problems. This is especially true when the master
> >> database has been recreated for some reason and you are not supplying a
> >> backup.
> >>
> >> If you create the following login ID's in SQL Server in the following
> > order:
> >>
> >> 1. Frogger
> >> 2. Kermit
> >> 3. BigBird
> >>
> >> They will have sysxlogin ids of 1, 2 and 3 (just an example folks)
> >>
> >> If you then map these into your LilyPad database as the users:
> >> Frogger
> >> Kermit
> >> BigBird
> >>
> >> Then the sysusers table will have 1, 2 and 3 respectively.
> >> You then assign permissions to those database users.
> >>
> >> If you then detach your databases and move them to a different server
and
> >> reattach them, the following happens.
> >>
> >> 1. The database users are still there, however they no longer map to
> > login
> >> id's from sysxlogins because sysxlogins is in the old master database
on
> > the
> >> other server.
> >>
> >> You recreate the ID's on your new server, but do so in the following
> > order:
> >>
> >> 1. BigBird
> >> 2. Frogger
> >> 3. Kermit
> >>
> >> The ID's are now there, but they still do not match your database user
> > ids.
> >>
> >> In order to complete the process, you have to match up these new ID's
from
> >> sysxlogins with the database users.
> >>
> >> To do so, you need to run sp_change_users_login for each ID and in each
> >> database.
> >>
> >>
> >> HTH
> >>
> >> Rick Sawtell
> >>
> >>
> >
> >
>sql