Permission Denied Error: security

Showing posts with label security. Show all posts

Monday, March 26, 2012

Permissions with views and tables

We are attempting to implement security on top of a shrink-wrapped software
package and are trying to get row-level security. Here's the scenario:

1. Table dbo.BOOK contains all the information about books in every
department.
2. There are a large number of developed reports that run queries like
"select * from BOOK..."
3. We wish to have each Department only be able to see their books - without
changing the existing reports.

Our thought was to create a series of views:

create view Dept1.BOOK as
select * from BOOK where Dept=1

...

and then create Roles for each Dept. We'd then remove rights to dbo.BOOK
and grant rights to DeptN.BOOK as appropriate for each role. We started
testing this and seemed to get it working, but are now having problems. Is
this possible? Is there another, better solution?

Thanks!Anon (anon email) writes:
> We are attempting to implement security on top of a shrink-wrapped
> software package and are trying to get row-level security. Here's the
> scenario:
> 1. Table dbo.BOOK contains all the information about books in every
> department.
> 2. There are a large number of developed reports that run queries like
> "select * from BOOK..."
> 3. We wish to have each Department only be able to see their books -
> without changing the existing reports.
> Our thought was to create a series of views:
> create view Dept1.BOOK as
> select * from BOOK where Dept=1
> ...
> and then create Roles for each Dept. We'd then remove rights to
> dbo.BOOK and grant rights to DeptN.BOOK as appropriate for each role.
> We started testing this and seemed to get it working, but are now having
> problems. Is this possible? Is there another, better solution?

And the problems you get are?

Whther this will work a lot, depends on your shrink-wrap. After all,
you are doing something for which it is not prepared. Updates would
fail, but you could have INSTEAD OF triggers to cate for that.

In the view definition, I would recommend that you say dbo.BOOK for
clarity.

You should also beware of that this sort of row-level security is not
fool-proof. It is possible to dig out information about data you don't
have access to. Then again, it's not trivial and it does require
expert skills to do it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Sorry, I didn't clarify the view; it is created using "select * from
dbo.BOOK". However, when a user with rights to Dept1.BOOK but not to
dbo.BOOK attempts to run the query they get an error that states

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'BOOK', database 'LIBRARY', owner 'dbo'.

What we'd like to see is the explicit rights on the View supercede the
rights on the table, but that doesn't seem to be the case.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9662F1EE69756Yazorman@.127.0.0.1...
> Anon (anon email) writes:
>> We are attempting to implement security on top of a shrink-wrapped
>> software package and are trying to get row-level security. Here's the
>> scenario:
>>
>> 1. Table dbo.BOOK contains all the information about books in every
>> department.
>> 2. There are a large number of developed reports that run queries like
>> "select * from BOOK..."
>> 3. We wish to have each Department only be able to see their books -
>> without changing the existing reports.
>>
>> Our thought was to create a series of views:
>>
>> create view Dept1.BOOK as
>> select * from BOOK where Dept=1
>>
>> ...
>>
>> and then create Roles for each Dept. We'd then remove rights to
>> dbo.BOOK and grant rights to DeptN.BOOK as appropriate for each role.
>> We started testing this and seemed to get it working, but are now having
>> problems. Is this possible? Is there another, better solution?
> And the problems you get are?
> Whther this will work a lot, depends on your shrink-wrap. After all,
> you are doing something for which it is not prepared. Updates would
> fail, but you could have INSTEAD OF triggers to cate for that.
> In the view definition, I would recommend that you say dbo.BOOK for
> clarity.
> You should also beware of that this sort of row-level security is not
> fool-proof. It is possible to dig out information about data you don't
> have access to. Then again, it's not trivial and it does require
> expert skills to do it.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Anon (anon email) writes:
> Sorry, I didn't clarify the view; it is created using "select * from
> dbo.BOOK". However, when a user with rights to Dept1.BOOK but not to
> dbo.BOOK attempts to run the query they get an error that states
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'BOOK', database 'LIBRARY', owner
> 'dbo'.
> What we'd like to see is the explicit rights on the View supercede the
> rights on the table, but that doesn't seem to be the case.

I will have to admit that if you granted Dept1 rights on dbo.Book, and
then the users rights to Dept1.book it would work, but nope. In fact
I even tried creating a stored procedure Dept1.book_sp and grant users
execute rights on that one, but that also failed. However, this latter
arrangeent actually works on SQL 6.5, so at least I did remember
correctly so far. (But Microsoft has changed the rules. Grr!)

Right now, I have to good ideas to get this to work in SQL 2000. In
SQL 2005, it would be another matter, because Dept1 would just be a
schema, that still could be owned by dbo.

Of course, you can create the view as dbo.Dept1books, but I don't
if that meets your ambition to fool the shrink-wrap package.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Unfortunately it seems our quick trial was on a 2005 server, and that
remains in Beta. Sigh. Does anyone have any other ideas on how to
accomplish this?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9667F329E4C86Yazorman@.127.0.0.1...
> Anon (anon email) writes:
>> Sorry, I didn't clarify the view; it is created using "select * from
>> dbo.BOOK". However, when a user with rights to Dept1.BOOK but not to
>> dbo.BOOK attempts to run the query they get an error that states
>>
>> Server: Msg 229, Level 14, State 5, Line 1
>> SELECT permission denied on object 'BOOK', database 'LIBRARY', owner
>> 'dbo'.
>>
>> What we'd like to see is the explicit rights on the View supercede the
>> rights on the table, but that doesn't seem to be the case.
> I will have to admit that if you granted Dept1 rights on dbo.Book, and
> then the users rights to Dept1.book it would work, but nope. In fact
> I even tried creating a stored procedure Dept1.book_sp and grant users
> execute rights on that one, but that also failed. However, this latter
> arrangeent actually works on SQL 6.5, so at least I did remember
> correctly so far. (But Microsoft has changed the rules. Grr!)
> Right now, I have to good ideas to get this to work in SQL 2000. In
> SQL 2005, it would be another matter, because Dept1 would just be a
> schema, that still could be owned by dbo.
> Of course, you can create the view as dbo.Dept1books, but I don't
> if that meets your ambition to fool the shrink-wrap package.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Anon (anon email) writes:
> Unfortunately it seems our quick trial was on a 2005 server, and that
> remains in Beta. Sigh. Does anyone have any other ideas on how to
> accomplish this?

