Permission Denied Error: logins

Showing posts with label logins. Show all posts

Monday, March 26, 2012

Permissions when restoring a database

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

Note that to add a user to a database, the dba does not need to be able to see the login's metadata - he only needs to know the login's name.

To see the information about a login, you need VIEW DEFINITION permission on that login.

To see information about all logins, you would need VIEW ANY DEFINITION permission, but this permission allows you to see more than just login information, so I don't recommend granting this permission. Instead, you can look at creating a procedure to return the necessary login information and sign the procedure with a certificate that has VIEW ANY DEFINITION permission.

Thanks
Laurentiu

sql

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 create database diagrams...

Hello...
Am I able to grant permissions to sql server user logins to create and
manage database diagrams without making them db_owner or system
administrator? I am using SQL Server 2000. If I am able to, what do I need
to do to grant the permissions?
Please advise...
Thank you in advanced,
BrettBrett,
ddl_admin database fixed role membership ought to be sufficient.
Ilya
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:%2387%236oxAFHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hello...
> Am I able to grant permissions to sql server user logins to create and
> manage database diagrams without making them db_owner or system
> administrator? I am using SQL Server 2000. If I am able to, what do I
need
> to do to grant the permissions?
> Please advise...
> Thank you in advanced,
> Brett
>sql

Wednesday, March 21, 2012

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 of Logins

I made a new login for my database. In the database access
tab i only checked my database as permit and gave the login
no server roles. But when I log on to the SQL Server with
that login, I can see all Databases and their tables on the
server. Why is that?
Thanks, Rainer.
hi Rainer,
"Rainer Halanek" <anonymous@.discussions.microsoft.com> ha scritto nel
messaggio news:03e001c4b697$37083c20$a401280a@.phx.gbl
> I made a new login for my database. In the database access
> tab i only checked my database as permit and gave the login
> no server roles. But when I log on to the SQL Server with
> that login, I can see all Databases and their tables on the
> server. Why is that?
>
if your login only is associated to 1 database users and for only 1
database, you should get
err 916 Server user 'x' is not a valid user in database 'db_name'
raised by Enterprise Manager...
please verify the login is not member of a particular server role
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
You aksed if the login is associated to a database user. I
don't really understand that. I thougth that the login is a
database user. I associated it with one database (the only
one where I checked the permit flag). Maybe I did something
wrong?
Thanks, rainer.

>--Original Message--
>hi Rainer,
>"Rainer Halanek" <anonymous@.discussions.microsoft.com> ha
scritto nel
>messaggio news:03e001c4b697$37083c20$a401280a@.phx.gbl
>if your login only is associated to 1 database users and
for only 1
>database, you should get
>err 916 Server user 'x' is not a valid user in database
'db_name'
>raised by Enterprise Manager...
>please verify the login is not member of a particular
server role
>--
>Andrea Montanari (Microsoft MVP - SQL Server)
>http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
>DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
>(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE
2000 a visual
>interface)
>-- remove DMO to reply
>.
>
|||Rainer,
You will still be able to see all the databases on the server. You will
also still have access to anything the public role has access to on the
system databases. You won't be able to actually open up any of the stored
procedures/tables/etc on the server other databases though. I would
recommened deleting the guest account and restricting the public role
though. You can read more about SQL Server security at www.sqlsecurity.com.
<anonymous@.discussions.microsoft.com> wrote in message
news:046c01c4b83f$6e781370$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Andrea,
> You aksed if the login is associated to a database user. I
> don't really understand that. I thougth that the login is a
> database user. I associated it with one database (the only
> one where I checked the permit flag). Maybe I did something
> wrong?
> Thanks, rainer.
> scritto nel
> for only 1
> 'db_name'
> server role
> 2000 a visual
|||This is also something that changes with SQL Express and with SQL Server
2005. You will no longer be able to see objects that you have no access to.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eh1l91RuEHA.3496@.TK2MSFTNGP10.phx.gbl...
> Rainer,
> You will still be able to see all the databases on the server. You will
> also still have access to anything the public role has access to on the
> system databases. You won't be able to actually open up any of the stored
> procedures/tables/etc on the server other databases though. I would
> recommened deleting the guest account and restricting the public role
> though. You can read more about SQL Server security at
> www.sqlsecurity.com.
> <anonymous@.discussions.microsoft.com> wrote in message
> news:046c01c4b83f$6e781370$a501280a@.phx.gbl...
>

