permissions via roles query (SQL Server 2000)

Is anybody willing to share a query which shows all permissions granted
to a user, including permissions granted via roles? The complexity is
that a role can be granted to a role, and therefore this becomes a
bill-of-materials explosion / tree hierarchy / adjacency list problem.
Example:
create role r1
grant select on t1 to r1
grant select on t2 to r1
create role r2
grant select on t3 to r2
grant select on t4 to r2
create role r3
grant r1 to r3
grant r2 to r3
grant r3 to user1
The end result is that you want to be able to see that user1 has select
on t1, t2, t3, t4. An added bonus would be able to see the lineage.
I have found general discussion about solving these kinds of problems.
I'm curious if anybody has a working example for permissions and roles
in SQLServer 2000.sp_helprotect
<rc8740@.netscape.net> wrote in message
news:1149690495.671940.102730@.i39g2000cwa.googlegroups.com...
> Is anybody willing to share a query which shows all permissions granted
> to a user, including permissions granted via roles? The complexity is
> that a role can be granted to a role, and therefore this becomes a
> bill-of-materials explosion / tree hierarchy / adjacency list problem.
> Example:
> create role r1
> grant select on t1 to r1
> grant select on t2 to r1
> create role r2
> grant select on t3 to r2
> grant select on t4 to r2
> create role r3
> grant r1 to r3
> grant r2 to r3
> grant r3 to user1
> The end result is that you want to be able to see that user1 has select
> on t1, t2, t3, t4. An added bonus would be able to see the lineage.
> I have found general discussion about solving these kinds of problems.
> I'm curious if anybody has a working example for permissions and roles
> in SQLServer 2000.
>|||There are some good ones up on sqlservercentral.com
It sounds like you may be looking for one like this one:
http://www.sqlservercentral.com/scr...butions/268.asp
-Sue
On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:

>Is anybody willing to share a query which shows all permissions granted
>to a user, including permissions granted via roles? The complexity is
>that a role can be granted to a role, and therefore this becomes a
>bill-of-materials explosion / tree hierarchy / adjacency list problem.
>Example:
>create role r1
>grant select on t1 to r1
>grant select on t2 to r1
>create role r2
>grant select on t3 to r2
>grant select on t4 to r2
>create role r3
>grant r1 to r3
>grant r2 to r3
>grant r3 to user1
>The end result is that you want to be able to see that user1 has select
>on t1, t2, t3, t4. An added bonus would be able to see the lineage.
>I have found general discussion about solving these kinds of problems.
>I'm curious if anybody has a working example for permissions and roles
>in SQLServer 2000.|||There are some good ones up on sqlservercentral.com
It sounds like you may be looking for one like this one:
http://www.sqlservercentral.com/scr...butions/268.asp
-Sue
On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:

>Is anybody willing to share a query which shows all permissions granted
>to a user, including permissions granted via roles? The complexity is
>that a role can be granted to a role, and therefore this becomes a
>bill-of-materials explosion / tree hierarchy / adjacency list problem.
>Example:
>create role r1
>grant select on t1 to r1
>grant select on t2 to r1
>create role r2
>grant select on t3 to r2
>grant select on t4 to r2
>create role r3
>grant r1 to r3
>grant r2 to r3
>grant r3 to user1
>The end result is that you want to be able to see that user1 has select
>on t1, t2, t3, t4. An added bonus would be able to see the lineage.
>I have found general discussion about solving these kinds of problems.
>I'm curious if anybody has a working example for permissions and roles
>in SQLServer 2000.|||I was really hopeful when I found this...but I'm getting zero records when
I run the SP created by the script.... any other tools or suggestions?
Thanks.
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:u7sr8217iivmuojp2cq7msh6mc7vplde0f@.
4ax.com...
> There are some good ones up on sqlservercentral.com
> It sounds like you may be looking for one like this one:
> http://www.sqlservercentral.com/scr...butions/268.asp
> -Sue
> On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:
>
>|||I was really hopeful when I found this...but I'm getting zero records when
I run the SP created by the script.... any other tools or suggestions?
Thanks.
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:u7sr8217iivmuojp2cq7msh6mc7vplde0f@.
4ax.com...
> There are some good ones up on sqlservercentral.com
> It sounds like you may be looking for one like this one:
> http://www.sqlservercentral.com/scr...butions/268.asp
> -Sue
> On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:
>
>

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