Maybe you could start to give the full presumptions for your case. You've
presented some scattered some information, from which I was able to make
some guesses. But it does help to know what exact degrees of freedom
you have with your shrink-wrap.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Permissions when restoring a database

Permissions used to Connect to SSIS on remote machine

We are trying to take advantage of the new Security Context for SSIS but users are unable to connect remotely to the SSIS Service unless they have been added to the Administrators Users Group on the server. I have tried adding them to Guests, Power Users, Remote Desktop Users, Users and SQLServer2005DTSUser$machine but the user is unable to connect.

The message received when the user is unable to connect is:

Cannot connect to 192.x.x.x
Additional Information
-> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
-> Connect to SSIS Service on machine "192.x.x.x" failed:
Access is denied

What is the list of permissions and privileges that a user that does not belong to the server's Administrator group and have sysadmin server role, must have to allow them to create and run a package through SSIS.

i have the same issue. i am able to connect to the local integration services. but when i try to access another servers integration services Access is denied. what permissions are needed?

aaks

|||Try these instructions: http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx|||

You have to perform these steps in addition to the one's specified above.

http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry

Permissions used to Connect to SSIS on remote machine

The message received when the user is unable to connect is:

i have the same issue. i am able to connect to the local integration services. but when i try to access another servers integration services Access is denied. what permissions are needed?

aaks

|||Try these instructions: http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx|||

You have to perform these steps in addition to the one's specified above.

http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry

Permissions to See Server Logins/Create Database Users

Our company has 2 Database Roles (DBE and DBA). The DBE creates database
schema, performs SQL Server Administration, and manages server security. The
DBA writes data access, ETL, and manages database security. In 2005, we're
struggling with how to allow the DBA to see all of the logins on the server
in order to add them as users of their database. What permissions does the
DBA need to select from any of the logins on the server to add them to their
database?Michelle (Michelle@.discussions.microsoft.com) writes:
> Our company has 2 Database Roles (DBE and DBA). The DBE creates database
> schema, performs SQL Server Administration, and manages server security.
> The DBA writes data access, ETL, and manages database security. In 2005,
> we're struggling with how to allow the DBA to see all of the logins on
> the server in order to add them as users of their database. What
> permissions does the DBA need to select from any of the logins on the
> server to add them to their database?
VIEW ANY DEFINITION is the simplest - then the DBA will see all logins.
But he will also see other logins.
The other alternative is to grant VIEW DEFINITION on the logins he should
be permitted to play with.
Curiously there is no VIEW ANY LOGIN. There is ALTER ANY LOGIN, but that
would give the DBA permissions he should not have.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, March 23, 2012

Permissions to Deploy and view RS Homepage

I messed up some settings security settings in IIS manager
and due to which I am having trouble with deployment
through VS.NET as well as trouble viewing the Reporting
Services main page.
When I type http://localhost/Reports, I just get the Home
page with Home, My Subscriptions and Help links on the
right top corner. All my reports have dissapeared. Any
tips?Go to properties of Reports and ReportServer virtual roots, open security
tab, select "Edit" under "authentication and access control", then make sure
anonymous in NOT checked. See if this helps.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Amar Karande" <anonymous@.discussions.microsoft.com> wrote in message
news:24f3d01c4606e$869a6170$a601280a@.phx.gbl...
> I messed up some settings security settings in IIS manager
> and due to which I am having trouble with deployment
> through VS.NET as well as trouble viewing the Reporting
> Services main page.
> When I type http://localhost/Reports, I just get the Home
> page with Home, My Subscriptions and Help links on the
> right top corner. All my reports have dissapeared. Any
> tips?|||It helped. Thanks a bunch.
>--Original Message--
>Go to properties of Reports and ReportServer virtual
roots, open security
>tab, select "Edit" under "authentication and access
control", then make sure
>anonymous in NOT checked. See if this helps.
>--
>Dmitry Vasilevsky, SQL Server Reporting Services
Developer
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>--
>---
>"Amar Karande" <anonymous@.discussions.microsoft.com>
wrote in message
>news:24f3d01c4606e$869a6170$a601280a@.phx.gbl...
>> I messed up some settings security settings in IIS
manager
>> and due to which I am having trouble with deployment
>> through VS.NET as well as trouble viewing the Reporting
>> Services main page.
>> When I type http://localhost/Reports, I just get the
Home
>> page with Home, My Subscriptions and Help links on the
>> right top corner. All my reports have dissapeared. Any
>> tips?
>
>.
>

Wednesday, March 21, 2012

Permissions Problem

I am having a problem trying to set permissions based on an Active
Directory security group account. I go to the permissions of the Home
page and add DOMAIN\Group with the Browser role. I then go to the
specific folders under Home and add the same Group to it with the
Browser role. When the user that is in that group goes to the Report
Server, they see a page with only Home and no links. If I add the
Domain Users group, then the user can see the folders. If I add just
the user, then they can see the folders. But I want to use Security
Groups to enforce permissions.
Has anyone gotten this to work? Any help is greatly appreciated.
Thanks
RickHi Rick,
I experience this often with newly created AD Groups. I find that you have
to wait a while (2-3 Hours) until the report server can recognise the new
group. Hope the solution is that simple for you as well...
Cheers
Darren
"Rick Saavedra" wrote:
> I am having a problem trying to set permissions based on an Active
> Directory security group account. I go to the permissions of the Home
> page and add DOMAIN\Group with the Browser role. I then go to the
> specific folders under Home and add the same Group to it with the
> Browser role. When the user that is in that group goes to the Report
> Server, they see a page with only Home and no links. If I add the
> Domain Users group, then the user can see the folders. If I add just
> the user, then they can see the folders. But I want to use Security
> Groups to enforce permissions.
> Has anyone gotten this to work? Any help is greatly appreciated.
> Thanks
> Rick
>

Permissions on Restore

