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

Permissions problem - rsAccessDenied - Urgent

Hi

I'm trying to get the CustomSecurity sample in Reporting Services to work [C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample\cs\FormsAuthentication] but I keep on getting the following message:

The permissions granted to user 'Admin' are insufficient for performing this operation. (rsAccessDenied)

I have given <servername>\ASPNET execute permissions.
I have successfully created a new user "Admin".

However, when I then try to logon to either ReportServer [http://miles/ReportServer/logon.aspx] or ReportManager [http://miles/Reports/pages/uilogon.aspx] I get the above error.

I'm using SQL 2005 Developer edition and XP professional.

Please help!

Connect to Reporting Services through Sql 2005

Select your reporting service project, right click and select Properties. In the Properties window select Permissions tab and add the group or user eg. <servername>\ASPNET and give all permissions to it. Say Ok

Permissions problem - rsAccessDenied - Urgent

Hi

I'm trying to get the CustomSecurity sample in Reporting Services to work [C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample\cs\FormsAuthentication] but I keep on getting the following message:

The permissions granted to user 'Admin' are insufficient for performing this operation. (rsAccessDenied)

I have given <servername>\ASPNET execute permissions.
I have successfully created a new user "Admin".

However, when I then try to logon to either ReportServer [http://miles/ReportServer/logon.aspx] or ReportManager [http://miles/Reports/pages/uilogon.aspx] I get the above error.

I'm using SQL 2005 Developer edition and XP professional.

Please help!

Connect to Reporting Services through Sql 2005

Select your reporting service project, right click and select Properties. In the Properties window select Permissions tab and add the group or user eg. <servername>\ASPNET and give all permissions to it. Say Ok

permissions MDF LDF

Hi all
My question is , What permissions do you grant to the MDF / LDF files ,
bear in mind I want maximum security
all the best !
mastar
The service account needs read and write access. That should be all a
vanilla installation needs. However, I would also give local administrators
and system access as well. They could get access if they really wanted it
and it may save problems down the road.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"mastar" <mastarezma@.hotmail.com> wrote in message
news:9F37950E-4359-43B8-90F6-D05FFE13B1C8@.microsoft.com...
> Hi all
> My question is , What permissions do you grant to the MDF / LDF files ,
> bear in mind I want maximum security
> all the best !
> --
> mastar

permissions MDF LDF

Hi all
My question is , What permissions do you grant to the MDF / LDF files ,
bear in mind I want maximum security
all the best !
--
mastarThe service account needs read and write access. That should be all a
vanilla installation needs. However, I would also give local administrators
and system access as well. They could get access if they really wanted it
and it may save problems down the road.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"mastar" <mastarezma@.hotmail.com> wrote in message
news:9F37950E-4359-43B8-90F6-D05FFE13B1C8@.microsoft.com...
> Hi all
> My question is , What permissions do you grant to the MDF / LDF files ,
> bear in mind I want maximum security
> all the best !
> --
> mastar

Permission Issues running DTS from Agent

We have a DTS which loads files using FTP to another
server. This will be used by app groups and will be run
via click of button using sp_start_job,

Somehow the userid running the job gets "not sysadmin to
run cmdshell.." Top fix that, we granted it to execute the
xp_cmdshell and also created a proxy account for SQL Agent
which is an admin on server and sql. However, still we get
permission errors like "...The needed permission is
missing to run command shell.." Please help . The DTS is
being called from SQl Agent job and we don't want an admin
ID to be used .Do you have 'exec master.dbo.' in from of xp_command... :confused: