cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
Bing
For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
WHERE clause (find the rows).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:

> For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/
|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:

> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>
|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:

> Check to ensure the user's login is correctly mapped to the desired database
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Online
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
>
|||bing skrev:

> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson
|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.

> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...[vbcol=seagreen]
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
|||"impslayer" wrote:

> bing skrev:
>
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bing

cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
BingFor some strange reason, you need SELECT as well. I guess it is because SQL
Server need to match the
WHERE clause (find the rows).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
I
> looked, he did not have DELETE on that table. Then I checked DELETE for h
is
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:

> For some strange reason, you need SELECT as well. I guess it is because SQ
L Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:

> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:

> Check to ensure the user's login is correctly mapped to the desired databa
se
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Onlin
e
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
>|||bing skrev:

> Hello,
> One of our users said he was not able to delete records in a table. When
I
> looked, he did not have DELETE on that table. Then I checked DELETE for h
is
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.

> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...[vbcol=seagreen]
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
>|||"impslayer" wrote:

> bing skrev:
>
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bingsql

cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
BingFor some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
WHERE clause (find the rows).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:
> For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When I
> > looked, he did not have DELETE on that table. Then I checked DELETE for his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
>|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:
> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:
> Check to ensure the user's login is correctly mapped to the desired database
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Online
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When
> > I
> > looked, he did not have DELETE on that table. Then I checked DELETE for
> > his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
>
>|||bing skrev:
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.
> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
>> Check to ensure the user's login is correctly mapped to the desired
>> database
>> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
>> mapping is incorrect following a database restore/attach. If that is you
>> problem, you can correct using sp_change_users_login. See the Books
>> Online
>> for usage details.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>> > Hello,
>> >
>> > One of our users said he was not able to delete records in a table.
>> > When
>> > I
>> > looked, he did not have DELETE on that table. Then I checked DELETE
>> > for
>> > his
>> > account. But he still cannot delete any records. I don't think there
>> > is
>> > anything more I need to do to make the permission change take effect.
>> > Correct me if I'm wrong. So what I'm missing here?
>> >
>> > Thanks in advance,
>> >
>> > Bing
>>|||"impslayer" wrote:
> bing skrev:
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When I
> > looked, he did not have DELETE on that table. Then I checked DELETE for his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bing|||bing skrev:
> This user did not tell me the
> exact error message he got if there was any. I need to check back with him
> on that.
Ah, here's the problem, I guess :)
/impslayer

Cannot delete users in SQL 2005?

Hi,

Running multiple SQL 2000 servers and on each can easily go under Enterprise Manager>>ServerXYZ>>Security>>Logins>>Delete and delete user regardless users DB membership etc...now installed SQL 2005 (MS SQL Server Management Studio) and trying to do the same (attached to server running SQL 2000) and I cannot do it. Each time I try it, I am getting:

TITLE: Microsoft SQL Server Management Studio

Drop failed for Login 'testdelete'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3033.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Login&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Login 'testdelete' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (Microsoft SQL Server, Error: 15175)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=15175&LinkId=20476

BUTTONS:

OK

If I go and manually remove user from all databases and then try to delete it only then I can delete such user...this is not the solution for me as we have some servers where users can be associated up to different 300+ databases.

I would like to move from 2000 and start using 2005 tools exclusively and this is only reason I cannot.

Any fix, suggestion or solution is appreciated. Thanks.

I believe the error is by default as you can see that user is mapped/aliased to other databases, if you are using linked servers then thsi is expected. As a matter of access can you try revoking the permissions for that user and then drop it.|||

Thanks for your reply.

Yes I know what the message is meaning but what I do not understand is how I can just go to any user setup with access to multiple databases on same server (not linked) and select "Delete" using SQL 2000 Enterprise Manager and it drops it w/o any issues but when I go to the same server and user with same setup under SQL 2005 Server Managment Studio it tells me that user is associated to other databases and it cannot delete it w/o me first revoking all the access just then to drop it (that works)...this is lot more complex and requires extra steps?

|||

The issue is not so much that the login has mapped users, but that the mapped users own something in a database. In SQL Server 2005, we made security enhancements that require every object to be owned by some security principal. A user can no longer simply relinquish ownership. Instead, ownership must be positively taken by some other principal. Once all the users mapped to the login are no longer owners of anything, you should be able to drop the login without error.

This sounds like a pain point for you. If you could file a suggestion to make this easier at http://connect.microsoft.com/sqlserver, that would help. The development team gives additional weight to defect reports and feature requests submitted by customers when we are prioritizing future work.

Hope this helps,
Steve