Permissions not effective for Windows Authentication login

Hello All,

I'm hoping someone can help me with this puzzle.

Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....

My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -

SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.

So I decided to try the simplest possible scenario to make it work:

I've created a login DOMAIN\newEmployee with Windows authentication.

DOMAIN\newEmployee has been granted access to databasename.

By default, DOMAIN\newEmployee is a member of Public.

Public has been granted all available permissions on all objects.

ie... grant all on userTables to public

........grant all on sysobjects to public

........grant all on otherSystemTables to public

etc.

Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAIN\newEmployee, querying -

select * from dbo.sysobjects

via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).

To compare, I then created a login newEmployee2 with SQL Server authentication.

newEmployee2 has been granted access to databasename.

select * from dbo.sysobjects

runs successfully from Query Analyser (as to any queries on user-created tables).

What else is required to grant access to tables from a Windows authenticated login?

( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )

Any help would be appreciated.

Thanks.

Kim.

Moved to Security.|||

Let me see if I understand the scenario, please correct me if I am missing something:

· DOMAIN\newEmployee & DOMAIN\newEmploee2 are Windows domain users

· DOMAIN\newEmployee is a member of existingRole

· public has been granted the permissions you mentioned.

· DOMAIN\newEmploee2 can select from dbo.sysobjects

· DOMAIN\newEmploee cannot select from dbo.sysobjects and gets back a permission denied error

From your description, it seems like the most likely cause is that newEmployee has an explicit denied permission on dbo.sysobjects either directly or via a role membership. Check the permissions for all the roles and groups that newEmployee is a member of.

BTW. Some of the objects and permissions that you mentioned here are deprecated, they will still work on SQL Server 2005, but they are supported only for backwards compatibility.

Let us know if this information was useful or/and if you have further question, please also let us know what version of SQL Server you are using in order to better assist you.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

D'oh !! I've checked db_denydatareader instead of db_datawriter.

I'll now crawl into a hole and die of embarrassment.

Thanks for the help.

Kim.

Tuesday, March 20, 2012

Permissions Hierarchy - GRANT on logins/users/roles

A question on the permissions hierarchy:

Since logins, database users, and database roles are both principals and securables - what does it mean to GRANT permission on a login/user/role to another principal? Does it mean that for a login - you can GRANT permission to EXECUTE AS that login or modify it, for example?

Thanks!

Yes, you can grant IMPERSONATE to execute as that principal or ALTER/CONTROL to allow its modification.

Laurentiu|||Thanks again!

Friday, March 9, 2012

Permissioning problem in SQL 2005...need help please!

I'm doing some data-driven scripting and I have a generic test case database with logins, etc. in it. I'm having trouble with the SQL permissioning to the database. I'm referencing a system DSN in TestPartner. As long as I have the DSN set up to use my domain account (integrated security), I can read and write to the database all day. My domain account has the sysadmin role, but no explicit database permissions. I set up a user on the SQL Server called tpuser, and gave him the same sysadmin role. However, that user cannot read or write to the same database.

I then removed the sysadmin role and gave tpuser explicit permissions to the database in the 'user mapping' section (the preferred method). I set the default schema as dbo and selected db_owner and public under 'database role membership'. The user still can't read or write to the database. What am I doing wrong?

When you connect using the tpuser, execute the following command from your application:

select suser_sname()

If you are not getting back tpuser, then you are not logged in with those credentials. If you created a standard SQL Server login, assigned it to the sysadmin role, and then logged in with that account, it will have the authority to do anything within SQL Server.

Wednesday, March 7, 2012

Permission Questions After running DTS TransferLogins

I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:
> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the new
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership of
> the public role cannot be changed" AND there I am unable to make any changes
> to the public ROLE. Can some one tell me what I am missing ' Thanks you..
>
>