Our staff login to SQL Server using NT Authentication.
The logins have Security Administrator, Disk
Administrator and Database Creator Server Roles.
Staff memebers create a new database with Enterprise
Manager and are automatically the dbo of that database.
They then need to restore the database (again using EM)
from a backup file sent to us from various clients.
Obviously their login/user will not exist in this
external backup file we have been sent. When they restore
the database (and they are large so this can take an
hour), the restore is almost complete when it gives the
error :
Server user 'BLAH' is not a valid user in
database 'clientdb'.
RESTORE DATABASE is terminating abnormally
The user is no longer dbo or even part of the database
they just created.
IS there any way for me to get around this error without
making all our staff System Administrors ?
Thanks,
AlisonMost likely you are using SQL Server logins and they do not have the same SID numbers in the two
databases.
So, in the dest server, the login create a database and is the owner of the database. Then RESTORE.
This will make the dbo the SID of the originating SQL Server. Then EM tries to connect to the
database at the dest server, but cannot do this because of mis-matched SID for the dbo.
Best is if you can make sure the logins have the same SID, search KB for sp_help_revlogins. Also, to
understand the topic, read in Books Online about sp_change_users_login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alison" <anonymous@.discussions.microsoft.com> wrote in message
news:48b401c490af$9c53a320$a501280a@.phx.gbl...
> Our staff login to SQL Server using NT Authentication.
> The logins have Security Administrator, Disk
> Administrator and Database Creator Server Roles.
> Staff memebers create a new database with Enterprise
> Manager and are automatically the dbo of that database.
> They then need to restore the database (again using EM)
> from a backup file sent to us from various clients.
> Obviously their login/user will not exist in this
> external backup file we have been sent. When they restore
> the database (and they are large so this can take an
> hour), the restore is almost complete when it gives the
> error :
> Server user 'BLAH' is not a valid user in
> database 'clientdb'.
> RESTORE DATABASE is terminating abnormally
> The user is no longer dbo or even part of the database
> they just created.
> IS there any way for me to get around this error without
> making all our staff System Administrors ?
> Thanks,
> Alison|||As we receive these databases from our clients, we don't
know their logins or SIDs. They will not provide us with
any information other than the .bak file.
Is there any way to go about this without knowing the
SIDs ? Or is System Administration rights to our staff
members the only solution ?
>--Original Message--
>Most likely you are using SQL Server logins and they do
not have the same SID numbers in the two
>databases.
>So, in the dest server, the login create a database and
is the owner of the database. Then RESTORE.
>This will make the dbo the SID of the originating SQL
Server. Then EM tries to connect to the
>database at the dest server, but cannot do this because
of mis-matched SID for the dbo.
>Best is if you can make sure the logins have the same
SID, search KB for sp_help_revlogins. Also, to
>understand the topic, read in Books Online about
sp_change_users_login.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Alison" <anonymous@.discussions.microsoft.com> wrote in
message
>news:48b401c490af$9c53a320$a501280a@.phx.gbl...
>> Our staff login to SQL Server using NT Authentication.
>> The logins have Security Administrator, Disk
>> Administrator and Database Creator Server Roles.
>> Staff memebers create a new database with Enterprise
>> Manager and are automatically the dbo of that database.
>> They then need to restore the database (again using EM)
>> from a backup file sent to us from various clients.
>> Obviously their login/user will not exist in this
>> external backup file we have been sent. When they
restore
>> the database (and they are large so this can take an
>> hour), the restore is almost complete when it gives the
>> error :
>> Server user 'BLAH' is not a valid user in
>> database 'clientdb'.
>> RESTORE DATABASE is terminating abnormally
>> The user is no longer dbo or even part of the database
>> they just created.
>> IS there any way for me to get around this error
without
>> making all our staff System Administrors ?
>> Thanks,
>> Alison
>
>.
>|||How about not doing the restore from EM, but use QA instead. And after the restore change the
database owner, using sp_changedbowner (to "sa", for instance).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alison" <anonymous@.discussions.microsoft.com> wrote in message
news:545501c49140$121f9290$a301280a@.phx.gbl...
> As we receive these databases from our clients, we don't
> know their logins or SIDs. They will not provide us with
> any information other than the .bak file.
> Is there any way to go about this without knowing the
> SIDs ? Or is System Administration rights to our staff
> members the only solution ?
>
> >--Original Message--
> >Most likely you are using SQL Server logins and they do
> not have the same SID numbers in the two
> >databases.
> >
> >So, in the dest server, the login create a database and
> is the owner of the database. Then RESTORE.
> >This will make the dbo the SID of the originating SQL
> Server. Then EM tries to connect to the
> >database at the dest server, but cannot do this because
> of mis-matched SID for the dbo.
> >
> >Best is if you can make sure the logins have the same
> SID, search KB for sp_help_revlogins. Also, to
> >understand the topic, read in Books Online about
> sp_change_users_login.
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Alison" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:48b401c490af$9c53a320$a501280a@.phx.gbl...
> >> Our staff login to SQL Server using NT Authentication.
> >> The logins have Security Administrator, Disk
> >> Administrator and Database Creator Server Roles.
> >>
> >> Staff memebers create a new database with Enterprise
> >> Manager and are automatically the dbo of that database.
> >> They then need to restore the database (again using EM)
> >> from a backup file sent to us from various clients.
> >> Obviously their login/user will not exist in this
> >> external backup file we have been sent. When they
> restore
> >> the database (and they are large so this can take an
> >> hour), the restore is almost complete when it gives the
> >> error :
> >>
> >> Server user 'BLAH' is not a valid user in
> >> database 'clientdb'.
> >> RESTORE DATABASE is terminating abnormally
> >>
> >> The user is no longer dbo or even part of the database
> >> they just created.
> >>
> >> IS there any way for me to get around this error
> without
> >> making all our staff System Administrors ?
> >>
> >> Thanks,
> >> Alison
> >
> >
> >.
> >|||Unfortunately, the same error is still experienced.
Processed 7360 pages for database 'TEST',
file 'IPSHOW_Data' on file 1.
Processed 1 pages for database 'TEST', file 'IPSHOW_Log'
on file 1.
Server: Msg 916, Level 14, State 1, Line 1
Server user 'ALISON' is not a valid user in
database 'TEST'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Any other ideas ?
>--Original Message--
>How about not doing the restore from EM, but use QA
instead. And after the restore change the
>database owner, using sp_changedbowner (to "sa", for
instance).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Alison" <anonymous@.discussions.microsoft.com> wrote in
message
>news:545501c49140$121f9290$a301280a@.phx.gbl...
>> As we receive these databases from our clients, we
don't
>> know their logins or SIDs. They will not provide us
with
>> any information other than the .bak file.
>> Is there any way to go about this without knowing the
>> SIDs ? Or is System Administration rights to our staff
>> members the only solution ?
>>
>> >--Original Message--
>> >Most likely you are using SQL Server logins and they
do
>> not have the same SID numbers in the two
>> >databases.
>> >
>> >So, in the dest server, the login create a database
and
>> is the owner of the database. Then RESTORE.
>> >This will make the dbo the SID of the originating SQL
>> Server. Then EM tries to connect to the
>> >database at the dest server, but cannot do this
because
>> of mis-matched SID for the dbo.
>> >
>> >Best is if you can make sure the logins have the same
>> SID, search KB for sp_help_revlogins. Also, to
>> >understand the topic, read in Books Online about
>> sp_change_users_login.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Alison" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:48b401c490af$9c53a320$a501280a@.phx.gbl...
>> >> Our staff login to SQL Server using NT
Authentication.
>> >> The logins have Security Administrator, Disk
>> >> Administrator and Database Creator Server Roles.
>> >>
>> >> Staff memebers create a new database with Enterprise
>> >> Manager and are automatically the dbo of that
database.
>> >> They then need to restore the database (again using
EM)
>> >> from a backup file sent to us from various clients.
>> >> Obviously their login/user will not exist in this
>> >> external backup file we have been sent. When they
>> restore
>> >> the database (and they are large so this can take an
>> >> hour), the restore is almost complete when it gives
the
>> >> error :
>> >>
>> >> Server user 'BLAH' is not a valid user in
>> >> database 'clientdb'.
>> >> RESTORE DATABASE is terminating abnormally
>> >>
>> >> The user is no longer dbo or even part of the
database
>> >> they just created.
>> >>
>> >> IS there any way for me to get around this error
>> without
>> >> making all our staff System Administrors ?
>> >>
>> >> Thanks,
>> >> Alison
>> >
>> >
>> >.
>> >
>
>.
>|||Next step I would try is to make certain the SID is the same on both servers for the logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:683001c493d7$7beb0cb0$a501280a@.phx.gbl...
> Unfortunately, the same error is still experienced.
> Processed 7360 pages for database 'TEST',
> file 'IPSHOW_Data' on file 1.
> Processed 1 pages for database 'TEST', file 'IPSHOW_Log'
> on file 1.
> Server: Msg 916, Level 14, State 1, Line 1
> Server user 'ALISON' is not a valid user in
> database 'TEST'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Any other ideas ?
> >--Original Message--
> >How about not doing the restore from EM, but use QA
> instead. And after the restore change the
> >database owner, using sp_changedbowner (to "sa", for
> instance).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Alison" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:545501c49140$121f9290$a301280a@.phx.gbl...
> >> As we receive these databases from our clients, we
> don't
> >> know their logins or SIDs. They will not provide us
> with
> >> any information other than the .bak file.
> >>
> >> Is there any way to go about this without knowing the
> >> SIDs ? Or is System Administration rights to our staff
> >> members the only solution ?
> >>
> >>
> >> >--Original Message--
> >> >Most likely you are using SQL Server logins and they
> do
> >> not have the same SID numbers in the two
> >> >databases.
> >> >
> >> >So, in the dest server, the login create a database
> and
> >> is the owner of the database. Then RESTORE.
> >> >This will make the dbo the SID of the originating SQL
> >> Server. Then EM tries to connect to the
> >> >database at the dest server, but cannot do this
> because
> >> of mis-matched SID for the dbo.
> >> >
> >> >Best is if you can make sure the logins have the same
> >> SID, search KB for sp_help_revlogins. Also, to
> >> >understand the topic, read in Books Online about
> >> sp_change_users_login.
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >http://www.solidqualitylearning.com/
> >> >
> >> >
> >> >"Alison" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:48b401c490af$9c53a320$a501280a@.phx.gbl...
> >> >> Our staff login to SQL Server using NT
> Authentication.
> >> >> The logins have Security Administrator, Disk
> >> >> Administrator and Database Creator Server Roles.
> >> >>
> >> >> Staff memebers create a new database with Enterprise
> >> >> Manager and are automatically the dbo of that
> database.
> >> >> They then need to restore the database (again using
> EM)
> >> >> from a backup file sent to us from various clients.
> >> >> Obviously their login/user will not exist in this
> >> >> external backup file we have been sent. When they
> >> restore
> >> >> the database (and they are large so this can take an
> >> >> hour), the restore is almost complete when it gives
> the
> >> >> error :
> >> >>
> >> >> Server user 'BLAH' is not a valid user in
> >> >> database 'clientdb'.
> >> >> RESTORE DATABASE is terminating abnormally
> >> >>
> >> >> The user is no longer dbo or even part of the
> database
> >> >> they just created.
> >> >>
> >> >> IS there any way for me to get around this error
> >> without
> >> >> making all our staff System Administrors ?
> >> >>
> >> >> Thanks,
> >> >> Alison
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||That is the one bit of information I can not find out.
Our client just sends a .bak file and we load it onto our
server. That is all we get... no information at all,
just a .bak file.
So it is starting to look more and more like either one
person with system administration rights does all
restores and creates a new backup files once the users
are mapped to logins on our server OR I give all our
users System Admin rights (eeeK).
>--Original Message--
>Next step I would try is to make certain the SID is the
same on both servers for the logins.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:683001c493d7$7beb0cb0$a501280a@.phx.gbl...
>> Unfortunately, the same error is still experienced.
>> Processed 7360 pages for database 'TEST',
>> file 'IPSHOW_Data' on file 1.
>> Processed 1 pages for database 'TEST',
file 'IPSHOW_Log'
>> on file 1.
>> Server: Msg 916, Level 14, State 1, Line 1
>> Server user 'ALISON' is not a valid user in
>> database 'TEST'.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Any other ideas ?
>> >--Original Message--
>> >How about not doing the restore from EM, but use QA
>> instead. And after the restore change the
>> >database owner, using sp_changedbowner (to "sa", for
>> instance).
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Alison" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:545501c49140$121f9290$a301280a@.phx.gbl...
>> >> As we receive these databases from our clients, we
>> don't
>> >> know their logins or SIDs. They will not provide us
>> with
>> >> any information other than the .bak file.
>> >>
>> >> Is there any way to go about this without knowing
the
>> >> SIDs ? Or is System Administration rights to our
staff
>> >> members the only solution ?
>> >>
>> >>
>> >> >--Original Message--
>> >> >Most likely you are using SQL Server logins and
they
>> do
>> >> not have the same SID numbers in the two
>> >> >databases.
>> >> >
>> >> >So, in the dest server, the login create a database
>> and
>> >> is the owner of the database. Then RESTORE.
>> >> >This will make the dbo the SID of the originating
SQL
>> >> Server. Then EM tries to connect to the
>> >> >database at the dest server, but cannot do this
>> because
>> >> of mis-matched SID for the dbo.
>> >> >
>> >> >Best is if you can make sure the logins have the
same
>> >> SID, search KB for sp_help_revlogins. Also, to
>> >> >understand the topic, read in Books Online about
>> >> sp_change_users_login.
>> >> >--
>> >> >Tibor Karaszi, SQL Server MVP
>> >> >http://www.karaszi.com/sqlserver/default.asp
>> >> >http://www.solidqualitylearning.com/
>> >> >
>> >> >
>> >> >"Alison" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message
>> >> >news:48b401c490af$9c53a320$a501280a@.phx.gbl...
>> >> >> Our staff login to SQL Server using NT
>> Authentication.
>> >> >> The logins have Security Administrator, Disk
>> >> >> Administrator and Database Creator Server Roles.
>> >> >>
>> >> >> Staff memebers create a new database with
Enterprise
>> >> >> Manager and are automatically the dbo of that
>> database.
>> >> >> They then need to restore the database (again
using
>> EM)
>> >> >> from a backup file sent to us from various
clients.
>> >> >> Obviously their login/user will not exist in this
>> >> >> external backup file we have been sent. When they
>> >> restore
>> >> >> the database (and they are large so this can
take an
>> >> >> hour), the restore is almost complete when it
gives
>> the
>> >> >> error :
>> >> >>
>> >> >> Server user 'BLAH' is not a valid user in
>> >> >> database 'clientdb'.
>> >> >> RESTORE DATABASE is terminating abnormally
>> >> >>
>> >> >> The user is no longer dbo or even part of the
>> database
>> >> >> they just created.
>> >> >>
>> >> >> IS there any way for me to get around this error
>> >> without
>> >> >> making all our staff System Administrors ?
>> >> >>
>> >> >> Thanks,
>> >> >> Alison
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||> That is the one bit of information I can not find out.
> Our client just sends a .bak file and we load it onto our
> server. That is all we get... no information at all,
> just a .bak file.
I see, understandable...
> So it is starting to look more and more like either one
> person with system administration rights does all
> restores and creates a new backup files once the users
> are mapped to logins on our server
So, the sysadmin does the restore. Handle the dbo sid and possibly also user sid's. Then backup. And
hand that backup to the person who eventually need to do this. Am I understanding you correctly? If
so, seems like a valid strategy. This should be fairly straightforward to automate as well, if
needed.
> OR I give all our
> users System Admin rights (eeeK).
Yes, obviously the other option is to prefer... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:7b2b01c49547$8fd2a5a0$a601280a@.phx.gbl...
> That is the one bit of information I can not find out.
> Our client just sends a .bak file and we load it onto our
> server. That is all we get... no information at all,
> just a .bak file.
> So it is starting to look more and more like either one
> person with system administration rights does all
> restores and creates a new backup files once the users
> are mapped to logins on our server OR I give all our
> users System Admin rights (eeeK).
>>--Original Message--
>>Next step I would try is to make certain the SID is the
> same on both servers for the logins.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:683001c493d7$7beb0cb0$a501280a@.phx.gbl...
>> Unfortunately, the same error is still experienced.
>> Processed 7360 pages for database 'TEST',
>> file 'IPSHOW_Data' on file 1.
>> Processed 1 pages for database 'TEST',
> file 'IPSHOW_Log'
>> on file 1.
>> Server: Msg 916, Level 14, State 1, Line 1
>> Server user 'ALISON' is not a valid user in
>> database 'TEST'.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Any other ideas ?
>> >--Original Message--
>> >How about not doing the restore from EM, but use QA
>> instead. And after the restore change the
>> >database owner, using sp_changedbowner (to "sa", for
>> instance).
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Alison" <anonymous@.discussions.microsoft.com> wrote
> in
>> message
>> >news:545501c49140$121f9290$a301280a@.phx.gbl...
>> >> As we receive these databases from our clients, we
>> don't
>> >> know their logins or SIDs. They will not provide us
>> with
>> >> any information other than the .bak file.
>> >>
>> >> Is there any way to go about this without knowing
> the
>> >> SIDs ? Or is System Administration rights to our
> staff
>> >> members the only solution ?
>> >>
>> >>
>> >> >--Original Message--
>> >> >Most likely you are using SQL Server logins and
> they
>> do
>> >> not have the same SID numbers in the two
>> >> >databases.
>> >> >
>> >> >So, in the dest server, the login create a database
>> and
>> >> is the owner of the database. Then RESTORE.
>> >> >This will make the dbo the SID of the originating
> SQL
>> >> Server. Then EM tries to connect to the
>> >> >database at the dest server, but cannot do this
>> because
>> >> of mis-matched SID for the dbo.
>> >> >
>> >> >Best is if you can make sure the logins have the
> same
>> >> SID, search KB for sp_help_revlogins. Also, to
>> >> >understand the topic, read in Books Online about
>> >> sp_change_users_login.
>> >> >--
>> >> >Tibor Karaszi, SQL Server MVP
>> >> >http://www.karaszi.com/sqlserver/default.asp
>> >> >http://www.solidqualitylearning.com/
>> >> >
>> >> >
>> >> >"Alison" <anonymous@.discussions.microsoft.com>
> wrote
>> in
>> >> message
>> >> >news:48b401c490af$9c53a320$a501280a@.phx.gbl...
>> >> >> Our staff login to SQL Server using NT
>> Authentication.
>> >> >> The logins have Security Administrator, Disk
>> >> >> Administrator and Database Creator Server Roles.
>> >> >>
>> >> >> Staff memebers create a new database with
> Enterprise
>> >> >> Manager and are automatically the dbo of that
>> database.
>> >> >> They then need to restore the database (again
> using
>> EM)
>> >> >> from a backup file sent to us from various
> clients.
>> >> >> Obviously their login/user will not exist in this
>> >> >> external backup file we have been sent. When they
>> >> restore
>> >> >> the database (and they are large so this can
> take an
>> >> >> hour), the restore is almost complete when it
> gives
>> the
>> >> >> error :
>> >> >>
>> >> >> Server user 'BLAH' is not a valid user in
>> >> >> database 'clientdb'.
>> >> >> RESTORE DATABASE is terminating abnormally
>> >> >>
>> >> >> The user is no longer dbo or even part of the
>> database
>> >> >> they just created.
>> >> >>
>> >> >> IS there any way for me to get around this error
>> >> without
>> >> >> making all our staff System Administrors ?
>> >> >>
>> >> >> Thanks,
>> >> >> Alison
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>>
>>.

