sql login permissions: tab

Showing posts with label tab. Show all posts

Monday, March 26, 2012

Long Queries Hang VS; Other Productivity Killers

Whenever I run long SQL in VS whether it's in the Data tab or in Preview
mode, I can't seem to get a report out of it. I'm talking SQL in excess of
200 lines.
I can run the SQL fine in Query Analyzer but running it in the Data tab, it
freezes VS, and when I run the Preview, the report never renders. I have even
tried hardcoding all parameters, creating a stored procedure, and running the
report off a view. None have resolved the issue.
On a possibly related note, my connection to the database times out
intermittently, saying:
--
Microsoft Development Environment
--
An error occurred while executing the query.
General network error. Check your network documentation.
--
OK
--
This absolutely kills me over the course of a day. I have to shut down VS
and restart it.
Is my Reporting Services VS package install corrupt? What do I do?How many fields are being returned? How many rows of data?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> Whenever I run long SQL in VS whether it's in the Data tab or in Preview
> mode, I can't seem to get a report out of it. I'm talking SQL in excess of
> 200 lines.
> I can run the SQL fine in Query Analyzer but running it in the Data tab,
it
> freezes VS, and when I run the Preview, the report never renders. I have
even
> tried hardcoding all parameters, creating a stored procedure, and running
the
> report off a view. None have resolved the issue.
> On a possibly related note, my connection to the database times out
> intermittently, saying:
> --
> Microsoft Development Environment
> --
> An error occurred while executing the query.
> General network error. Check your network documentation.
> --
> OK
> --
> This absolutely kills me over the course of a day. I have to shut down VS
> and restart it.
> Is my Reporting Services VS package install corrupt? What do I do?|||For one report in particular, about 727 rows, and today it's taking just over
4 minutes to run. The other has over 60,000 rows and I'm just going to have
to work on limiting that severely.
"Bruce L-C [MVP]" wrote:
> How many fields are being returned? How many rows of data?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > Whenever I run long SQL in VS whether it's in the Data tab or in Preview
> > mode, I can't seem to get a report out of it. I'm talking SQL in excess of
> > 200 lines.
> >
> > I can run the SQL fine in Query Analyzer but running it in the Data tab,
> it
> > freezes VS, and when I run the Preview, the report never renders. I have
> even
> > tried hardcoding all parameters, creating a stored procedure, and running
> the
> > report off a view. None have resolved the issue.
> >
> > On a possibly related note, my connection to the database times out
> > intermittently, saying:
> >
> > --
> > Microsoft Development Environment
> > --
> > An error occurred while executing the query.
> >
> > General network error. Check your network documentation.
> > --
> > OK
> > --
> >
> > This absolutely kills me over the course of a day. I have to shut down VS
> > and restart it.
> >
> > Is my Reporting Services VS package install corrupt? What do I do?
>
>|||The 727 rows should not be a problem. Is that one causing an issue from the
data tab?
It doesn't surprise me that the 60,000 row one has problems.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> For one report in particular, about 727 rows, and today it's taking just
over
> 4 minutes to run. The other has over 60,000 rows and I'm just going to
have
> to work on limiting that severely.
> "Bruce L-C [MVP]" wrote:
> > How many fields are being returned? How many rows of data?
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > Whenever I run long SQL in VS whether it's in the Data tab or in
Preview
> > > mode, I can't seem to get a report out of it. I'm talking SQL in
excess of
> > > 200 lines.
> > >
> > > I can run the SQL fine in Query Analyzer but running it in the Data
tab,
> > it
> > > freezes VS, and when I run the Preview, the report never renders. I
have
> > even
> > > tried hardcoding all parameters, creating a stored procedure, and
running
> > the
> > > report off a view. None have resolved the issue.
> > >
> > > On a possibly related note, my connection to the database times out
> > > intermittently, saying:
> > >
> > > --
> > > Microsoft Development Environment
> > > --
> > > An error occurred while executing the query.
> > >
> > > General network error. Check your network documentation.
> > > --
> > > OK
> > > --
> > >
> > > This absolutely kills me over the course of a day. I have to shut down
VS
> > > and restart it.
> > >
> > > Is my Reporting Services VS package install corrupt? What do I do?
> >
> >
> >|||For the most part, Mr. 727 will run on the data tab, but clocks endlessly on
Preview. The report does consist of about 20 fields, but I would still expect
it to render.
Sometimes I just lose connectivity and have to close out VS. I'm not sure if
it's related, but it could be I suppose.
Any ideas?
"Bruce L-C [MVP]" wrote:
> The 727 rows should not be a problem. Is that one causing an issue from the
> data tab?
> It doesn't surprise me that the 60,000 row one has problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > For one report in particular, about 727 rows, and today it's taking just
> over
> > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> have
> > to work on limiting that severely.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > How many fields are being returned? How many rows of data?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> Preview
> > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> excess of
> > > > 200 lines.
> > > >
> > > > I can run the SQL fine in Query Analyzer but running it in the Data
> tab,
> > > it
> > > > freezes VS, and when I run the Preview, the report never renders. I
> have
> > > even
> > > > tried hardcoding all parameters, creating a stored procedure, and
> running
> > > the
> > > > report off a view. None have resolved the issue.
> > > >
> > > > On a possibly related note, my connection to the database times out
> > > > intermittently, saying:
> > > >
> > > > --
> > > > Microsoft Development Environment
> > > > --
> > > > An error occurred while executing the query.
> > > >
> > > > General network error. Check your network documentation.
> > > > --
> > > > OK
> > > > --
> > > >
> > > > This absolutely kills me over the course of a day. I have to shut down
> VS
> > > > and restart it.
> > > >
> > > > Is my Reporting Services VS package install corrupt? What do I do?
> > >
> > >
> > >
>
>|||Sometimes it runs from the data tab. Sometimes (like now), it hoses VS. The
query is 309 lines long and is pretty complex...Still, I'm having a heck of a
time. Could it be my RS client install, or could it be my machine?
"Bruce L-C [MVP]" wrote:
> The 727 rows should not be a problem. Is that one causing an issue from the
> data tab?
> It doesn't surprise me that the 60,000 row one has problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > For one report in particular, about 727 rows, and today it's taking just
> over
> > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> have
> > to work on limiting that severely.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > How many fields are being returned? How many rows of data?
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> Preview
> > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> excess of
> > > > 200 lines.
> > > >
> > > > I can run the SQL fine in Query Analyzer but running it in the Data
> tab,
> > > it
> > > > freezes VS, and when I run the Preview, the report never renders. I
> have
> > > even
> > > > tried hardcoding all parameters, creating a stored procedure, and
> running
> > > the
> > > > report off a view. None have resolved the issue.
> > > >
> > > > On a possibly related note, my connection to the database times out
> > > > intermittently, saying:
> > > >
> > > > --
> > > > Microsoft Development Environment
> > > > --
> > > > An error occurred while executing the query.
> > > >
> > > > General network error. Check your network documentation.
> > > > --
> > > > OK
> > > > --
> > > >
> > > > This absolutely kills me over the course of a day. I have to shut down
> VS
> > > > and restart it.
> > > >
> > > > Is my Reporting Services VS package install corrupt? What do I do?
> > >
> > >
> > >
>
>|||Did you say you tried it as a stored procedure and you still had problems.
20 fields should not be a problem. 309 lines of a query could be a problem
in the IDE (not necessarily when deployed). I suggest try deploying it and
see if it works to determine if it is an IDE only problem. Next, try having
it in a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
The
> query is 309 lines long and is pretty complex...Still, I'm having a heck
of a
> time. Could it be my RS client install, or could it be my machine?
> "Bruce L-C [MVP]" wrote:
> > The 727 rows should not be a problem. Is that one causing an issue from
the
> > data tab?
> >
> > It doesn't surprise me that the 60,000 row one has problems.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > For one report in particular, about 727 rows, and today it's taking
just
> > over
> > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > have
> > > to work on limiting that severely.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > How many fields are being returned? How many rows of data?
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > Preview
> > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > excess of
> > > > > 200 lines.
> > > > >
> > > > > I can run the SQL fine in Query Analyzer but running it in the
Data
> > tab,
> > > > it
> > > > > freezes VS, and when I run the Preview, the report never renders.
I
> > have
> > > > even
> > > > > tried hardcoding all parameters, creating a stored procedure, and
> > running
> > > > the
> > > > > report off a view. None have resolved the issue.
> > > > >
> > > > > On a possibly related note, my connection to the database times
out
> > > > > intermittently, saying:
> > > > >
> > > > > --
> > > > > Microsoft Development Environment
> > > > > --
> > > > > An error occurred while executing the query.
> > > > >
> > > > > General network error. Check your network documentation.
> > > > > --
> > > > > OK
> > > > > --
> > > > >
> > > > > This absolutely kills me over the course of a day. I have to shut
down
> > VS
> > > > > and restart it.
> > > > >
> > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > >
> > > >
> > > >
> >
> >
> >|||Yes, I did give it a shot as a stored procedure, with no luck.
Well, tomorrow's another day. I'll try deploying it and seeing what happens.
Thanks for the help.
Mike
"Bruce L-C [MVP]" wrote:
> Did you say you tried it as a stored procedure and you still had problems.
> 20 fields should not be a problem. 309 lines of a query could be a problem
> in the IDE (not necessarily when deployed). I suggest try deploying it and
> see if it works to determine if it is an IDE only problem. Next, try having
> it in a stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> The
> > query is 309 lines long and is pretty complex...Still, I'm having a heck
> of a
> > time. Could it be my RS client install, or could it be my machine?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > The 727 rows should not be a problem. Is that one causing an issue from
> the
> > > data tab?
> > >
> > > It doesn't surprise me that the 60,000 row one has problems.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > For one report in particular, about 727 rows, and today it's taking
> just
> > > over
> > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > have
> > > > to work on limiting that severely.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > How many fields are being returned? How many rows of data?
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > Preview
> > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > excess of
> > > > > > 200 lines.
> > > > > >
> > > > > > I can run the SQL fine in Query Analyzer but running it in the
> Data
> > > tab,
> > > > > it
> > > > > > freezes VS, and when I run the Preview, the report never renders.
> I
> > > have
> > > > > even
> > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > running
> > > > > the
> > > > > > report off a view. None have resolved the issue.
> > > > > >
> > > > > > On a possibly related note, my connection to the database times
> out
> > > > > > intermittently, saying:
> > > > > >
> > > > > > --
> > > > > > Microsoft Development Environment
> > > > > > --
> > > > > > An error occurred while executing the query.
> > > > > >
> > > > > > General network error. Check your network documentation.
> > > > > > --
> > > > > > OK
> > > > > > --
> > > > > >
> > > > > > This absolutely kills me over the course of a day. I have to shut
> down
> > > VS
> > > > > > and restart it.
> > > > > >
> > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Have you tried changing the connection string of the data source to increase
the time out?
try
"data source=ServerName;initial catalog=DatabaseName;Connection Timeout=30"
The connection timeout has a default of 15.
"Bassist695" wrote:
> Yes, I did give it a shot as a stored procedure, with no luck.
> Well, tomorrow's another day. I'll try deploying it and seeing what happens.
> Thanks for the help.
> Mike
> "Bruce L-C [MVP]" wrote:
> > Did you say you tried it as a stored procedure and you still had problems.
> > 20 fields should not be a problem. 309 lines of a query could be a problem
> > in the IDE (not necessarily when deployed). I suggest try deploying it and
> > see if it works to determine if it is an IDE only problem. Next, try having
> > it in a stored procedure.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> > The
> > > query is 309 lines long and is pretty complex...Still, I'm having a heck
> > of a
> > > time. Could it be my RS client install, or could it be my machine?
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > The 727 rows should not be a problem. Is that one causing an issue from
> > the
> > > > data tab?
> > > >
> > > > It doesn't surprise me that the 60,000 row one has problems.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > > For one report in particular, about 727 rows, and today it's taking
> > just
> > > > over
> > > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > > have
> > > > > to work on limiting that severely.
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > How many fields are being returned? How many rows of data?
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > > Preview
> > > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > > excess of
> > > > > > > 200 lines.
> > > > > > >
> > > > > > > I can run the SQL fine in Query Analyzer but running it in the
> > Data
> > > > tab,
> > > > > > it
> > > > > > > freezes VS, and when I run the Preview, the report never renders.
> > I
> > > > have
> > > > > > even
> > > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > > running
> > > > > > the
> > > > > > > report off a view. None have resolved the issue.
> > > > > > >
> > > > > > > On a possibly related note, my connection to the database times
> > out
> > > > > > > intermittently, saying:
> > > > > > >
> > > > > > > --
> > > > > > > Microsoft Development Environment
> > > > > > > --
> > > > > > > An error occurred while executing the query.
> > > > > > >
> > > > > > > General network error. Check your network documentation.
> > > > > > > --
> > > > > > > OK
> > > > > > > --
> > > > > > >
> > > > > > > This absolutely kills me over the course of a day. I have to shut
> > down
> > > > VS
> > > > > > > and restart it.
> > > > > > >
> > > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||No, I haven't tried that. I don't believe the queries are timing out.
Wouldn't I get an error message to indicate that?
At present, there is no timeout set, which I believe means that the query
will not time out.
I'm going to try the deploy-and-see method...Let's hope it's my IDE.
Mike
"Carl Finnegan" wrote:
> Have you tried changing the connection string of the data source to increase
> the time out?
> try
> "data source=ServerName;initial catalog=DatabaseName;Connection Timeout=30"
> The connection timeout has a default of 15.
> "Bassist695" wrote:
> > Yes, I did give it a shot as a stored procedure, with no luck.
> >
> > Well, tomorrow's another day. I'll try deploying it and seeing what happens.
> > Thanks for the help.
> >
> > Mike
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Did you say you tried it as a stored procedure and you still had problems.
> > > 20 fields should not be a problem. 309 lines of a query could be a problem
> > > in the IDE (not necessarily when deployed). I suggest try deploying it and
> > > see if it works to determine if it is an IDE only problem. Next, try having
> > > it in a stored procedure.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > news:0C35989D-938D-4EE6-B1F3-746130C76E80@.microsoft.com...
> > > > Sometimes it runs from the data tab. Sometimes (like now), it hoses VS.
> > > The
> > > > query is 309 lines long and is pretty complex...Still, I'm having a heck
> > > of a
> > > > time. Could it be my RS client install, or could it be my machine?
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > The 727 rows should not be a problem. Is that one causing an issue from
> > > the
> > > > > data tab?
> > > > >
> > > > > It doesn't surprise me that the 60,000 row one has problems.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > news:CED42435-B092-427F-A344-1A6F707C67B2@.microsoft.com...
> > > > > > For one report in particular, about 727 rows, and today it's taking
> > > just
> > > > > over
> > > > > > 4 minutes to run. The other has over 60,000 rows and I'm just going to
> > > > > have
> > > > > > to work on limiting that severely.
> > > > > >
> > > > > > "Bruce L-C [MVP]" wrote:
> > > > > >
> > > > > > > How many fields are being returned? How many rows of data?
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Bruce Loehle-Conger
> > > > > > > MVP SQL Server Reporting Services
> > > > > > >
> > > > > > > "Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
> > > > > > > news:188CC9BC-F0D3-46A7-A4F7-896E279E5252@.microsoft.com...
> > > > > > > > Whenever I run long SQL in VS whether it's in the Data tab or in
> > > > > Preview
> > > > > > > > mode, I can't seem to get a report out of it. I'm talking SQL in
> > > > > excess of
> > > > > > > > 200 lines.
> > > > > > > >
> > > > > > > > I can run the SQL fine in Query Analyzer but running it in the
> > > Data
> > > > > tab,
> > > > > > > it
> > > > > > > > freezes VS, and when I run the Preview, the report never renders.
> > > I
> > > > > have
> > > > > > > even
> > > > > > > > tried hardcoding all parameters, creating a stored procedure, and
> > > > > running
> > > > > > > the
> > > > > > > > report off a view. None have resolved the issue.
> > > > > > > >
> > > > > > > > On a possibly related note, my connection to the database times
> > > out
> > > > > > > > intermittently, saying:
> > > > > > > >
> > > > > > > > --
> > > > > > > > Microsoft Development Environment
> > > > > > > > --
> > > > > > > > An error occurred while executing the query.
> > > > > > > >
> > > > > > > > General network error. Check your network documentation.
> > > > > > > > --
> > > > > > > > OK
> > > > > > > > --
> > > > > > > >
> > > > > > > > This absolutely kills me over the course of a day. I have to shut
> > > down
> > > > > VS
> > > > > > > > and restart it.
> > > > > > > >
> > > > > > > > Is my Reporting Services VS package install corrupt? What do I do?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