Cannot delete users in SQL 2005?

Hi,

Running multiple SQL 2000 servers and on each can easily go under Enterprise Manager>>ServerXYZ>>Security>>Logins>>Delete and delete user regardless users DB membership etc...now installed SQL 2005 (MS SQL Server Management Studio) and trying to do the same (attached to server running SQL 2000) and I cannot do it. Each time I try it, I am getting:

TITLE: Microsoft SQL Server Management Studio

Drop failed for Login 'testdelete'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3033.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Login&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Login 'testdelete' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (Microsoft SQL Server, Error: 15175)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=15175&LinkId=20476

BUTTONS:

OK

If I go and manually remove user from all databases and then try to delete it only then I can delete such user...this is not the solution for me as we have some servers where users can be associated up to different 300+ databases.

I would like to move from 2000 and start using 2005 tools exclusively and this is only reason I cannot.

Any fix, suggestion or solution is appreciated. Thanks.

I believe the error is by default as you can see that user is mapped/aliased to other databases, if you are using linked servers then thsi is expected. As a matter of access can you try revoking the permissions for that user and then drop it.|||

Thanks for your reply.

Yes I know what the message is meaning but what I do not understand is how I can just go to any user setup with access to multiple databases on same server (not linked) and select "Delete" using SQL 2000 Enterprise Manager and it drops it w/o any issues but when I go to the same server and user with same setup under SQL 2005 Server Managment Studio it tells me that user is associated to other databases and it cannot delete it w/o me first revoking all the access just then to drop it (that works)...this is lot more complex and requires extra steps?

|||

The issue is not so much that the login has mapped users, but that the mapped users own something in a database. In SQL Server 2005, we made security enhancements that require every object to be owned by some security principal. A user can no longer simply relinquish ownership. Instead, ownership must be positively taken by some other principal. Once all the users mapped to the login are no longer owners of anything, you should be able to drop the login without error.

This sounds like a pain point for you. If you could file a suggestion to make this easier at http://connect.microsoft.com/sqlserver, that would help. The development team gives additional weight to defect reports and feature requests submitted by customers when we are prioritizing future work.

Hope this helps,
Steve

Cannot create new roles for users or groups as well as folders

Good afternoon,
I cannot seem to create or modify both roles and folders in Report Manager.
When I click on OK in the form to create or modify existing items nothing
happens. Am I forgetting something?
I had an issue during the installation or RS that may weigh in... the
installation of RS would fail at the final phase and give me an error (25619
- KB Article 867872). There is a knowledgebase article for this. It details
using a parameter to not create the virtual folders for Reports and
ReportServer and how to create the virtual folders and it's permissions
afterwards. This completed the installation and everything else, aside from
this issue, appears to be operatin normally.
Specs:
Windows Server 2003
SQL Server 2000 w/ SP3
SQL RS w/ SP1Disregard... another thread gave me the answer:
on the server run:
aspnet_regiis -c
from the .Net framwork dir.
"Peter Capazzi" wrote:
> Good afternoon,
> I cannot seem to create or modify both roles and folders in Report Manager.
> When I click on OK in the form to create or modify existing items nothing
> happens. Am I forgetting something?
> I had an issue during the installation or RS that may weigh in... the
> installation of RS would fail at the final phase and give me an error (25619
> - KB Article 867872). There is a knowledgebase article for this. It details
> using a parameter to not create the virtual folders for Reports and
> ReportServer and how to create the virtual folders and it's permissions
> afterwards. This completed the installation and everything else, aside from
> this issue, appears to be operatin normally.
> Specs:
> Windows Server 2003
> SQL Server 2000 w/ SP3
> SQL RS w/ SP1
>

Cannot create a connection to data source !!!!!!!

I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?

I'm pulling my hair out at this point:

Screen Shots:

http://www.webfound.net/datasource_connection.jpg

http://www.webfound.net/datasource_connection2.jpg

http://www.webfound.net/datasource_connection3.jpg

Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run

An error has occurred during report processing.

Cannot create a connection to data source 'datasourcename'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

no it usues whatever account iis is running under.

IUSR_something I think.

if you want use integrated auth you need to use impersonation.

|||ok....then how do incorporate personalization in Reporting Services! I don't see anything on that...I think you're referring to .NET, I'm talking about Reporting Services 2005|||I posted this in the wrong forum by accident..I'll move it to Reporting Services..sorry

Cannot create a connection to data source !!!! SSRS 2005

I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?

I'm pulling my hair out at this point:

Screen Shots:

http://www.webfound.net/datasource_connection.jpg

http://www.webfound.net/datasource_connection2.jpg