Permissions on Restore

Permissions Not Visible - SQL 2005 Management Studio

I'm trying to apply some security to my database, so i've created a role, added my user to the role and then set the security rights for the role. Everything work great, the security right behave exactly as defined...
However, when I go back the the Permissions pages, the values that i've already set don't appear in the lists (the all still work). In Fact they don't show up anywhere either in the permissions for the table / sp concerned or the right for the user or role.
Is it just me, or is it a problem with SQL 2005 Management Studio? or can anybody suggest a way of finding the securty rights of any given database object?
Microsoft SQL Server 2005 Beta 2
Microsoft SQL Server Management Studio 9.00.1116.00
Regards
Gary T

Security in SQL Server 2005 is Schema based, try these articles to get started. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3481751

http://www.windowsitpro.com/Article/ArticleID/42031/42031.html?Ad=1

|||

This has got me completely confused !!!!
In my simple world...
I would create a database role and the give this role the required rights to my database objects i.e the tables (select, insert update etc), stored procedures (execute etc). I would then create user accounts and add them to the required database roles...
CREATE ROLE [MyDB_AccessRole]
...Table def's...
GRANT SELECT ON [dbo].[MyTable] TO [MyDB_AccessRole] ..etc
...SP defs ...
GRANT EXECUTE ON [dbo].[MySP] TO [MyDB_AccessRole] ..etc
CREATE USER [DBUser] FOR LOGIN [DBUser]
EXEC sp_addrolemember N'MyDB_AccessRole, N'DBUser'

