i have 3 tables member_info, subscription_info, exclude
member_info has 3 columns (login, fname, lname)
subscription_info has 5 columns
(login, subid, monthlypayment, startdate, enddate)
exclude has only one column (login)
I want to write a query that would return:
login, fname, lname, subid, monthlypayment
where login is not on of the logins from exclude table..
I tried this query:
select login, fname, lname, subid, monthlypayment
from member_info, subscription_info, exclude
where member_info.login=subscription_info.login and
member_info.login <> exclude.login
but that yeilds duplicate records, please helptry:
select m.login, m.fname, m.lname, s.subid, s.monthlypayment
from member_info m
inner join subscription_info s ON m.login = s.login
left outer join exclude e ON e.login = m.login
where e.login IS NULL
>--Original Message--
>i have 3 tables member_info, subscription_info, exclude
>member_info has 3 columns (login, fname, lname)
>subscription_info has 5 columns
>(login, subid, monthlypayment, startdate, enddate)
>exclude has only one column (login)
>I want to write a query that would return:
>login, fname, lname, subid, monthlypayment
>where login is not on of the logins from exclude table..
>I tried this query:
>select login, fname, lname, subid, monthlypayment
>from member_info, subscription_info, exclude
>where member_info.login=subscription_info.login and
>member_info.login <> exclude.login
>but that yeilds duplicate records, please help
>.
>
Showing posts with label login. Show all posts
Showing posts with label login. Show all posts
Tuesday, March 20, 2012
3 Login failed every second
Hi.
In my system since 27th april every second appears in event\application
three failed connection with sa.
In my system since 27th april every second appears in event\application
three failed connection with sa.
How can is possible ?
On this server there are all my website.
Can you help me ?
Saimon(Florence)Saimon (saimon181072@.supereva.it) writes:
> In my system since 27th april every second appears in event\application
> three failed connection with sa.
> How can is possible ?
> On this server there are all my website.
Is the server exposed on the Internet? In such case be glad as long as
the connection attempts fails.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
Sunday, March 11, 2012
2nd Login failed for user '(null)' ...
This is driving me NUTS!
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.
OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.
OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>
2nd Login failed for user '(null)' ...
This is driving me NUTS!
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>
2nd Login failed for user '(null)' ...
This is driving me NUTS!
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>
Tuesday, March 6, 2012
2005: Disabled SQL Login
Is there something that would cause a SQL Login to get disabled
automatically? The login was used successfully yesterday but today we
were getting an error:
Login failed for user '<sql login>'. Reason: The account is disabled. [CLIENT: ipaddress]
Perhaps someone accidentally disabled this but that sounds unlikely.
I didn't see anything in the log about the account getting disabled.
Michelle
If you run
select * from sys.server_principals where name = '<sql login>'
You should be able to check to see if the login actually is disabled and what the last modified date is. This way you can check to see if it was actually changed.
Which log did you check? I believe that if you enabled trace for this, you should have received an event when the login was altered as well.
Sung
|||I had already enabled the login - the 'disabled' checkbox was checkedso I'm fairly certain that it really was disabled. The log file that
showed the error when the login was used to log in to the database was
the SQL Server error log.
I'm just trying to figure out how it got disabled and the only thing
that I can come up with is that someone did it accidentally.|||
The only thing I can think of that would cause something similar is when the account gets locked out.
But I believe the warning message is different and the control panel would show that the account is locked, not disabled.
I would guess the account was disabled by accident. You may want to create a trace for this action so you can monitor and audit this in the future?
Sung
|||Logins are not disabled automatically and the action of disabling a login does not issue a log entry. Someone must have explicitly disabled the login. Was this login created by some application or was it a regular user login?
Thanks
Laurentiu
Friday, February 24, 2012
2005 selected databas monitoring - how?
I have three databases with same user login. How can I monitor activity only
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.
Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>
|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>
|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>
|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.
Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>
|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>
|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>
|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>
2005 selected databas monitoring - how?
I have three databases with same user login. How can I monitor activity only
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>>I have three databases with same user login. How can I monitor activity
>>only for one database (logins, transactions) in SQL 2005 server?
>> I can't change the user for this database.
>|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> You can filter the database by db_id within sql server profiler
>>
>> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
>> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>>I have three databases with same user login. How can I monitor activity
>>only for one database (logins, transactions) in SQL 2005 server?
>> I can't change the user for this database.
>>
>|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
>> it was in 2000 profiler, but it's like here is some changes in 2005.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> You can filter the database by db_id within sql server profiler
>>
>> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
>> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>>I have three databases with same user login. How can I monitor activity
>>only for one database (logins, transactions) in SQL 2005 server?
>> I can't change the user for this database.
>>
>>
>
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>>I have three databases with same user login. How can I monitor activity
>>only for one database (logins, transactions) in SQL 2005 server?
>> I can't change the user for this database.
>|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> You can filter the database by db_id within sql server profiler
>>
>> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
>> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>>I have three databases with same user login. How can I monitor activity
>>only for one database (logins, transactions) in SQL 2005 server?
>> I can't change the user for this database.
>>
>|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
>> it was in 2000 profiler, but it's like here is some changes in 2005.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> You can filter the database by db_id within sql server profiler
>>
>> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
>> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>>I have three databases with same user login. How can I monitor activity
>>only for one database (logins, transactions) in SQL 2005 server?
>> I can't change the user for this database.
>>
>>
>
2005 selected databas monitoring - how?
I have three databases with same user login. How can I monitor activity only
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.
Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>
|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>
|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>
|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.
Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>
|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>
|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>
|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>
2005 selected databas monitoring - how?
I have three databases with same user login. How can I monitor activity only
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>
Thursday, February 16, 2012
2005 Fully qualified names.
In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
Simon
But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:
> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>
|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:
> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon
|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
|||I guess I understated that some
. I always tell people to qualify objects
no matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
Simon
But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:
> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>
|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:
> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon
|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
|||I guess I understated that some
. I always tell people to qualify objectsno matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>
2005 Fully qualified names.
In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:
> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views
?
Si
"Simon" wrote:
> In SQL 2005 I have a server with a username of JM. This username created a
nd
> maintains a database. When I login via query analyser I can`t run queries
on
> a table unless I qualify it JM.tablename. How do I alter the login to ensu
re
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>|||I guess I understated that some. I always tell people to qualify objects
no matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:
> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views
?
Si
"Simon" wrote:
> In SQL 2005 I have a server with a username of JM. This username created a
nd
> maintains a database. When I login via query analyser I can`t run queries
on
> a table unless I qualify it JM.tablename. How do I alter the login to ensu
re
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>|||I guess I understated that some
. I always tell people to qualify objectsno matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>
2005 Fully qualified names.
In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:
> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> > In SQL 2005 I have a server with a username of JM. This username created
> > and
> > maintains a database. When I login via query analyser I can`t run queries
> > on
> > a table unless I qualify it JM.tablename. How do I alter the login to
> > ensure
> > i can query just on tablename ?
> >
> > Simon
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:
> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
--
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>> In SQL 2005 I have a server with a username of JM. This username created
>> and
>> maintains a database. When I login via query analyser I can`t run queries
>> on
>> a table unless I qualify it JM.tablename. How do I alter the login to
>> ensure
>> i can query just on tablename ?
>> Simon
>|||I guess I understated that some:). I always tell people to qualify objects
no matter what as well.
--
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>> It is a good idea to always schema qualify all objects anyway.
>> --
>> Andrew J. Kelly SQL MVP
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>> In SQL 2005 I have a server with a username of JM. This username created
>> and
>> maintains a database. When I login via query analyser I can`t run
>> queries on
>> a table unless I qualify it JM.tablename. How do I alter the login to
>> ensure
>> i can query just on tablename ?
>> Simon
>>
>
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:
> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> > In SQL 2005 I have a server with a username of JM. This username created
> > and
> > maintains a database. When I login via query analyser I can`t run queries
> > on
> > a table unless I qualify it JM.tablename. How do I alter the login to
> > ensure
> > i can query just on tablename ?
> >
> > Simon
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:
> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
--
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>> In SQL 2005 I have a server with a username of JM. This username created
>> and
>> maintains a database. When I login via query analyser I can`t run queries
>> on
>> a table unless I qualify it JM.tablename. How do I alter the login to
>> ensure
>> i can query just on tablename ?
>> Simon
>|||I guess I understated that some:). I always tell people to qualify objects
no matter what as well.
--
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>> It is a good idea to always schema qualify all objects anyway.
>> --
>> Andrew J. Kelly SQL MVP
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>> In SQL 2005 I have a server with a username of JM. This username created
>> and
>> maintains a database. When I login via query analyser I can`t run
>> queries on
>> a table unless I qualify it JM.tablename. How do I alter the login to
>> ensure
>> i can query just on tablename ?
>> Simon
>>
>
Saturday, February 11, 2012
2005 CTP Connection error
When I try to connect to the 2005 CTP SQL server in any of the VBexpress software packages I get the gfollowing error
user instance login flag not supported in this version of SQL
I am not sure what to check or what I may have missed in the SQL server setupThe User Instance flag is only supported in the express SKU, are you connected to Express or another version of SQL Server? You can check by running select @.@.version
user instance login flag not supported in this version of SQL
I am not sure what to check or what I may have missed in the SQL server setupThe User Instance flag is only supported in the express SKU, are you connected to Express or another version of SQL Server? You can check by running select @.@.version
-Euan|||I am trying to connect to the sql 2005 version CTP [not the express version]|||All versions(SKUs) are part of CTP.
But if you are convinced that you are not connecting to express then this is the problem you need to remove the User Instance from the connection string.
-Euan|||Yes I am sure I am using the non-express version. However, to connect I am using the wizards that come with the Visual Studio Express products...I hate be sound ignorant but this is not my area of expertise...how do I remove that user instance from the wizard?
and from the the SQL server management studio It tell me SQL Server 9.0.1187|||Actually I saw in the advanced area where to change that. But if I do I get this message
"Unable to open Phycical file...The process cannot access the file because is it in use by another process..."|||SQL Server 9.0.1187|||Ah ok there is another setting that is used by express that you need to remove and thats the AttachDBFilename.
I suspect you you selected in the Wizard to connect to database file, try connecting the database instead.
-Euan|||I would lvoe to but the only connection options I have are
Microsoft Access Databse File
Microsoft SQL server Database File|||
I had the same problem and it was solved choosing the appropriate MDF file to the field "AttachDBFileNAme" and inserting the logical name of DB (the name that appears on SQL Server Man. Studio) in the field "InitialCatalog".
No other configurations were needed on SQL Server
2005 CTP Connection error
When I try to connect to the 2005 CTP SQL server in any of the VBexpress software packages I get the gfollowing error
user instance login flag not supported in this version of SQL
I am not sure what to check or what I may have missed in the SQL server setupThe User Instance flag is only supported in the express SKU, are you connected to Express or another version of SQL Server? You can check by running select @.@.version
user instance login flag not supported in this version of SQL
I am not sure what to check or what I may have missed in the SQL server setupThe User Instance flag is only supported in the express SKU, are you connected to Express or another version of SQL Server? You can check by running select @.@.version
-Euan|||I am trying to connect to the sql 2005 version CTP [not the express version]|||All versions(SKUs) are part of CTP.
But if you are convinced that you are not connecting to express then this is the problem you need to remove the User Instance from the connection string.
-Euan|||Yes I am sure I am using the non-express version. However, to connect I am using the wizards that come with the Visual Studio Express products...I hate be sound ignorant but this is not my area of expertise...how do I remove that user instance from the wizard?
and from the the SQL server management studio It tell me SQL Server 9.0.1187|||Actually I saw in the advanced area where to change that. But if I do I get this message
"Unable to open Phycical file...The process cannot access the file because is it in use by another process..."|||SQL Server 9.0.1187|||Ah ok there is another setting that is used by express that you need to remove and thats the AttachDBFilename.
I suspect you you selected in the Wizard to connect to database file, try connecting the database instead.
-Euan|||I would lvoe to but the only connection options I have are
Microsoft Access Databse File
Microsoft SQL server Database File|||
I had the same problem and it was solved choosing the appropriate MDF file to the field "AttachDBFileNAme" and inserting the logical name of DB (the name that appears on SQL Server Man. Studio) in the field "InitialCatalog".
No other configurations were needed on SQL Server
Subscribe to:
Posts (Atom)