Monday, February 20, 2012

login permissions

I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
--
Dan D.You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >I created a new user and wanted them to only be able to access one
> >database.
> > For that one database on the "database access" tab I checked only that one
> > database and checked the "public" and "db_datareader" database roles for
> > that
> > one database.
> >
> > When I log in through QA they can also see the "master", "msdb" and
> > "tempdb"
> > databases. And they run select queries on the "master" database. How can I
> > prevent this? I only want them to be able to "see" the "stone" database.
> >
> > Thanks,
> > --
> > Dan D.
>
>|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
>> You can't. They have access to these databases because they contain a guest
>> user. This cannot be removed from master or tempdb however it can be removed
>> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> be able to create/manage jobs and save DTS packages to the server.
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >I created a new user and wanted them to only be able to access one
>> >database.
>> > For that one database on the "database access" tab I checked only that one
>> > database and checked the "public" and "db_datareader" database roles for
>> > that
>> > one database.
>> >
>> > When I log in through QA they can also see the "master", "msdb" and
>> > "tempdb"
>> > databases. And they run select queries on the "master" database. How can I
>> > prevent this? I only want them to be able to "see" the "stone" database.
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages.
> Sysadmins will.
>
> > If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok?
> Yes. But again, no need to add sysadmins.
>
> > What happens if I remove guest from master?
> You can't do that.
>
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases.
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages. If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok? What happens if I remove guest from master?
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases. Is there a reason?
> >
> > Thanks,
> >
> > "Jasper Smith" wrote:
> >
> >> You can't. They have access to these databases because they contain a guest
> >> user. This cannot be removed from master or tempdb however it can be removed
> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
> >> be able to create/manage jobs and save DTS packages to the server.
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >> >I created a new user and wanted them to only be able to access one
> >> >database.
> >> > For that one database on the "database access" tab I checked only that one
> >> > database and checked the "public" and "db_datareader" database roles for
> >> > that
> >> > one database.
> >> >
> >> > When I log in through QA they can also see the "master", "msdb" and
> >> > "tempdb"
> >> > databases. And they run select queries on the "master" database. How can I
> >> > prevent this? I only want them to be able to "see" the "stone" database.
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
> >>
>
>|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages. If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok? What happens if I remove guest from master?
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a guest
>> >> user. This cannot be removed from master or tempdb however it can be removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only that one
>> >> > database and checked the "public" and "db_datareader" database roles for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How can I
>> >> > prevent this? I only want them to be able to "see" the "stone" database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts
of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages. If I add each
user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok? What happens if I remove guest from
master?
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a
guest
>> >> user. This cannot be removed from master or tempdb however it can be
removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins
will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only
that one
>> >> > database and checked the "public" and "db_datareader" database roles
for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How
can I
>> >> > prevent this? I only want them to be able to "see" the "stone"
database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>

Monday, March 26, 2012

Long Queries Hang VS; Other Productivity Killers

Monday, February 20, 2012

login permissions

login permissions

login permissions

sql login permissions

Blog Archive

About Me