http://www.webfound.net/datasource_connection3.jpg

Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run

An error has occurred during report processing.

Cannot create a connection to data source 'datasourcename'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

Are the client, RS, and SQL server all on different machines? If so, then you are probably hitting the double-hop problem. This is a limitation of integrated security in environments which don't support kerberos.

Tudor talks about it a bit in this blog entry:

http://blogs.msdn.com/tudortr/archive/2005/11/03/488731.aspx

You might want to check that out and see if the problem described there is what you are experiencing.

|||

No, I have gotten this to work fine for my test server which is the server running both RS and SQL Server 2005. RS and SQL Server 2005 are on the same box. The server I'm trying to connect to now is a different server's DB which is where the datasource is pointing to. So there is no double-hop issue here at least!

So in other words if I change the datasets to use the datasources that piont to ServerA below, my report runs for me ok. If I change the datasets in my report to talk to ServerB, I get the error immediately. The datasets are running stored procs and referencing the data sources for the DB connection in the dataset properties. I am using 3 datasets in my report and around 4 datasources are in my project that are uploaded automatically when I deploy my report to the report serve (TestServerA)

TestServerA - Has RS and SQL Server 2005. When using Datasources that point to this server's stored procs, it works for me at least the other users still have issues though where it's prompting them for a windows username and password

ServerB - a production server running SQL Server 2000 (doesn't have Reporting Services on it, we're just runnign stored procs against it for the datasets) in which the dataset (on TestServerA) is trying to run a stored proc off of ServerB. It stil uses ServerA for Reporting services part...whcih is where I'm getting the error from my own client when trying to run the report from the client in Report Manager

|||Ok, now I tried logging on directly to my TestServerA and ran the report from IE on that server (report Manager) and it works fine. Ok, so how am I getting a double hop issue if I have everything installed on the same server? I don't think hopping is the issue here still!|||scratch my last response...it was working only because my datasets were pointing to the datasources that were pointing to my TestServerA. Once I changed it back to the point where I got the error (for this post) whcih was to chagne teh datasets to point to the datasources that point to ServerB, I still get the error, even when running the report from TestServerA's Report Manager....|||

Here's a diagram to show you what's going on. Keep in mind, if the datasets aren't using datasources that point to ServerB and are using datasources that point to TestServerA, all works fine...it's just when my report is using datasources trying to talk to ServerB.

http://www.webfound.net/diagram.jpg

|||

Can you check the errors in the report server log files - they are under /Reporting Services/LogFiles/ReportServer*.log

If you see an error message coming from your data sources server that looks like - login failed for user (null), then it's likely that you are experiencing the Integrated security double-hop issue.

If that's the case, you can store credentials for the data sources in the report server, or enable Kerberos delegation as shown below:

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

Thanks,

Tudor

|||

In your second setup (ServerB) you are describing what I would expect to be the symptoms of the double hop problem. You have 3 machines, A->B->C. A is the client machine, B is the machine with reporting services, and C is the machine that has the data you are reporting against. You are trying to pass credentials from machine A all the way to machine C. Unless you have set up your environment to allow kerberos delegation (see the links that I pasted above), then this will fail. If you don't want to (or cannot) enable delegation, then a workaround for you is to store credentials for accessing the datasources in the catalog.

In your 1st scenario (TestServerA), I am not certain why they are getting prompted for credentials. I suspect it is because your test users don't have access to your machine, are they able to connect when providing their credentials?

|||my users do have access to the SQL Server databases so I don't know why they would get prompted still. No, when they type in their username and password, it doesn't take.|||