So far so good... this still all works in SQL 2005 (althought it nevers shows the rights anywhere?)
Then we have schemas...
CREATE SCHEMA [MyDB_AccessSchema] AUTHORIZATION [MyDB_AccessRole] ?

Is is true that the schema is just like the role? (i.e. A collection of database objects and permissions) if so, why do we need both.
Has anybody any simple examples (for dummies like me) that would explain the different between a role and a schema.
Having tried to create a role, I stiil don;t seem to be able to see the rights the user, role or schema has.
regards
Gary T

Tuesday, March 20, 2012

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

Monday, March 12, 2012

Permissions for linked server

This is a continuation from another thread in the SSIS forum. The broader issue is a Security/Permissions issue.

We are running SQL Server on a Windows 2003 server that is isolated from our main Novell network. The db admin does not want to load the Netware Client on the server for security reasons. As we continue to develop and extend our data access I am finding, as a developer, that I will need to frequently consume data and files store on the Novell network.

I did get the "net use" command working in a Job as a part of an SSIS package. This took a fair amount of research and trial and error to get working but at least it works. Now I am trying to use the same process to open a connection to Novell in the Query editor so I can setup a stored procedure that a Web service can call to open a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the net use command succeeds but does not make the conncetion availible to any other session. When I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. It works fine in a dos window opened manually. So I started looking at permissions. Whoami returns Administrator from the manual dos window. Whoami returns nt autority from the batch file. Since I have a valid Novell user/password in the net use command why does if fail? When I used impersonate it did not change the user from NT AUTHORITY/? I can't a connection open.

