Well i cofigured one of my reports to be sent as email to user in the domain.
In the Report manager appeared that the mail was sent successfully but the
user didn't get it.
When I sent it to a outdoor email it didn't send it at all,why?
Where i can check the smtp setup 4 reporting services?If you look in the RSReportServer.config file you will see the SMTP settings
that Report Services use. If the subscription status says "Mail sent ..."
then RS has sent the mail to the SMTP server and you would need to look
there for possible errors.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"'" <@.discussions.microsoft.com> wrote in message
news:AE026676-803E-4FED-A7E7-BCF9D38292FB@.microsoft.com...
> Well i cofigured one of my reports to be sent as email to user in the
> domain.
> In the Report manager appeared that the mail was sent successfully but the
> user didn't get it.
> When I sent it to a outdoor email it didn't send it at all,why?
> Where i can check the smtp setup 4 reporting services?sql
Showing posts with label domain. Show all posts
Showing posts with label domain. Show all posts
Wednesday, March 21, 2012
Friday, March 9, 2012
Email Address in subscription
My companies email domain options for email address are diamondjo.com or
evangelinedowns.com.
When I set up a subscription is it possible to only put in the alias name
such as 'something' instead of somthing@.diamondjo.com? When I am in Outlook
creating an address these two domains are integrated into one address book.
I know each domain has there own exchange server.
If so, how.
Thanks.Hi Brian.
I think you're going to need to supply the fully qualified email address.
Reporting Services uses SMTP to send mail and it needs to the fully
qualified address to know where to relay the email to.
Is this a workaround to another problem?
-Tim
"Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
news:uHeqRQ$kGHA.4444@.TK2MSFTNGP02.phx.gbl...
> My companies email domain options for email address are diamondjo.com or
> evangelinedowns.com.
> When I set up a subscription is it possible to only put in the alias name
> such as 'something' instead of somthing@.diamondjo.com? When I am in
> Outlook creating an address these two domains are integrated into one
> address book. I know each domain has there own exchange server.
> If so, how.
> Thanks.
>|||This is not a workaround. I am new to RS and figured that being a MS
product it would closely integrate with Exchange. I didn't want to be fully
qualifiying email if I didn't have to.
Thanks for your response.
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:%23ldKm9GlGHA.4284@.TK2MSFTNGP05.phx.gbl...
> Hi Brian.
> I think you're going to need to supply the fully qualified email address.
> Reporting Services uses SMTP to send mail and it needs to the fully
> qualified address to know where to relay the email to.
> Is this a workaround to another problem?
> -Tim
> "Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
> news:uHeqRQ$kGHA.4444@.TK2MSFTNGP02.phx.gbl...
>> My companies email domain options for email address are diamondjo.com or
>> evangelinedowns.com.
>> When I set up a subscription is it possible to only put in the alias name
>> such as 'something' instead of somthing@.diamondjo.com? When I am in
>> Outlook creating an address these two domains are integrated into one
>> address book. I know each domain has there own exchange server.
>> If so, how.
>> Thanks.
>
evangelinedowns.com.
When I set up a subscription is it possible to only put in the alias name
such as 'something' instead of somthing@.diamondjo.com? When I am in Outlook
creating an address these two domains are integrated into one address book.
I know each domain has there own exchange server.
If so, how.
Thanks.Hi Brian.
I think you're going to need to supply the fully qualified email address.
Reporting Services uses SMTP to send mail and it needs to the fully
qualified address to know where to relay the email to.
Is this a workaround to another problem?
-Tim
"Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
news:uHeqRQ$kGHA.4444@.TK2MSFTNGP02.phx.gbl...
> My companies email domain options for email address are diamondjo.com or
> evangelinedowns.com.
> When I set up a subscription is it possible to only put in the alias name
> such as 'something' instead of somthing@.diamondjo.com? When I am in
> Outlook creating an address these two domains are integrated into one
> address book. I know each domain has there own exchange server.
> If so, how.
> Thanks.
>|||This is not a workaround. I am new to RS and figured that being a MS
product it would closely integrate with Exchange. I didn't want to be fully
qualifiying email if I didn't have to.
Thanks for your response.
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:%23ldKm9GlGHA.4284@.TK2MSFTNGP05.phx.gbl...
> Hi Brian.
> I think you're going to need to supply the fully qualified email address.
> Reporting Services uses SMTP to send mail and it needs to the fully
> qualified address to know where to relay the email to.
> Is this a workaround to another problem?
> -Tim
> "Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
> news:uHeqRQ$kGHA.4444@.TK2MSFTNGP02.phx.gbl...
>> My companies email domain options for email address are diamondjo.com or
>> evangelinedowns.com.
>> When I set up a subscription is it possible to only put in the alias name
>> such as 'something' instead of somthing@.diamondjo.com? When I am in
>> Outlook creating an address these two domains are integrated into one
>> address book. I know each domain has there own exchange server.
>> If so, how.
>> Thanks.
>
email address domain strip of query
Could this query be made more efficient?
It takes ages to run it. It strips of everything from an email address and keeps only the domain.
SUBSTRING(Mailaddress, CHARINDEX('@.', Mailaddress) + 1, CHARINDEX('.', SUBSTRING(Mailaddress, CHARINDEX('@.', Mailaddress) + 1, 100)) - 1) AS mail_domaingot 2005? string manipulation is faster in CLR.|||unfortunately no i don't. I may upgrade soon but for now it is 2000 im using.|||I assume that if you take this column out of the select list, the plan remains the same, but the query time goes up? Does the query time remain the same when you add the raw email address to the select list (and comment out all the substringing)? I am wondering, if you accidentally got a covering index on all of the fields not associated with the email addresses, or maybe the query is using different indexes for the query with all the substrings.
Also, not to be a nit picker, but which part of the domain are you after? An email address can be very complex. My brother has an email address that ends in @.haystack.mit.edu. Some of our British posters would have addresses like @.company.co.uk. I am guessing you would be interested in the "mit" or the "company", but not in "co' or "haystack".|||yes its from select list and no joins so it should be very fast. I se now that i have to make this to work with thoose split domain names aswell... damn :)
My question about the query was more kinda of " is this a stupid way to do this"?
i can live with the time it takes to run this query it is after al quite a large table.
thx for your help
It takes ages to run it. It strips of everything from an email address and keeps only the domain.
SUBSTRING(Mailaddress, CHARINDEX('@.', Mailaddress) + 1, CHARINDEX('.', SUBSTRING(Mailaddress, CHARINDEX('@.', Mailaddress) + 1, 100)) - 1) AS mail_domaingot 2005? string manipulation is faster in CLR.|||unfortunately no i don't. I may upgrade soon but for now it is 2000 im using.|||I assume that if you take this column out of the select list, the plan remains the same, but the query time goes up? Does the query time remain the same when you add the raw email address to the select list (and comment out all the substringing)? I am wondering, if you accidentally got a covering index on all of the fields not associated with the email addresses, or maybe the query is using different indexes for the query with all the substrings.
Also, not to be a nit picker, but which part of the domain are you after? An email address can be very complex. My brother has an email address that ends in @.haystack.mit.edu. Some of our British posters would have addresses like @.company.co.uk. I am guessing you would be interested in the "mit" or the "company", but not in "co' or "haystack".|||yes its from select list and no joins so it should be very fast. I se now that i have to make this to work with thoose split domain names aswell... damn :)
My question about the query was more kinda of " is this a stupid way to do this"?
i can live with the time it takes to run this query it is after al quite a large table.
thx for your help
Wednesday, March 7, 2012
EM Issue - Missing servers in available servers list?
My PC was renamed and added to Active Directory in a new domain yesterday. Today when I tried to register a server in EM I only see SQL Servers in this new domain and not in the other, trusted, domains. I visited the machines of 5 developers and found that some people are experiencing the same issue while others can see SQL Servers in other domains, however they can only see SQL 2000 and not SQL 7 servers.
Any idea how the "Available Server" list is populate in EM and what might be causing this issue? It is important to note that even though I do not see server in the list, I can still register the servers without a problem.
Thanks, DaveThe entries are stored in the client registry, as you might have assumed:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers|||...well, that's one of the places :)
I found another one:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers|||Dave
I assume you are talking about the list of available servers in the register server wizard in Enterprise Manager? If so, then there is actually a security alert about this (lastest SQL Server cummulative patch).
Your client broadcasts out a message to all of your (sub)network, and says I"I am looking for SQL Servers". Anything listening on port 1433 or so will respond with "Hey, I am a SQL Server". The vulnerability is in the way the client accepts the response. If someone "fakes" a response from SQL Server, they can do something to you (I forget the details, but you can look it up on the SQL site under downloads). But that is the short of how the register server wizard works. That is also why I never use it. If I don't know about the server, I probably shouldn't be messing with it. Hope this helps.
rdjabarov has pointed out where you get the lists of servers already registered in SQL Server Enterprise Manager and where Query Analizer and Profiler get that drop-down list of servers to connect to. Basically just the last 20 servers you visited.|||well, my first posting shows the currently used location, and my second posting shows the location on my other workstation. Both locations are sources for EM, not Profiler or QA. The latter 2 truely are using the last 20 servers you connected to.|||Thanks for the info about the security issue. I'll read about it.
The only reason why I like to see the list of servers is to ensure nobody is trying to add an unauthorized SQL Server to our network. There are probably other ways to verify this, but I'm just use to using EM.
Dave
Any idea how the "Available Server" list is populate in EM and what might be causing this issue? It is important to note that even though I do not see server in the list, I can still register the servers without a problem.
Thanks, DaveThe entries are stored in the client registry, as you might have assumed:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers|||...well, that's one of the places :)
I found another one:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers|||Dave
I assume you are talking about the list of available servers in the register server wizard in Enterprise Manager? If so, then there is actually a security alert about this (lastest SQL Server cummulative patch).
Your client broadcasts out a message to all of your (sub)network, and says I"I am looking for SQL Servers". Anything listening on port 1433 or so will respond with "Hey, I am a SQL Server". The vulnerability is in the way the client accepts the response. If someone "fakes" a response from SQL Server, they can do something to you (I forget the details, but you can look it up on the SQL site under downloads). But that is the short of how the register server wizard works. That is also why I never use it. If I don't know about the server, I probably shouldn't be messing with it. Hope this helps.
rdjabarov has pointed out where you get the lists of servers already registered in SQL Server Enterprise Manager and where Query Analizer and Profiler get that drop-down list of servers to connect to. Basically just the last 20 servers you visited.|||well, my first posting shows the currently used location, and my second posting shows the location on my other workstation. Both locations are sources for EM, not Profiler or QA. The latter 2 truely are using the last 20 servers you connected to.|||Thanks for the info about the security issue. I'll read about it.
The only reason why I like to see the list of servers is to ensure nobody is trying to add an unauthorized SQL Server to our network. There are probably other ways to verify this, but I'm just use to using EM.
Dave
EM does not display Login Name
All,
After adding Domain users to a database I get the situation where some
display in the "Users" section without a Login Name.
The users still function correctly, but some have Login Names and some
don't. Another note is that we have Production and Test servers on differen
t
boxes and a user can display different ways on each.
So, my question is, why does this happen? And more importantly, is there
some subtle problem behind the scenes that means I can't just ignore this
symptom?
Thanks,
Simon.
P.S. For example:
Name Login Name Database Access
---
User1 Domain\U1 Permit
User2 Permit
User3 Domain\U3 Permit
^- no login name for Domain\U2Simon wrote:
> Name Login Name Database Access
> ---
> User1 Domain\U1 Permit
> User2 Permit
> User3 Domain\U3 Permit
> ^- no login name for Domain\U2
Simon,
we are encountering the same effect, when I do a RESTORE from a Database Bac
kup
from a Server which is not in the Windows Domain. I suppose it has something
to
do with an orphaned User SID.
I suppose when you enter
use mydb
sp_helpuser
you get NULL in the Login column.
Suggestion: Use a script with 'sp_dropuser' resp. 'sp_revokedbaccess' and 'g
rantdbaccess' to delete and rebuild the
user(s).
If the DB owner user dbo is also void, use 'sp_changedbowner' to reassign it
.
Joachim|||Hi Joachim,
Yes, sp_helpuser does return null. However, we are using a script with the
mentioned sp's to add users to this database. Way the app works is by a use
r
entering details passed through to the script (sp). Some work, some don't.
We have two machines (Prod & Test) and the results can differ. UserA might
be fine on Prod but not on Test, UserB might befine on Test but not on Prod.
Repeatedly dropping and adding the user gets consistent results on each
machine, whether that be by script, Query Analyser, Enterprise Manager. I
can spot no common denominator, and have found no way to make a "nulling"
user not-null.
Only other thing that I can think worth mentioning: at the Security | Logins
level we have added "Domain\All Users" to grant all users server access.
Database access is granted on a user by user basis (via the script mentioned
earlier.)
Simon.
"Joachim Hofmann" wrote:
> Simon wrote:
>
> Simon,
> we are encountering the same effect, when I do a RESTORE from a Database B
ackup
> from a Server which is not in the Windows Domain. I suppose it has somethi
ng to
> do with an orphaned User SID.
> I suppose when you enter
> use mydb
> sp_helpuser
> you get NULL in the Login column.
> Suggestion: Use a script with 'sp_dropuser' resp. 'sp_revokedbaccess' and
'grantdbaccess' to delete and rebuild the
> user(s).
> If the DB owner user dbo is also void, use 'sp_changedbowner' to reassign
it.
>
> Joachim
>|||You can have null for the login name where there isn't a
matching record in the syslogins table in the master
database. The syslogins table and the sysusers table will be
joined on sid. If no matching record is found in syslogins
by sid, then the login name is null and nothing will display
for Login Name in Enterprise Manager.
-Sue
On Mon, 28 Feb 2005 14:53:03 -0800, "Simon"
<Simon@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Joachim,
>Yes, sp_helpuser does return null. However, we are using a script with the
>mentioned sp's to add users to this database. Way the app works is by a us
er
>entering details passed through to the script (sp). Some work, some don't.
>We have two machines (Prod & Test) and the results can differ. UserA might
>be fine on Prod but not on Test, UserB might befine on Test but not on Prod
.
>Repeatedly dropping and adding the user gets consistent results on each
>machine, whether that be by script, Query Analyser, Enterprise Manager. I
>can spot no common denominator, and have found no way to make a "nulling"
>user not-null.
>Only other thing that I can think worth mentioning: at the Security | Login
s
>level we have added "Domain\All Users" to grant all users server access.
>Database access is granted on a user by user basis (via the script mentione
d
>earlier.)
>Simon.
>
>"Joachim Hofmann" wrote:
>
After adding Domain users to a database I get the situation where some
display in the "Users" section without a Login Name.
The users still function correctly, but some have Login Names and some
don't. Another note is that we have Production and Test servers on differen
t
boxes and a user can display different ways on each.
So, my question is, why does this happen? And more importantly, is there
some subtle problem behind the scenes that means I can't just ignore this
symptom?
Thanks,
Simon.
P.S. For example:
Name Login Name Database Access
---
User1 Domain\U1 Permit
User2 Permit
User3 Domain\U3 Permit
^- no login name for Domain\U2Simon wrote:
> Name Login Name Database Access
> ---
> User1 Domain\U1 Permit
> User2 Permit
> User3 Domain\U3 Permit
> ^- no login name for Domain\U2
Simon,
we are encountering the same effect, when I do a RESTORE from a Database Bac
kup
from a Server which is not in the Windows Domain. I suppose it has something
to
do with an orphaned User SID.
I suppose when you enter
use mydb
sp_helpuser
you get NULL in the Login column.
Suggestion: Use a script with 'sp_dropuser' resp. 'sp_revokedbaccess' and 'g
rantdbaccess' to delete and rebuild the
user(s).
If the DB owner user dbo is also void, use 'sp_changedbowner' to reassign it
.
Joachim|||Hi Joachim,
Yes, sp_helpuser does return null. However, we are using a script with the
mentioned sp's to add users to this database. Way the app works is by a use
r
entering details passed through to the script (sp). Some work, some don't.
We have two machines (Prod & Test) and the results can differ. UserA might
be fine on Prod but not on Test, UserB might befine on Test but not on Prod.
Repeatedly dropping and adding the user gets consistent results on each
machine, whether that be by script, Query Analyser, Enterprise Manager. I
can spot no common denominator, and have found no way to make a "nulling"
user not-null.
Only other thing that I can think worth mentioning: at the Security | Logins
level we have added "Domain\All Users" to grant all users server access.
Database access is granted on a user by user basis (via the script mentioned
earlier.)
Simon.
"Joachim Hofmann" wrote:
> Simon wrote:
>
> Simon,
> we are encountering the same effect, when I do a RESTORE from a Database B
ackup
> from a Server which is not in the Windows Domain. I suppose it has somethi
ng to
> do with an orphaned User SID.
> I suppose when you enter
> use mydb
> sp_helpuser
> you get NULL in the Login column.
> Suggestion: Use a script with 'sp_dropuser' resp. 'sp_revokedbaccess' and
'grantdbaccess' to delete and rebuild the
> user(s).
> If the DB owner user dbo is also void, use 'sp_changedbowner' to reassign
it.
>
> Joachim
>|||You can have null for the login name where there isn't a
matching record in the syslogins table in the master
database. The syslogins table and the sysusers table will be
joined on sid. If no matching record is found in syslogins
by sid, then the login name is null and nothing will display
for Login Name in Enterprise Manager.
-Sue
On Mon, 28 Feb 2005 14:53:03 -0800, "Simon"
<Simon@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Joachim,
>Yes, sp_helpuser does return null. However, we are using a script with the
>mentioned sp's to add users to this database. Way the app works is by a us
er
>entering details passed through to the script (sp). Some work, some don't.
>We have two machines (Prod & Test) and the results can differ. UserA might
>be fine on Prod but not on Test, UserB might befine on Test but not on Prod
.
>Repeatedly dropping and adding the user gets consistent results on each
>machine, whether that be by script, Query Analyser, Enterprise Manager. I
>can spot no common denominator, and have found no way to make a "nulling"
>user not-null.
>Only other thing that I can think worth mentioning: at the Security | Login
s
>level we have added "Domain\All Users" to grant all users server access.
>Database access is granted on a user by user basis (via the script mentione
d
>earlier.)
>Simon.
>
>"Joachim Hofmann" wrote:
>
Friday, February 17, 2012
Effective Permissions Error with Domain User
If I create a new Login at the server level for a domain group :i.e.,
MyDomain\Domain Users
Then I go add a user tied into this new Login to my database i.e., Northwind
or pubs to add a user tied to this login.
Why is it then when I go to Database Properties -> Permissions and select
Effective Permissions on this new user I get the following error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the server principal because the principal "EGUIRES\Domain
Users" does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15406)
For help, click:
tSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
BUTTONS:
OK
The guest account seems fine for this but any manual entries I try that is
linked to my domain gets this error.
I would appreciate any light you can shed on this
Scott EguiresScott
Did you get these database from SQL Server 2000 by using a RESTORE command?
What is the compatibilty level of both databases?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
> If I create a new Login at the server level for a domain group :i.e.,
> MyDomain\Domain Users
> Then I go add a user tied into this new Login to my database i.e.,
> Northwind
> or pubs to add a user tied to this login.
>
> Why is it then when I go to Database Properties -> Permissions and select
> Effective Permissions on this new user I get the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Cannot execute as the server principal because the principal
> "EGUIRES\Domain
> Users" does not exist, this type of principal cannot be impersonated, or
> you
> do not have permission. (Microsoft SQL Server, Error: 15406)
> For help, click:
> EvtSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
> --
> BUTTONS:
> OK
>
> The guest account seems fine for this but any manual entries I try that is
> linked to my domain gets this error.
>
>
> I would appreciate any light you can shed on this
>
> Scott Eguires
>|||Yes I did. I set the database compatibility to 2005. WHat is interesting i
s
that Effective Permissions work for the Domain\Administrator group. I did a
server profile trace and found that it was calling the Execute As User. Thi
s
led me to the following clues:
When I run the following:
EXECUTE AS USER = 'MYDOMAIN\Domain Users';
I get the error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal
"MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
But if I run:
EXECUTE AS USER = 'MYDOMAIN\Administrator';
It works fine.
This leads me to believe it is some sort of permissions issue.
Any info would be greatly appreciated.
Thanks
"Uri Dimant" wrote:
> Scott
> Did you get these database from SQL Server 2000 by using a RESTORE command
?
> What is the compatibilty level of both databases?
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
>
>|||Scott
Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
the user a member of loca Administrator group on a computer when you run
SQL Server?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...[vbcol=seagreen]
> Yes I did. I set the database compatibility to 2005. WHat is interesting
> is
> that Effective Permissions work for the Domain\Administrator group. I did
> a
> server profile trace and found that it was calling the Execute As User.
> This
> led me to the following clues:
> When I run the following:
> EXECUTE AS USER = 'MYDOMAIN\Domain Users';
> I get the error:
> Msg 15517, Level 16, State 1, Line 1
> Cannot execute as the database principal because the principal
> "MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
> impersonated, or you do not have permission.
>
> But if I run:
> EXECUTE AS USER = 'MYDOMAIN\Administrator';
> It works fine.
> This leads me to believe it is some sort of permissions issue.
> Any info would be greatly appreciated.
> Thanks
>
> "Uri Dimant" wrote:
>|||No the 'MYDOMAIN\Domain Users' login is neither a member of the local
administrator
group nor a member of the sysadmin server role on the machine I am running
SQL Server.
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>|||Scott
I tried tp reproduce the problem on my worstation SQL Server 2005 (Dev
Edition)
1) Restore a database from SQL Server 2000 which had an user called 'John'
(I changed compatibility level to 90)
2) Create a new Login 'NewJohn' and granted him an access to the database
(non sysadmin)
3) Right Click on the database --Properties --Permissions ( I sow
'John' user along with'NewJohn' )
4) Click on Effective Permissions ( I got the same error)
5) Click on Remove button and removed 'John' user
6) Click on Effective Permissions again and now it working
I think it is because that user "John" was transfered as a database user
(orphaned) without mapping to any login on the new server
MS has provided two stored procedures to move the logins with their original
SID but it worked fime on SS2K and I have not tried yet on SS2005
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:4F36A7F9-3108-4142-875A-2DB8CA46ABB3@.microsoft.com...[vbcol=seagreen]
> No the 'MYDOMAIN\Domain Users' login is neither a member of the local
> administrator
> group nor a member of the sysadmin server role on the machine I am running
> SQL Server.
>
> "Uri Dimant" wrote:
>|||It seems that you cannot execute as user on a Group. Which makes sense sinc
e
the text is
Execute as User. So this tells me I will never be able to get the effective
permissions for a group
through the SQL Workbench tool which is fine I suppose. I had an additional
problem in that I had
my service account was set to local and needed to set it to a domain user
with appropriate rights. Without that my Execute as user fails since it
cannot resolve the user in my domain.
Scott Eguires
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>
MyDomain\Domain Users
Then I go add a user tied into this new Login to my database i.e., Northwind
or pubs to add a user tied to this login.
Why is it then when I go to Database Properties -> Permissions and select
Effective Permissions on this new user I get the following error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the server principal because the principal "EGUIRES\Domain
Users" does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15406)
For help, click:
tSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
BUTTONS:
OK
The guest account seems fine for this but any manual entries I try that is
linked to my domain gets this error.
I would appreciate any light you can shed on this
Scott EguiresScott
Did you get these database from SQL Server 2000 by using a RESTORE command?
What is the compatibilty level of both databases?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
> If I create a new Login at the server level for a domain group :i.e.,
> MyDomain\Domain Users
> Then I go add a user tied into this new Login to my database i.e.,
> Northwind
> or pubs to add a user tied to this login.
>
> Why is it then when I go to Database Properties -> Permissions and select
> Effective Permissions on this new user I get the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Cannot execute as the server principal because the principal
> "EGUIRES\Domain
> Users" does not exist, this type of principal cannot be impersonated, or
> you
> do not have permission. (Microsoft SQL Server, Error: 15406)
> For help, click:
> EvtSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
> --
> BUTTONS:
> OK
>
> The guest account seems fine for this but any manual entries I try that is
> linked to my domain gets this error.
>
>
> I would appreciate any light you can shed on this
>
> Scott Eguires
>|||Yes I did. I set the database compatibility to 2005. WHat is interesting i
s
that Effective Permissions work for the Domain\Administrator group. I did a
server profile trace and found that it was calling the Execute As User. Thi
s
led me to the following clues:
When I run the following:
EXECUTE AS USER = 'MYDOMAIN\Domain Users';
I get the error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal
"MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
But if I run:
EXECUTE AS USER = 'MYDOMAIN\Administrator';
It works fine.
This leads me to believe it is some sort of permissions issue.
Any info would be greatly appreciated.
Thanks
"Uri Dimant" wrote:
> Scott
> Did you get these database from SQL Server 2000 by using a RESTORE command
?
> What is the compatibilty level of both databases?
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
>
>|||Scott
Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
the user a member of loca Administrator group on a computer when you run
SQL Server?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...[vbcol=seagreen]
> Yes I did. I set the database compatibility to 2005. WHat is interesting
> is
> that Effective Permissions work for the Domain\Administrator group. I did
> a
> server profile trace and found that it was calling the Execute As User.
> This
> led me to the following clues:
> When I run the following:
> EXECUTE AS USER = 'MYDOMAIN\Domain Users';
> I get the error:
> Msg 15517, Level 16, State 1, Line 1
> Cannot execute as the database principal because the principal
> "MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
> impersonated, or you do not have permission.
>
> But if I run:
> EXECUTE AS USER = 'MYDOMAIN\Administrator';
> It works fine.
> This leads me to believe it is some sort of permissions issue.
> Any info would be greatly appreciated.
> Thanks
>
> "Uri Dimant" wrote:
>|||No the 'MYDOMAIN\Domain Users' login is neither a member of the local
administrator
group nor a member of the sysadmin server role on the machine I am running
SQL Server.
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>|||Scott
I tried tp reproduce the problem on my worstation SQL Server 2005 (Dev
Edition)
1) Restore a database from SQL Server 2000 which had an user called 'John'
(I changed compatibility level to 90)
2) Create a new Login 'NewJohn' and granted him an access to the database
(non sysadmin)
3) Right Click on the database --Properties --Permissions ( I sow
'John' user along with'NewJohn' )
4) Click on Effective Permissions ( I got the same error)
5) Click on Remove button and removed 'John' user
6) Click on Effective Permissions again and now it working
I think it is because that user "John" was transfered as a database user
(orphaned) without mapping to any login on the new server
MS has provided two stored procedures to move the logins with their original
SID but it worked fime on SS2K and I have not tried yet on SS2005
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:4F36A7F9-3108-4142-875A-2DB8CA46ABB3@.microsoft.com...[vbcol=seagreen]
> No the 'MYDOMAIN\Domain Users' login is neither a member of the local
> administrator
> group nor a member of the sysadmin server role on the machine I am running
> SQL Server.
>
> "Uri Dimant" wrote:
>|||It seems that you cannot execute as user on a Group. Which makes sense sinc
e
the text is
Execute as User. So this tells me I will never be able to get the effective
permissions for a group
through the SQL Workbench tool which is fine I suppose. I had an additional
problem in that I had
my service account was set to local and needed to set it to a domain user
with appropriate rights. Without that my Execute as user fails since it
cannot resolve the user in my domain.
Scott Eguires
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)