,I have missed the same problem once ,the problem is that ur datasource was set ot windows Authentification,(if u pay more attention to ur report'Connection string,u could find the bugs),but ur database has no right for this kind of user,

u could follow this steps to add a user or a group which the user belong to :

1.login in database

2.extend the security node

3.extend the logins node

3.right-click logins node,click new login, and then add the user or the group which the user belong to to Database'logins.

And there is a skill,if ur user is a member of administrator,and u assign permissions to the other user,by default,this kind of user only is a menber of user,dont have the role of administrator,so if u use the windows Authentification to build connection string for ur report,the user who just have a role of user don't have logins to database,u need to follow the steps above to add the user or the group to Database'logins.

|||Samuel, they definitely already have logins to the database, right down to the permissions of the stored procs being called by the datasets so this is covered! They have read rights as well as datawriter (which isn't really needed). This is why I posted, I have done almost everything.|||

I believe you are running into a couple of problems which are not necessarily related.

When you are prompted for credentials in IE with the standard credentials dialog, it is because IIS did not accept the user's token when IE presented it in response to a 401. This usually means that the user is not a valid user on the target machine. Check your IIS logs for 401s.

The datasource problems are because the user we are trying to access the database with is not allowed access. This can be for any number of reasons, the double-hop issue being a pretty common one when people are trying to pass through Windows credentials.

|||I have a similar problem to FlavorFlave. Our setup:

Machine A : client
Machine B: SSRS 2005
Machine C: SSAS 2000

Running reports from Machine B works, running from from A does not - getting the exact error he specified. Logged on to machine A with a limited user account, onto B/C with full admin rights.

Enabling delegation is not going to be possible on our network. John, i see you say a workaround is to "store credentials for accessing the datasources in the catalog". Not sure what you meant by this.. or where to do this?|||never mind.. ive figured it out :)

stored the credentials securely within the datasource on the report server itself. works now... though its slower to initialise than when the security is integrated.
|||talwar, can you explain the steps you took in detail so hopefully I can do the same...thanks

Cannot create a connection to data source !!!! SSRS 2005

I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?

I'm pulling my hair out at this point:

Screen Shots:

http://www.webfound.net/datasource_connection.jpg

http://www.webfound.net/datasource_connection2.jpg

http://www.webfound.net/datasource_connection3.jpg

Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run

An error has occurred during report processing.

Cannot create a connection to data source 'datasourcename'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

Are the client, RS, and SQL server all on different machines? If so, then you are probably hitting the double-hop problem. This is a limitation of integrated security in environments which don't support kerberos.

Tudor talks about it a bit in this blog entry:

http://blogs.msdn.com/tudortr/archive/2005/11/03/488731.aspx

You might want to check that out and see if the problem described there is what you are experiencing.

|||

No, I have gotten this to work fine for my test server which is the server running both RS and SQL Server 2005. RS and SQL Server 2005 are on the same box. The server I'm trying to connect to now is a different server's DB which is where the datasource is pointing to. So there is no double-hop issue here at least!

So in other words if I change the datasets to use the datasources that piont to ServerA below, my report runs for me ok. If I change the datasets in my report to talk to ServerB, I get the error immediately. The datasets are running stored procs and referencing the data sources for the DB connection in the dataset properties. I am using 3 datasets in my report and around 4 datasources are in my project that are uploaded automatically when I deploy my report to the report serve (TestServerA)

TestServerA - Has RS and SQL Server 2005. When using Datasources that point to this server's stored procs, it works for me at least the other users still have issues though where it's prompting them for a windows username and password

ServerB - a production server running SQL Server 2000 (doesn't have Reporting Services on it, we're just runnign stored procs against it for the datasets) in which the dataset (on TestServerA) is trying to run a stored proc off of ServerB. It stil uses ServerA for Reporting services part...whcih is where I'm getting the error from my own client when trying to run the report from the client in Report Manager

|||Ok, now I tried logging on directly to my TestServerA and ran the report from IE on that server (report Manager) and it works fine. Ok, so how am I getting a double hop issue if I have everything installed on the same server? I don't think hopping is the issue here still!|||scratch my last response...it was working only because my datasets were pointing to the datasources that were pointing to my TestServerA. Once I changed it back to the point where I got the error (for this post) whcih was to chagne teh datasets to point to the datasources that point to ServerB, I still get the error, even when running the report from TestServerA's Report Manager....|||

Here's a diagram to show you what's going on. Keep in mind, if the datasets aren't using datasources that point to ServerB and are using datasources that point to TestServerA, all works fine...it's just when my report is using datasources trying to talk to ServerB.

http://www.webfound.net/diagram.jpg

|||

Can you check the errors in the report server log files - they are under /Reporting Services/LogFiles/ReportServer*.log

If you see an error message coming from your data sources server that looks like - login failed for user (null), then it's likely that you are experiencing the Integrated security double-hop issue.

If that's the case, you can store credentials for the data sources in the report server, or enable Kerberos delegation as shown below:

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

Thanks,

Tudor

|||

In your second setup (ServerB) you are describing what I would expect to be the symptoms of the double hop problem. You have 3 machines, A->B->C. A is the client machine, B is the machine with reporting services, and C is the machine that has the data you are reporting against. You are trying to pass credentials from machine A all the way to machine C. Unless you have set up your environment to allow kerberos delegation (see the links that I pasted above), then this will fail. If you don't want to (or cannot) enable delegation, then a workaround for you is to store credentials for accessing the datasources in the catalog.

In your 1st scenario (TestServerA), I am not certain why they are getting prompted for credentials. I suspect it is because your test users don't have access to your machine, are they able to connect when providing their credentials?

|||my users do have access to the SQL Server databases so I don't know why they would get prompted still. No, when they type in their username and password, it doesn't take.|||

,I have missed the same problem once ,the problem is that ur datasource was set ot windows Authentification,(if u pay more attention to ur report'Connection string,u could find the bugs),but ur database has no right for this kind of user,

u could follow this steps to add a user or a group which the user belong to :

1.login in database

2.extend the security node

3.extend the logins node

3.right-click logins node,click new login, and then add the user or the group which the user belong to to Database'logins.

And there is a skill,if ur user is a member of administrator,and u assign permissions to the other user,by default,this kind of user only is a menber of user,dont have the role of administrator,so if u use the windows Authentification to build connection string for ur report,the user who just have a role of user don't have logins to database,u need to follow the steps above to add the user or the group to Database'logins.

|||Samuel, they definitely already have logins to the database, right down to the permissions of the stored procs being called by the datasets so this is covered! They have read rights as well as datawriter (which isn't really needed). This is why I posted, I have done almost everything.|||

I believe you are running into a couple of problems which are not necessarily related.

When you are prompted for credentials in IE with the standard credentials dialog, it is because IIS did not accept the user's token when IE presented it in response to a 401. This usually means that the user is not a valid user on the target machine. Check your IIS logs for 401s.

The datasource problems are because the user we are trying to access the database with is not allowed access. This can be for any number of reasons, the double-hop issue being a pretty common one when people are trying to pass through Windows credentials.

|||I have a similar problem to FlavorFlave. Our setup:

Machine A : client
Machine B: SSRS 2005
Machine C: SSAS 2000

Running reports from Machine B works, running from from A does not - getting the exact error he specified. Logged on to machine A with a limited user account, onto B/C with full admin rights.

Enabling delegation is not going to be possible on our network. John, i see you say a workaround is to "store credentials for accessing the datasources in the catalog". Not sure what you meant by this.. or where to do this?|||never mind.. ive figured it out :)

stored the credentials securely within the datasource on the report server itself. works now... though its slower to initialise than when the security is integrated.
|||talwar, can you explain the steps you took in detail so hopefully I can do the same...thanks

Cannot create a connection to data source !!!! SSRS 2005

I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?

I'm pulling my hair out at this point:

Screen Shots:

http://www.webfound.net/datasource_connection.jpg

http://www.webfound.net/datasource_connection2.jpg

http://www.webfound.net/datasource_connection3.jpg

Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run

An error has occurred during report processing.

Cannot create a connection to data source 'datasourcename'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

Are the client, RS, and SQL server all on different machines? If so, then you are probably hitting the double-hop problem. This is a limitation of integrated security in environments which don't support kerberos.

Tudor talks about it a bit in this blog entry:

http://blogs.msdn.com/tudortr/archive/2005/11/03/488731.aspx

You might want to check that out and see if the problem described there is what you are experiencing.

|||

No, I have gotten this to work fine for my test server which is the server running both RS and SQL Server 2005. RS and SQL Server 2005 are on the same box. The server I'm trying to connect to now is a different server's DB which is where the datasource is pointing to. So there is no double-hop issue here at least!

So in other words if I change the datasets to use the datasources that piont to ServerA below, my report runs for me ok. If I change the datasets in my report to talk to ServerB, I get the error immediately. The datasets are running stored procs and referencing the data sources for the DB connection in the dataset properties. I am using 3 datasets in my report and around 4 datasources are in my project that are uploaded automatically when I deploy my report to the report serve (TestServerA)

TestServerA - Has RS and SQL Server 2005. When using Datasources that point to this server's stored procs, it works for me at least the other users still have issues though where it's prompting them for a windows username and password

ServerB - a production server running SQL Server 2000 (doesn't have Reporting Services on it, we're just runnign stored procs against it for the datasets) in which the dataset (on TestServerA) is trying to run a stored proc off of ServerB. It stil uses ServerA for Reporting services part...whcih is where I'm getting the error from my own client when trying to run the report from the client in Report Manager

|||Ok, now I tried logging on directly to my TestServerA and ran the report from IE on that server (report Manager) and it works fine. Ok, so how am I getting a double hop issue if I have everything installed on the same server? I don't think hopping is the issue here still!|||scratch my last response...it was working only because my datasets were pointing to the datasources that were pointing to my TestServerA. Once I changed it back to the point where I got the error (for this post) whcih was to chagne teh datasets to point to the datasources that point to ServerB, I still get the error, even when running the report from TestServerA's Report Manager....|||

Here's a diagram to show you what's going on. Keep in mind, if the datasets aren't using datasources that point to ServerB and are using datasources that point to TestServerA, all works fine...it's just when my report is using datasources trying to talk to ServerB.

http://www.webfound.net/diagram.jpg

|||

Can you check the errors in the report server log files - they are under /Reporting Services/LogFiles/ReportServer*.log

If you see an error message coming from your data sources server that looks like - login failed for user (null), then it's likely that you are experiencing the Integrated security double-hop issue.

If that's the case, you can store credentials for the data sources in the report server, or enable Kerberos delegation as shown below:

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

Thanks,

Tudor

|||

In your second setup (ServerB) you are describing what I would expect to be the symptoms of the double hop problem. You have 3 machines, A->B->C. A is the client machine, B is the machine with reporting services, and C is the machine that has the data you are reporting against. You are trying to pass credentials from machine A all the way to machine C. Unless you have set up your environment to allow kerberos delegation (see the links that I pasted above), then this will fail. If you don't want to (or cannot) enable delegation, then a workaround for you is to store credentials for accessing the datasources in the catalog.

In your 1st scenario (TestServerA), I am not certain why they are getting prompted for credentials. I suspect it is because your test users don't have access to your machine, are they able to connect when providing their credentials?

|||my users do have access to the SQL Server databases so I don't know why they would get prompted still. No, when they type in their username and password, it doesn't take.|||

,I have missed the same problem once ,the problem is that ur datasource was set ot windows Authentification,(if u pay more attention to ur report'Connection string,u could find the bugs),but ur database has no right for this kind of user,

u could follow this steps to add a user or a group which the user belong to :

1.login in database

2.extend the security node

3.extend the logins node

3.right-click logins node,click new login, and then add the user or the group which the user belong to to Database'logins.

And there is a skill,if ur user is a member of administrator,and u assign permissions to the other user,by default,this kind of user only is a menber of user,dont have the role of administrator,so if u use the windows Authentification to build connection string for ur report,the user who just have a role of user don't have logins to database,u need to follow the steps above to add the user or the group to Database'logins.

|||Samuel, they definitely already have logins to the database, right down to the permissions of the stored procs being called by the datasets so this is covered! They have read rights as well as datawriter (which isn't really needed). This is why I posted, I have done almost everything.|||

I believe you are running into a couple of problems which are not necessarily related.

When you are prompted for credentials in IE with the standard credentials dialog, it is because IIS did not accept the user's token when IE presented it in response to a 401. This usually means that the user is not a valid user on the target machine. Check your IIS logs for 401s.

The datasource problems are because the user we are trying to access the database with is not allowed access. This can be for any number of reasons, the double-hop issue being a pretty common one when people are trying to pass through Windows credentials.

|||

I have a similar problem to FlavorFlave. Our setup:

Machine A : client
Machine B: SSRS 2005
Machine C: SSAS 2000

Running

reports from Machine B works, running from from A does not - getting the

exact error he specified. Logged on to machine A with a limited user

account, onto B/C with full admin rights.

Enabling delegation is

not going to be possible on our network. John, i see you say a

workaround is to "store credentials for accessing the datasources in

the catalog". Not sure what you meant by this.. or where to do this?|||never mind.. ive figured it out :)

stored the credentials securely within the datasource on the report server itself. works now... though its slower to initialise than when the security is integrated.|||talwar, can you explain the steps you took in detail so hopefully I can do the same...thanks

Cannot create a connection to data source !!!! SSRS 2005

I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?

I'm pulling my hair out at this point:

Screen Shots:

http://www.webfound.net/datasource_connection.jpg

http://www.webfound.net/datasource_connection2.jpg

http://www.webfound.net/datasource_connection3.jpg

Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run

An error has occurred during report processing.

Cannot create a connection to data source 'datasourcename'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

Are the client, RS, and SQL server all on different machines? If so, then you are probably hitting the double-hop problem. This is a limitation of integrated security in environments which don't support kerberos.

Tudor talks about it a bit in this blog entry:

http://blogs.msdn.com/tudortr/archive/2005/11/03/488731.aspx

You might want to check that out and see if the problem described there is what you are experiencing.

|||

No, I have gotten this to work fine for my test server which is the server running both RS and SQL Server 2005. RS and SQL Server 2005 are on the same box. The server I'm trying to connect to now is a different server's DB which is where the datasource is pointing to. So there is no double-hop issue here at least!

So in other words if I change the datasets to use the datasources that piont to ServerA below, my report runs for me ok. If I change the datasets in my report to talk to ServerB, I get the error immediately. The datasets are running stored procs and referencing the data sources for the DB connection in the dataset properties. I am using 3 datasets in my report and around 4 datasources are in my project that are uploaded automatically when I deploy my report to the report serve (TestServerA)

TestServerA - Has RS and SQL Server 2005. When using Datasources that point to this server's stored procs, it works for me at least the other users still have issues though where it's prompting them for a windows username and password

ServerB - a production server running SQL Server 2000 (doesn't have Reporting Services on it, we're just runnign stored procs against it for the datasets) in which the dataset (on TestServerA) is trying to run a stored proc off of ServerB. It stil uses ServerA for Reporting services part...whcih is where I'm getting the error from my own client when trying to run the report from the client in Report Manager

|||Ok, now I tried logging on directly to my TestServerA and ran the report from IE on that server (report Manager) and it works fine. Ok, so how am I getting a double hop issue if I have everything installed on the same server? I don't think hopping is the issue here still!|||scratch my last response...it was working only because my datasets were pointing to the datasources that were pointing to my TestServerA. Once I changed it back to the point where I got the error (for this post) whcih was to chagne teh datasets to point to the datasources that point to ServerB, I still get the error, even when running the report from TestServerA's Report Manager....|||

Here's a diagram to show you what's going on. Keep in mind, if the datasets aren't using datasources that point to ServerB and are using datasources that point to TestServerA, all works fine...it's just when my report is using datasources trying to talk to ServerB.

http://www.webfound.net/diagram.jpg

|||

Can you check the errors in the report server log files - they are under /Reporting Services/LogFiles/ReportServer*.log

If you see an error message coming from your data sources server that looks like - login failed for user (null), then it's likely that you are experiencing the Integrated security double-hop issue.

If that's the case, you can store credentials for the data sources in the report server, or enable Kerberos delegation as shown below:

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

Thanks,

Tudor

|||

In your second setup (ServerB) you are describing what I would expect to be the symptoms of the double hop problem. You have 3 machines, A->B->C. A is the client machine, B is the machine with reporting services, and C is the machine that has the data you are reporting against. You are trying to pass credentials from machine A all the way to machine C. Unless you have set up your environment to allow kerberos delegation (see the links that I pasted above), then this will fail. If you don't want to (or cannot) enable delegation, then a workaround for you is to store credentials for accessing the datasources in the catalog.

In your 1st scenario (TestServerA), I am not certain why they are getting prompted for credentials. I suspect it is because your test users don't have access to your machine, are they able to connect when providing their credentials?

|||my users do have access to the SQL Server databases so I don't know why they would get prompted still. No, when they type in their username and password, it doesn't take.|||

,I have missed the same problem once ,the problem is that ur datasource was set ot windows Authentification,(if u pay more attention to ur report'Connection string,u could find the bugs),but ur database has no right for this kind of user,

u could follow this steps to add a user or a group which the user belong to :

1.login in database

2.extend the security node

3.extend the logins node

3.right-click logins node,click new login, and then add the user or the group which the user belong to to Database'logins.

And there is a skill,if ur user is a member of administrator,and u assign permissions to the other user,by default,this kind of user only is a menber of user,dont have the role of administrator,so if u use the windows Authentification to build connection string for ur report,the user who just have a role of user don't have logins to database,u need to follow the steps above to add the user or the group to Database'logins.

|||Samuel, they definitely already have logins to the database, right down to the permissions of the stored procs being called by the datasets so this is covered! They have read rights as well as datawriter (which isn't really needed). This is why I posted, I have done almost everything.|||

I believe you are running into a couple of problems which are not necessarily related.

When you are prompted for credentials in IE with the standard credentials dialog, it is because IIS did not accept the user's token when IE presented it in response to a 401. This usually means that the user is not a valid user on the target machine. Check your IIS logs for 401s.

The datasource problems are because the user we are trying to access the database with is not allowed access. This can be for any number of reasons, the double-hop issue being a pretty common one when people are trying to pass through Windows credentials.

|||I have a similar problem to FlavorFlave. Our setup:

Machine A : client
Machine B: SSRS 2005
Machine C: SSAS 2000

Running reports from Machine B works, running from from A does not - getting the exact error he specified. Logged on to machine A with a limited user account, onto B/C with full admin rights.

Enabling delegation is not going to be possible on our network. John, i see you say a workaround is to "store credentials for accessing the datasources in the catalog". Not sure what you meant by this.. or where to do this?|||never mind.. ive figured it out :)

stored the credentials securely within the datasource on the report server itself. works now... though its slower to initialise than when the security is integrated.
|||talwar, can you explain the steps you took in detail so hopefully I can do the same...thanks

Cannot create a connection to data source - SSRS 2005

I cannot get this error resolve either for myself or any users, and I'm
actually part of the Admin group! Furthermore, a user with whom I'm
tring allow to view this report keeps getting prompted for their
windows username and password. I thought that since this datasource
was set to Windows Authentification that it would just pass it through?
I'm pulling my hair out at this point:
Screen Shots:
http://www.webfound.net/datasource_connection.jpg
http://www.webfound.net/datasource_connection2.jpg
http://www.webfound.net/datasource_connection3.jpg
Also As far as I know, I've given proper permissions to the right
logins and right users on my SQL Databases and related stored procs
that the datasets (not datasource) run
An error has occurred during report processing.
Cannot create a connection to data source 'datasourcename'.
For more information about this error navigate to the report server on
the local server machine, or enable remote errorsWith windows authentication issue, there is a parameter in Internet
Explorer which can cause this. Goto Tools|Internet options|Security
then I think the problem is likely to be the Intranet Zone, but
depending on your setup it could be one of the others too. Anyway,
select the Intranet Zone then custom, scroll to the bottom and you
should see User Authentication, you've probably got it set to Prompt on
this particular PC. This seems to be the default on Windows 2000
machines.
You may also find Active Directory policies can override this setting,
when they reboot.
On the data source issue:
A bit of a long shot this, but try removing the hyphens '-' from the
datasource names. It's never a good idea to put these sorts of
characters in SQL related stuff (this is the voice of experience!).
Also, can you run it from BIDS? If you can, then it would suggest the
server you are deploying to cannot see the server containing the actual
datasource.
Also, it may be worth checking the datasource "file" on the report
server. Remember, when you deploy a report it won't overwrite a
datasource if it already exists, and if it's been changed on the server
in any way the report will use that instead. Having said that, viewing
your screenshots it doesn't look like this is the case.
My hunch is your report server can't gain access to the server with the
data. Maybe describing the server setup a bit more may shed some more
light on the issue.
Hope some of this helps.
Cheers
Chris
dba123 wrote:
> I cannot get this error resolve either for myself or any users, and
> I'm actually part of the Admin group! Furthermore, a user with whom
> I'm tring allow to view this report keeps getting prompted for their
> windows username and password. I thought that since this datasource
> was set to Windows Authentification that it would just pass it
> through?
> I'm pulling my hair out at this point:
> Screen Shots:
> http://www.webfound.net/datasource_connection.jpg
> http://www.webfound.net/datasource_connection2.jpg
> http://www.webfound.net/datasource_connection3.jpg
>
> Also As far as I know, I've given proper permissions to the right
> logins and right users on my SQL Databases and related stored procs
> that the datasets (not datasource) run
> An error has occurred during report processing.
> Cannot create a connection to data source 'datasourcename'.
> For more information about this error navigate to the report server on
> the local server machine, or enable remote errors

Cannot connect to SQL until after service is restarted

A new database and application was installed on my SQL Server. Recently
users cannot connect to the database. I cannot even connect with Enterprise
Manager or Query Analyzer. Some users were getting an error message telling
them there was not enough memory.
I am getting the following message in the logs:
WARNING: Failed to commit contiguous memory of Size 65536.
I've configured the SQL Server system to allocated up a minimum of 512MB and
a maxium of 2GB of memory.
Are there any thoughts on how to resolve this problem? Thanks for your
attention.
Tech Details:
SQL Server 2000 Standard
OS: Windows Server 2003 (Small Business Server 2003)
2 Processors (P4 Xeon 3.06Ghz)
4GB memory
Application (Zoll Data Systems?)Can you post the output from sp_configure? Preferably also attach as a text document to preserve
formatting.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:15715A15-D833-4C89-B8E8-6B8D12B7C18F@.microsoft.com...
>A new database and application was installed on my SQL Server. Recently
> users cannot connect to the database. I cannot even connect with Enterprise
> Manager or Query Analyzer. Some users were getting an error message telling
> them there was not enough memory.
> I am getting the following message in the logs:
> WARNING: Failed to commit contiguous memory of Size 65536.
> I've configured the SQL Server system to allocated up a minimum of 512MB and
> a maxium of 2GB of memory.
> Are there any thoughts on how to resolve this problem? Thanks for your
> attention.
> Tech Details:
> SQL Server 2000 Standard
> OS: Windows Server 2003 (Small Business Server 2003)
> 2 Processors (P4 Xeon 3.06Ghz)
> 4GB memory
> Application (Zoll Data Systems?)