Permissions to a standard database role

Q: A standard database role named abc is created and assigned permissions to
db_reader and db_writer roles.
Then the database users are made members to the role abc.
(The DatabaseAccess under users displays viaGroupMembership.)
But why doesnot users have access to the database?
(SQLServer2000)Hi,
Please let me now what error message, if any, you receive when you attempt
to access the database. Also, if you have auditing enabled, what error
message, if any, do you receive in the SQL Error Log or the Application
Event Log?
Based on my test, the configuration you describe should work OK.
Thanks.
Gary
This posting is provided "AS IS" with no warranties, and confers no rights.

Permissions set to Roles disappeared

We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.

We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.

Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)

Tim.

Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.

Thanks

Laurentiu

|||

This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

use aspstate

go

GRANT EXEC ON TempGetStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;

GO

GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;

GO

GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempResetTimeout TO MyPeakASPState;

GO

GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;

GO

GRANT EXEC ON DropTempTables TO MyPeakASPState;

GO

GRANT EXEC ON GetMajorVersion TO MyPeakASPState;

GO

GRANT EXEC ON CreateTempTables TO MyPeakASPState;

GO

GRANT EXEC ON ResetData TO MyPeakASPState;

GO

GRANT EXEC ON TempGetAppID TO MyPeakASPState

|||

I wish I could help you. I'm interested that it happened to someone else.

The only advice I can give you - becareful not to change logins when changing security.

My problem may have occurred because I was testing security on a user.

Tim.

|||

I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, I didn't know that.

Permissions set to Roles disappeared

We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.

We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.

Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)

Tim.

Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.

Thanks

Laurentiu

|||

This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

use aspstate

go

GRANT EXEC ON TempGetStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;

GO

GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;

GO

GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempResetTimeout TO MyPeakASPState;

GO

GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;

GO

GRANT EXEC ON DropTempTables TO MyPeakASPState;

GO

GRANT EXEC ON GetMajorVersion TO MyPeakASPState;

GO

GRANT EXEC ON CreateTempTables TO MyPeakASPState;

GO

GRANT EXEC ON ResetData TO MyPeakASPState;

GO

GRANT EXEC ON TempGetAppID TO MyPeakASPState

|||

I wish I could help you. I'm interested that it happened to someone else.

The only advice I can give you - becareful not to change logins when changing security.

My problem may have occurred because I was testing security on a user.

Tim.

|||

I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, I didn't know that.

Permissions set to Roles disappeared

We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.

We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.

Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)

Tim.

Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.

Thanks

Laurentiu

|||

This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

use aspstate

go

GRANT EXEC ON TempGetStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;

GO

GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;

GO

GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempResetTimeout TO MyPeakASPState;

GO

GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;

GO

GRANT EXEC ON DropTempTables TO MyPeakASPState;

GO

GRANT EXEC ON GetMajorVersion TO MyPeakASPState;

GO

GRANT EXEC ON CreateTempTables TO MyPeakASPState;

GO

GRANT EXEC ON ResetData TO MyPeakASPState;

GO

GRANT EXEC ON TempGetAppID TO MyPeakASPState

|||

I wish I could help you. I'm interested that it happened to someone else.

The only advice I can give you - becareful not to change logins when changing security.

My problem may have occurred because I was testing security on a user.

Tim.

|||

I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, I didn't know that.

sql

Permissions Question

When I hit the Report Manager website on one of my installations and log-in
(With a user name that has System Administrator and System User roles) I am
presented with the Report Manager screen but am missing most of the
controls: I have the Home|My Subscriptions, ... in the upper right, but the
Contents and Properties tab are missing and there are no New FOlder, New
Data SOurce, etc. controls on the bar below where it says HOME.
Also, When I try to deploy reports from VS to this server (Same log-in) I
get : The permissions granted to user '...' are insufficient for performing
this operation.
Any Ideas?Go to IIS Management Console and make the ReportServer virtual directory a Secure website by Managing Permissions. This will allow updates via Visual Studio.
You may then find that you cannot that you cannot reports from a client machine. Go bak and make the ReportServer a public website. Strange but TRUE, at least in my environment
--
R. Festino
Florida
"news.microsoft.com" wrote:
> When I hit the Report Manager website on one of my installations and log-in
> (With a user name that has System Administrator and System User roles) I am
> presented with the Report Manager screen but am missing most of the
> controls: I have the Home|My Subscriptions, ... in the upper right, but the
> Contents and Properties tab are missing and there are no New FOlder, New
> Data SOurce, etc. controls on the bar below where it says HOME.
> Also, When I try to deploy reports from VS to this server (Same log-in) I
> get : The permissions granted to user '...' are insufficient for performing
> this operation.
> Any Ideas?
>
>sql

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 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 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!