My goal is to find a method to connnect and disconnect to the Novell network as needed from SQL Server as well as from IIS Web applications. My Network Admin does not have any answers for me. I have done a ton of research and so far the only thing that has worked, at least partially, is "net use." Does anyone know how to overcome this issue? I know next to nothing about networking and security and certainly not how to get Windows and Novell to play well together without the Netware Client running. I do have the Gateway for Client Services running but not logged in for the net use function. Any help would be greatly appreciated!

Cory Bonallo

Retail Services

Developer

Please, I am still stuck. Does anyone have a clue on how to resolve this? Is there something different about permissions for the nt authority and administrator accounts at the server level? Something that would present different credentials to the Novell Server when trying to connect?

Cory

|||

Generally,

If your SQL Server service account is Localsystem then you will present credentials of domain\machine_name$ to other systems.

If your SQL Server service account is domain\domain_user then you will present credentials of domain\domain_user to other systems.

For xp_cmdshell there is a proxy account that can be set so the credentials sent out of sql server's process are the proxy accounts instead of the server's when the caller is not a sysadmin. If the caller is a sysadmin then xp_cmdshell will use the server's credentials.

hth,

-Steven Gott

S/DET

SQL Server

|||

Thank you for the reply. I tried changing the cridentials for xp_cmdshell but "whoami" still returns nt authority and the h: and dir commands fail. Why would the proxy account not take? I don't get it. That same batch works fine from the command prompt. The only difference I can see is that "whoami" returns Administrator there. I just don't understand enough to see the difference. The "net use" command succeeds either way, just the drive switch and dir fail.

Cory

|||

If you are calling xp_cmdshell as a sysadmin then the proxy account is not used.

hth,

-Steven Gott

S/DET

SQL Server