Permission Questions After running DTS TransferLogins

I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:

> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the n
ew
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership o
f
> the public role cannot be changed" AND there I am unable to make any chang
es
> to the public ROLE. Can some one tell me what I am missing ' Thanks you
.
>
>

Saturday, February 25, 2012

permission needed to run CmdExec job steps

Hi, Can someone tell me what permissions are needed for logins to be able
to run CmdExec Jobs'
I recently removed everyone from System Admin and have been working through
all kind of issues. I now have some jobs that fail and here is the message
that they fail with>> "Non-SysAdmins have been denied permission to run
CmdExec job steps. The step failed." There are 3 Analysts who create DTS
packages and schedule them to run at various times, so they need to own
their jobs, but what it the minimum permissions they can be provided to be
able to run these type of jobs'
Thank you..How you would go about this depends on the version of SQL
Server - it's different for all versions. Since you mention
DTS I am guessing you are on SQL Server 2000.
On 2000, if a non-sysadmin is going to be running a CmdExec
job, first you need to enable SQL Agent to allow
non-sysadmins to execute CmdExec steps. Right click on SQL
Agent, select properties and then go to the Job System tab.
From here, remove the check which restricts CmdExec and
ActiveX job steps to Sysadmins only.
You will then need to configure the proxy account. The proxy
account is the security context that will be used for the
jobs. You can find information on the proxy account as well
as some of the security issues you are running into in books
online under: xp_sqlagent_proxy_account
-Sue
On Tue, 5 Sep 2006 16:08:48 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:

>Hi, Can someone tell me what permissions are needed for logins to be able
>to run CmdExec Jobs'
>I recently removed everyone from System Admin and have been working through
>all kind of issues. I now have some jobs that fail and here is the message
>that they fail with>> "Non-SysAdmins have been denied permission to run
>CmdExec job steps. The step failed." There are 3 Analysts who create DTS
>packages and schedule them to run at various times, so they need to own
>their jobs, but what it the minimum permissions they can be provided to be
>able to run these type of jobs'
>Thank you..
>|||Thanks Sue, You are correct SQL2000 is what I speak of. I believe you have
provided me with all the right stuff. I will read what I can find under
xp_sqlagent_proxy_account.. Thanks again.
=======================================
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nfvrf2dp55icp6a9r02i0hfdndjp77dr4k@.
4ax.com...
> How you would go about this depends on the version of SQL
> Server - it's different for all versions. Since you mention
> DTS I am guessing you are on SQL Server 2000.
> On 2000, if a non-sysadmin is going to be running a CmdExec
> job, first you need to enable SQL Agent to allow
> non-sysadmins to execute CmdExec steps. Right click on SQL
> Agent, select properties and then go to the Job System tab.
> From here, remove the check which restricts CmdExec and
> ActiveX job steps to Sysadmins only.
> You will then need to configure the proxy account. The proxy
> account is the security context that will be used for the
> jobs. You can find information on the proxy account as well
> as some of the security issues you are running into in books
> online under: xp_sqlagent_proxy_account
> -Sue
> On Tue, 5 Sep 2006 16:08:48 -0500, "WANNABE" <breichenbach
> AT istate DOT com> wrote:
>
>

Permission Denied Error

Monday, March 26, 2012

Permissions when restoring a database

Permissions to See Server Logins/Create Database Users

Permissions to See Server Logins/Create Database Users

Friday, March 23, 2012

Permissions to create database diagrams...

Wednesday, March 21, 2012

Permissions on Restore

Permissions on Restore

Permissions on Restore

Permissions of Logins

Permissions not effective for Windows Authentication login

Tuesday, March 20, 2012

Permissions Hierarchy - GRANT on logins/users/roles

Friday, March 9, 2012

Permissioning problem in SQL 2005...need help please!

Wednesday, March 7, 2012

Permission Questions After running DTS TransferLogins

Permission Questions After running DTS TransferLogins

Saturday, February 25, 2012

permission needed to run CmdExec job steps

Followers

Blog Archive

About Me