permissions for developers not working after 2005 upgrade

Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.

After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?

Can you please elaborate on the access difficulty? Are you encountering errors and, if yes, what are those errors? It would help if you could give us an example of some action that used to work and now doesn't, and of what is the system response in this case.

Thanks
Laurentiu

|||

They are making a connection through Visual Studio using the MS OLE DB Provider for SQL Server with their domain account and receiving the following error...

SELECT permission denied on object 'Contact_Info', database 'GetLean', schema 'dbo'.

|||

Could you check what is the current execution context at the time when this error is obtained? You can use Profiler to figure out what is the current execution context.

There are two possibilities:

(1) current execution context is not a member of db_datareader, so you do not have SELECT permission.

(2) current execution context is explicitly denied SELECT permission on the Contact_Info table.

If you determine the current execution context, then you can check whether it's a member of db_datareader by looking at the sys.database_role_members catalog. You can check for the SELECT permission being denied by looking at the sys.database_permissions catalog.

Thanks
Laurentiu

|||

They are a member of the db_datareader, db_datawriter, db_ddladmin, and db_securityadmin roles. There are no permissions explicitly denied in this database.

Even though they have db_datareader, db_datawriter, db_ddladmin, and db_securityadmin rights....must I still assign them to the dbo schema?

|||

db_datareader grants select on the entire database, hence on the dbo schema as well (http://msdn2.microsoft.com/en-us/library/ms189612.aspx). You don't need to do a special permission grant for the dbo schema.

Can you try a little experiment? Create a test table in the dbo schema and then verify if those members of db_datareader can access it. Also, create a separate schema and a table in it and see if there is the same behavior for it as for the dbo schema.

Also, does this happen for all those developers or only for some? If some of them were added to db_denydatareader role, then that would prevent them from selecting from anything. Are they members of other roles than those four that you mentioned?

Thanks
Laurentiu

|||

It turned out not to be a DBA problem. Sorry, should have posted the resolution earlier.

It turned out to be a developer issue...a combination of failing to practice current standards and inexperience with Visual Studio. Aargh!

permissions for custom roles

I am on sql 2000 sp 4, windows 2003.
I am looking for a way to create custom database roles that are more
exacting than db_datareader, db_dbowner etc. What I need to do is to
create roles for the help desk personnel where they can view scheduled
jobs, but not edit them or create them. I also need to grant access to
edit DTS packages but not delete or create the. Does anyone have any
experience or good articles about this?
Thanks!
KristinaKristina,
Did you already get help through googlegroups? A couple of simple things on
SQL Server 2000.
Make those who should monitor jobs and schedules but not change them members
of the msdb TargetServersRole role. (Member of this role _cannot_ create new
jobs, so do not use this for someone who must also create jobs.)
Regarding DTS packages, I keep them stored on the file system, not in msdb.
Therefore, I control edit rights by giving the needed users Read access to
the DTS folders, but not Change rights.
RLF
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1184982108.759067.184370@.r34g2000hsd.googlegroups.com...
>I am on sql 2000 sp 4, windows 2003.
> I am looking for a way to create custom database roles that are more
> exacting than db_datareader, db_dbowner etc. What I need to do is to
> create roles for the help desk personnel where they can view scheduled
> jobs, but not edit them or create them. I also need to grant access to
> edit DTS packages but not delete or create the. Does anyone have any
> experience or good articles about this?
> Thanks!
> Kristina
>|||On Jul 23, 10:44 am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> Kristina,
> Did you already get help through googlegroups? A couple of simple things on
> SQL Server 2000.
> Make those who should monitor jobs and schedules but not change them members
> of the msdb TargetServersRole role. (Member of this role _cannot_ create new
> jobs, so do not use this for someone who must also create jobs.)
> Regarding DTS packages, I keep them stored on the file system, not in msdb.
> Therefore, I control edit rights by giving the needed users Read access to
> the DTS folders, but not Change rights.
> RLF"Kristina" <Kristina...@.gmail.com> wrote in message
> news:1184982108.759067.184370@.r34g2000hsd.googlegroups.com...
>
> >I am on sql 2000 sp 4, windows 2003.
> > I am looking for a way to create custom database roles that are more
> > exacting than db_datareader, db_dbowner etc. What I need to do is to
> > create roles for the help desk personnel where they can view scheduled
> > jobs, but not edit them or create them. I also need to grant access to
> > edit DTS packages but not delete or create the. Does anyone have any
> > experience or good articles about this?
> > Thanks!
> > Kristina- Hide quoted text -
> - Show quoted text -
Russell,
Thanks as always. This is the perfect advice I need! I have a crazy
new contract and I am the developer/DBA etc and don't have time to
research etc.
Kristina

permissions for custom roles

I am on sql 2000 sp 4, windows 2003.
I am looking for a way to create custom database roles that are more
exacting than db_datareader, db_dbowner etc. What I need to do is to
create roles for the help desk personnel where they can view scheduled
jobs, but not edit them or create them. I also need to grant access to
edit DTS packages but not delete or create the. Does anyone have any
experience or good articles about this?
Thanks!
Kristina
Kristina,
Did you already get help through googlegroups? A couple of simple things on
SQL Server 2000.
Make those who should monitor jobs and schedules but not change them members
of the msdb TargetServersRole role. (Member of this role _cannot_ create new
jobs, so do not use this for someone who must also create jobs.)
Regarding DTS packages, I keep them stored on the file system, not in msdb.
Therefore, I control edit rights by giving the needed users Read access to
the DTS folders, but not Change rights.
RLF
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1184982108.759067.184370@.r34g2000hsd.googlegr oups.com...
>I am on sql 2000 sp 4, windows 2003.
> I am looking for a way to create custom database roles that are more
> exacting than db_datareader, db_dbowner etc. What I need to do is to
> create roles for the help desk personnel where they can view scheduled
> jobs, but not edit them or create them. I also need to grant access to
> edit DTS packages but not delete or create the. Does anyone have any
> experience or good articles about this?
> Thanks!
> Kristina
>
|||On Jul 23, 10:44 am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> Kristina,
> Did you already get help through googlegroups? A couple of simple things on
> SQL Server 2000.
> Make those who should monitor jobs and schedules but not change them members
> of the msdb TargetServersRole role. (Member of this role _cannot_ create new
> jobs, so do not use this for someone who must also create jobs.)
> Regarding DTS packages, I keep them stored on the file system, not in msdb.
> Therefore, I control edit rights by giving the needed users Read access to
> the DTS folders, but not Change rights.
> RLF"Kristina" <Kristina...@.gmail.com> wrote in message
> news:1184982108.759067.184370@.r34g2000hsd.googlegr oups.com...
>
>
>
> - Show quoted text -
Russell,
Thanks as always. This is the perfect advice I need! I have a crazy
new contract and I am the developer/DBA etc and don't have time to
research etc.
Kristina

permissions for custom roles

I am on sql 2000 sp 4, windows 2003.
I am looking for a way to create custom database roles that are more
exacting than db_datareader, db_dbowner etc. What I need to do is to
create roles for the help desk personnel where they can view scheduled
jobs, but not edit them or create them. I also need to grant access to
edit DTS packages but not delete or create the. Does anyone have any
experience or good articles about this?
Thanks!
KristinaKristina,
Did you already get help through googlegroups? A couple of simple things on
SQL Server 2000.
Make those who should monitor jobs and schedules but not change them members
of the msdb TargetServersRole role. (Member of this role _cannot_ create new
jobs, so do not use this for someone who must also create jobs.)
Regarding DTS packages, I keep them stored on the file system, not in msdb.
Therefore, I control edit rights by giving the needed users Read access to
the DTS folders, but not Change rights.
RLF
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1184982108.759067.184370@.r34g2000hsd.googlegroups.com...
>I am on sql 2000 sp 4, windows 2003.
> I am looking for a way to create custom database roles that are more
> exacting than db_datareader, db_dbowner etc. What I need to do is to
> create roles for the help desk personnel where they can view scheduled
> jobs, but not edit them or create them. I also need to grant access to
> edit DTS packages but not delete or create the. Does anyone have any
> experience or good articles about this?
> Thanks!
> Kristina
>|||On Jul 23, 10:44 am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> Kristina,
> Did you already get help through googlegroups? A couple of simple things
on
> SQL Server 2000.
> Make those who should monitor jobs and schedules but not change them membe
rs
> of the msdb TargetServersRole role. (Member of this role _cannot_ create n
ew
> jobs, so do not use this for someone who must also create jobs.)
> Regarding DTS packages, I keep them stored on the file system, not in msdb
.
> Therefore, I control edit rights by giving the needed users Read access to
> the DTS folders, but not Change rights.
> RLF"Kristina" <Kristina...@.gmail.com> wrote in message
> news:1184982108.759067.184370@.r34g2000hsd.googlegroups.com...
>
>
>
>
> - Show quoted text -
Russell,
Thanks as always. This is the perfect advice I need! I have a crazy
new contract and I am the developer/DBA etc and don't have time to
research etc.
Kristina

Permissions for Connecting to Reporting Services in Management Studio

Hi,

We have a user in the System Administrator and System User roles of

2005 SSRS. He is able to connect to the website

(http://<servername>/Reports) and do whatever he needs to do.

But, when he tries to connect to Reporting Services in Management

Studio, he receives the following error:

===================================

Cannot connect to <servername>.

===================================

The machine could not

be found

(Microsoft.SqlServer.Management.UI.RSClient)

Program

Location:

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I added him to the Distributed COM Users group, in DCOM security I

added him to the Default Launch and Activation Permissions (Local

Launch, Remote Launch, Local Activation, Remote Activation), I gave him

all permissions to the root namespace (and sub-namespaces) in wmi.

After giving him these permissions, the error message changes slightly

(no longer does he get 'the machine name cannot be found'):

Program

Location:

at

System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32

errorCode, IntPtr errorInfo)

at

System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

If

I add him to the local administrators group, he can connect using

Management Studio. But, that is not what we want to do. And, in fact,

we want to revoke any of these extra permissions that we have assigned

in order to try to get this working. Does anyone know what permissions

a person needs (likely in the OS) for connecting to Reporting Services

in Management Studio?

Thanks,

Michelle

Try granting permissions on \root\Microsoft\SqlServer\ReportServer\v9 namespace to Everyone.

As a workaround you can type in the url to report server instead of machine name. This will cause management studio to not go through the WMI code path to resolve report server url.

|||Thanks! The workaround worked. The dba used

http://<servername>/reportserver for the server name in

Management Studio and is able to connect to Reporting Services.

Michelle

Permissions for Connecting to Reporting Services in Management Studio

Hi,

We have a user in the System Administrator and System User roles of

2005 SSRS. He is able to connect to the website

(http://<servername>/Reports) and do whatever he needs to do.

But, when he tries to connect to Reporting Services in Management

Studio, he receives the following error:

===================================

Cannot connect to <servername>.

===================================

The machine could not

be found

(Microsoft.SqlServer.Management.UI.RSClient)

Program

Location:

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I added him to the Distributed COM Users group, in DCOM security I

added him to the Default Launch and Activation Permissions (Local

Launch, Remote Launch, Local Activation, Remote Activation), I gave him

all permissions to the root namespace (and sub-namespaces) in wmi.

After giving him these permissions, the error message changes slightly

(no longer does he get 'the machine name cannot be found'):

Program

Location:

at

System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32

errorCode, IntPtr errorInfo)

at

System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

at

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

at

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

If

I add him to the local administrators group, he can connect using

Management Studio. But, that is not what we want to do. And, in fact,

we want to revoke any of these extra permissions that we have assigned

in order to try to get this working. Does anyone know what permissions

a person needs (likely in the OS) for connecting to Reporting Services

in Management Studio?

Thanks,

Michelle

Try granting permissions on \root\Microsoft\SqlServer\ReportServer\v9 namespace to Everyone.

As a workaround you can type in the url to report server instead of machine name. This will cause management studio to not go through the WMI code path to resolve report server url.

|||Thanks! The workaround worked. The dba used

http://<servername>/reportserver for the server name in

Management Studio and is able to connect to Reporting Services.

Michelle

Permissions for Connecting to Reporting Services in Management Stu

Hi,
We have a user in the System Administrator and System User roles of 2005
SSRS. He is able to connect to the website (http://<servername>/Reports) and
do whatever he needs to do. But, when he tries to connect to Reporting
Services in Management Studio, he receives the following error:
===================================
Cannot connect to <servername>.
===================================
The machine could not be found (Microsoft.SqlServer.Management.UI.RSClient)
--
Program Location:
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
I added him to the Distributed COM Users group, in DCOM security I added him
to the Default Launch and Activation Permissions (Local Launch, Remote
Launch, Local Activation, Remote Activation), I gave him all permissions to
the root namespace (and sub-namespaces) in wmi. After giving him these
permissions, the error message changes slightly (no longer does he get 'the
machine name cannot be found'):
Program Location:
at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
errorCode, IntPtr errorInfo)
at
System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
If I add him to the local administrators group, he can connect using
Management Studio. But, that is not what we want to do. And, in fact, we want
to revoke any of these extra permissions that we have assigned in order to
try to get this working. Does anyone know what permissions a person needs
(likely in the OS) for connecting to Reporting Services in Management Studio?
Thanks,
MichelleMichelle wrote:
> Hi,
> We have a user in the System Administrator and System User roles of 2005
> SSRS. He is able to connect to the website (http://<servername>/Reports) and
> do whatever he needs to do. But, when he tries to connect to Reporting
> Services in Management Studio, he receives the following error:
> ===================================> Cannot connect to <servername>.
>
> ===================================> The machine could not be found (Microsoft.SqlServer.Management.UI.RSClient)
> --
> Program Location:
>
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
> I added him to the Distributed COM Users group, in DCOM security I added him
> to the Default Launch and Activation Permissions (Local Launch, Remote
> Launch, Local Activation, Remote Activation), I gave him all permissions to
> the root namespace (and sub-namespaces) in wmi. After giving him these
> permissions, the error message changes slightly (no longer does he get 'the
> machine name cannot be found'):
> Program Location:
>
> at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
> errorCode, IntPtr errorInfo)
> at
> System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
> If I add him to the local administrators group, he can connect using
> Management Studio. But, that is not what we want to do. And, in fact, we want
> to revoke any of these extra permissions that we have assigned in order to
> try to get this working. Does anyone know what permissions a person needs
> (likely in the OS) for connecting to Reporting Services in Management Studio?
> Thanks,
> Michelle
Hi Michelle,
I'm having a very similiar problem to yours and I was wondering if you
ever found a solution to your problem. I haven't found much about it
on the internet yet, but I'm going to continue my search today. I'll
let you know if I find anything.
Thanks,
Rob

permission issues with database roles

Hello,
Does somebody knows or have a complete solution, for resolving the following
issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. I can use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins. The login created by the
sp_help_revlogin can access the database, but if i do a simple select
statement, i'm getting a error about permissions.
In the meantime, i've also tried the mapsid.exe, but still no luck. Maybe i
did something wrong i don't know.
I hope somebody has faced this problem before and can give me a solution.
ThnxHi
The correct procedure is to script the users out and then remove the users
from the DB before you detach the DB.
Regards
Mike
"Ezeki?l" wrote:

> Hello,
> Does somebody knows or have a complete solution, for resolving the followi
ng
> issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. I can use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins. The login created by the
> sp_help_revlogin can access the database, but if i do a simple select
> statement, i'm getting a error about permissions.
> In the meantime, i've also tried the mapsid.exe, but still no luck. Maybe
i
> did something wrong i don't know.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>|||Hi Mike,
Can you tell me how? Because what you're saying is the opposite what the
instructions says of sp_help_revlogin.
I used sp_help_revlogin on the source db to get me a script to create the
logins on the destination server. Next i detached the db an attached it on
the destination server. Now if i don't have database roles, it would work.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:8FF81982-6018-4D46-9EEE-2404D9015CF2@.microsoft.com...[vbcol=seagreen]
> Hi
> The correct procedure is to script the users out and then remove the users
> from the DB before you detach the DB.
> Regards
> Mike
> "Ezekil" wrote:
>
following[vbcol=seagreen]
Maybe i[vbcol=seagreen]
solution.[vbcol=seagreen]