|||

Oh. But if I log in as me then I don't have write to execute xp_cmdshell.... hmmm.

Cory

|||I'm having the same problem with two SQLServer 2000 servers in different domains (single hop). sql accounts don't work unless they have system administrator privileges.
Help would be greatly appreciated.|||http://support.microsoft.com/?kbid=830382

apparently, is a bug.

Wednesday, March 7, 2012

permission problem?

hi,
for security reason, I changed the "BUILTIN\Administrators" Server Access
from "Permit" to "Deny".
but the SQLSERVERAGENT was failed to start. got this error as follow(even I
chagne the service logon account to another window("power user" group's)
user).
Any ideas?
Thanks...
Error:
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Service Control
Event ID: 103
Date: 08/03/2004
Time: 12:51:02 PM
User: N/A
Computer: LL_SERVER
Description:
SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.The windows account that Agent uses need to be able to login to SQL Server.
You removed the Administrators account from SQL Server, and couldn't login,
which indicates that the service account for Agent is Administrator (or some
other Windows account which is member of the Administrators group). So far
no surprise.
The windows group Power Users are not added as logins to SQL Server by the
installation program. Make sure that the service account for Agent has a
login in SQL Server and that login has sysadmin permissions in SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"js" <js@.someone@.hotmail.com> wrote in message
news:ewdgfaTBEHA.3284@.TK2MSFTNGP09.phx.gbl...
> hi,
> for security reason, I changed the "BUILTIN\Administrators" Server Access
> from "Permit" to "Deny".
> but the SQLSERVERAGENT was failed to start. got this error as follow(even
I
> chagne the service logon account to another window("power user" group's)
> user).
> Any ideas?
> Thanks...
> Error:
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Service Control
> Event ID: 103
> Date: 08/03/2004
> Time: 12:51:02 PM
> User: N/A
> Computer: LL_SERVER
> Description:
> SQLServerAgent could not be started (reason: Unable to connect to server
> '(local)'; SQLServerAgent cannot start).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
>|||Thanks Tibor.
I defined a user "BackupUser" as Power Users group. and add SQL sysadmin
permission to it. In order to test, I need to relogin to window as
"BackupUser".
I'm able to to login to SQL.
But when I try to start the SQL Agent service. still got an error:
Service msg box:
Could not start the SQLSERVERAGENT service on Local Computer.
Error5: Access is denied.
How to fix this one?
Another question is: If I don't login to windows(interact mode), the sql
jobs are able to run?
Thanks again.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lAmNuTBEHA.1548@.TK2MSFTNGP12.phx.gbl...
> The windows account that Agent uses need to be able to login to SQL
Server.
> You removed the Administrators account from SQL Server, and couldn't
login,
> which indicates that the service account for Agent is Administrator (or
some
> other Windows account which is member of the Administrators group). So far
> no surprise.
> The windows group Power Users are not added as logins to SQL Server by the
> installation program. Make sure that the service account for Agent has a
> login in SQL Server and that login has sysadmin permissions in SQL Server.
>|||What error messages do you have in the SQL Server Agent errorlog? Perhaps it
is quite simply a matter of Agent doesn't have permissions on the registry
keys or directories/files that it needs.
No, you don't have to be logged in interactively in order for your Agent
jobs to run.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"js" <js@.hotmail.com> wrote in message
news:eBBqWMUBEHA.2440@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor.
> I defined a user "BackupUser" as Power Users group. and add SQL sysadmin
> permission to it. In order to test, I need to relogin to window as
> "BackupUser".
> I'm able to to login to SQL.
> But when I try to start the SQL Agent service. still got an error:
> Service msg box:
> Could not start the SQLSERVERAGENT service on Local Computer.
> Error5: Access is denied.
> How to fix this one?
> Another question is: If I don't login to windows(interact mode), the sql
> jobs are able to run?
> Thanks again.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23lAmNuTBEHA.1548@.TK2MSFTNGP12.phx.gbl...
> Server.
> login,
> some
far
the
Server.
>|||Thanks Tibor.
There is no errorlog. only a messagebox pupup.
Error5: Access is denied.
The user is belong to a Power User group. Still need more permissions for
agent servie to run?
Can I do this instead:
Reable "permit" access to BUILTIN\Administraotrs, So the local system
account won't block.
and deny access for individual admin instead: domain\admin1, domain\admin2.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u8qw4XUBEHA.3748@.tk2msftngp13.phx.gbl...
> What error messages do you have in the SQL Server Agent errorlog? Perhaps
it
> is quite simply a matter of Agent doesn't have permissions on the registry
> keys or directories/files that it needs.
> No, you don't have to be logged in interactively in order for your Agent
> jobs to run.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "js" <js@.hotmail.com> wrote in message
> news:eBBqWMUBEHA.2440@.TK2MSFTNGP12.phx.gbl...
sysadmin
> in
(or
> far
> the
a
> Server.
>|||SQL Agent has an errorlog file, which you for instance can get to from EM,
Management, Right-click Agent.
I suggest you read in Books Online about permissions. Search for "level
token" and you will only get one hot, describing security needed to be in
place for the service accounts.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"js" <js@.someone@.hotmail.com> wrote in message
news:%23V1beiUBEHA.1380@.TK2MSFTNGP10.phx.gbl...
> Thanks Tibor.
> There is no errorlog. only a messagebox pupup.
> Error5: Access is denied.
> The user is belong to a Power User group. Still need more permissions for
> agent servie to run?
> Can I do this instead:
> Reable "permit" access to BUILTIN\Administraotrs, So the local system
> account won't block.
> and deny access for individual admin instead: domain\admin1,
domain\admin2.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u8qw4XUBEHA.3748@.tk2msftngp13.phx.gbl...
Perhaps
> it
registry
> sysadmin
sql
wrote
> (or
So
by
has
> a
>|||Thanks Tibor.
I try to start the service from EM, got this:
Service Control Failure:
An error 1053 - (The service did not respond to the start or control request
in a timely
fashion) occured while performing this service operation on the
SQLServerAgent serve.
I already followed by the steps in "Setting up Windows Services Accounts"
for adding the foler, registry permission.
what I'm missing?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%236uNvkUBEHA.3928@.TK2MSFTNGP11.phx.gbl...
> SQL Agent has an errorlog file, which you for instance can get to from EM,
> Management, Right-click Agent.
> I suggest you read in Books Online about permissions. Search for "level
> token" and you will only get one hot, describing security needed to be in
> place for the service accounts.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23V1beiUBEHA.1380@.TK2MSFTNGP10.phx.gbl...
for
> domain\admin2.
> in
> Perhaps
> registry
Agent
> sql
> wrote
SQL
couldn't
Administrator
group).
> So
Server
> by
> has
>|||Still, I need to know the error from Agent, in the Agent error file (you
didn't follow the direction I gave). You can also find the file in:
C:\Program Files\Microsoft SQL Server\MSSQL$FRESH\LOG
And the file name is SQLAGENT.OUT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"js" <js@.someone@.hotmail.com> wrote in message
news:%23Qg4WuUBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor.
> I try to start the service from EM, got this:
> Service Control Failure:
> An error 1053 - (The service did not respond to the start or control
request
> in a timely
> fashion) occured while performing this service operation on the
> SQLServerAgent serve.
> I already followed by the steps in "Setting up Windows Services Accounts"
> for adding the foler, registry permission.
> what I'm missing?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%236uNvkUBEHA.3928@.TK2MSFTNGP11.phx.gbl...
EM,
in
> for
wrote
> Agent
the
> SQL
> couldn't
> Administrator
> group).
> Server
Agent
SQL
>|||Thanks Tibor.
There is SQLAGENT.OUT file in my SQL LOG folder.
There are some ERRORLOG.x and SQLAGENT.x files( x is a number)
if I specify the wrong account password for the Agent service. then I got
the error:
An error 1069 - (The service did not start due to a logon failure) occured
while performing this service
operation on the SQLServerAgent service.
If I specify it correctly, after waiting for a while(hourglass on EM) and
then got another error:
An error 1053 - ( The service did not respond to the start or control
request in a timely fashion) occured while performing this service operation
on the SQLServerAgent servcie.
again, no SQLAGENT.OUT file under that SQL log folder.
what is next?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMd$BzUBEHA.2600@.TK2MSFTNGP12.phx.gbl...
> Still, I need to know the error from Agent, in the Agent error file (you
> didn't follow the direction I gave). You can also find the file in:
> C:\Program Files\Microsoft SQL Server\MSSQL$FRESH\LOG
> And the file name is SQLAGENT.OUT
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23Qg4WuUBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> request
Accounts"
> in
> EM,
"level
> in
permissions
system
> wrote
as
error:
> the
<tibor_please.no.email_karaszi@.hotmail.nomail.com>
to
> Agent
> SQL
>|||Sorry.
There is no SQLAGENT.OUT file in my SQL LOG folder.
"js" <js@.someone@.hotmail.com> wrote in message
news:uay0hiVBEHA.3548@.TK2MSFTNGP10.phx.gbl...
> Thanks Tibor.
> There is SQLAGENT.OUT file in my SQL LOG folder.
> There are some ERRORLOG.x and SQLAGENT.x files( x is a number)
> if I specify the wrong account password for the Agent service. then I got
> the error:
> An error 1069 - (The service did not start due to a logon failure) occured
> while performing this service
> operation on the SQLServerAgent service.
> If I specify it correctly, after waiting for a while(hourglass on EM) and
> then got another error:
> An error 1053 - ( The service did not respond to the start or control
> request in a timely fashion) occured while performing this service
operation
> on the SQLServerAgent servcie.
> again, no SQLAGENT.OUT file under that SQL log folder.
> what is next?
>
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OMd$BzUBEHA.2600@.TK2MSFTNGP12.phx.gbl...
> Accounts"
wrote
from
> "level
be
> permissions
> system
errorlog?
the
your
SQL
window
> as
> error:
mode),
> <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> to
in
>

Monday, February 20, 2012

Permission Denied to object

We recently brought up SQL Server and migrated our Access 2000 back end DB t
o
it. The SQL Server security seemed pretty straight forward to me...I set up
the users and the roles and assigned the roles rights to my tables. The
problem is that my users get a Permission Denied error when ever the Access
2000 front end tries to access any SQL Server table unless I add them to the
System Administrator server role. I've gone over everything and I can't see
m
to figure this out. The only thing I can find that seems strange is that on
that database, my login name is associated with the "dbo" name. I think tha
t
makes me the owner of the db and I'm wondering if that is what is causing my
issue. I'd greatly appreciate any insight on how to get this issue taken
care of.
Thanks in advance!
JimNevermind, I found the answer to my problem in another post. Don't assign
the db_denydatareader or db_denydatawriter role to the user. That cleared u
p
my problem.
"Jim" wrote:

> We recently brought up SQL Server and migrated our Access 2000 back end DB
to
> it. The SQL Server security seemed pretty straight forward to me...I set
up
> the users and the roles and assigned the roles rights to my tables. The
> problem is that my users get a Permission Denied error when ever the Acces
s
> 2000 front end tries to access any SQL Server table unless I add them to t
he
> System Administrator server role. I've gone over everything and I can't s
eem
> to figure this out. The only thing I can find that seems strange is that
on
> that database, my login name is associated with the "dbo" name. I think t
hat
> makes me the owner of the db and I'm wondering if that is what is causing
my
> issue. I'd greatly appreciate any insight on how to get this issue taken
> care of.
> Thanks in advance!
> Jim

Permission Denied Error

Monday, March 26, 2012

Permissions with views and tables

Permissions when restoring a database

Permissions used to Connect to SSIS on remote machine

Permissions used to Connect to SSIS on remote machine

Permissions to See Server Logins/Create Database Users

Friday, March 23, 2012

Permissions to Deploy and view RS Homepage

Wednesday, March 21, 2012

Permissions Problem

Permissions on Restore

Permissions on Restore

Permissions on Restore

Permissions Not Visible - SQL 2005 Management Studio

Tuesday, March 20, 2012

permissions MDF LDF

Monday, March 12, 2012

Permissions for linked server

Wednesday, March 7, 2012

permission problem?

Monday, February 20, 2012

Permission Denied to object

Followers

Blog Archive

About Me