Personalize Reports for Each User

Hello Everyone,

I am trying to personalze my reports for each user, for example: Say I have a report which takes in userName as a parameter and then generates the report. Is it possible to use the same userName that the user used to login to the reporting services enviornment. This way the user don't have to enter their userName to see the report, it will be entered by default.

Thanks a lot,

-Rohit

Try User!UserID.

|||

Thanks for the reply!!!

I am still not sure where to use this field.

My question again is:

When a user login to Reporting Services, he/she has to enter a domain UserName and domain Password. I want to know if it is possible to grab that userName and use it as a default value for my parameter.

Thanks again,

-Rohit

|||Thanks a lot man...this works!!!|||

Glad I could help.

Permssion denied for dbo

Hi,
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied
.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
ThanksHi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:

> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> denied.
>
>|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging
on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are say
ing that both are
sysadmin? Right? (You cannot have two logins being the same user in a databa
se).
How do you determine that both are dbo? What tools/commands do you use to de
termine this?
Or are you saying that both are in the db_owner role in the database? That i
s a different thing from
being the dbo of a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission deni
ed.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:

> "Firestarter" schrieb:
> You can update the tables as dbo! You just have to add the ownername befor
e
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:

> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are s
aying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a data
base).
> How do you determine that both are dbo? What tools/commands do you use to
determine this?
> Or are you saying that both are in the db_owner role in the database? That
is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>|||When a db_owner (who isn't dbo) creates an object, it will not be owned by d
bo. It will be owned by
that persons user name in the database. You cannot change that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...[vbcol=seagreen]
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
>|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
--
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:

> Firestarter wrote:
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Permssion denied for dbo

Hi,
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
Thanks
Hi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks
|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:

> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> denied.
>
>
|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...
|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are saying that both are
sysadmin? Right? (You cannot have two logins being the same user in a database).
How do you determine that both are dbo? What tools/commands do you use to determine this?
Or are you saying that both are in the db_owner role in the database? That is a different thing from
being the dbo of a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks
|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:

> "Firestarter" schrieb:
> You can update the tables as dbo! You just have to add the ownername before
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...
|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:

> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are saying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a database).
> How do you determine that both are dbo? What tools/commands do you use to determine this?
> Or are you saying that both are in the db_owner role in the database? That is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>
|||When a db_owner (who isn't dbo) creates an object, it will not be owned by dbo. It will be owned by
that persons user name in the database. You cannot change that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...[vbcol=seagreen]
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:

> Firestarter wrote:
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
sql

Permssion denied for dbo

Hi,
My NT and my Ad user account are both shown as dbo on a database.
However, when I create tables using either account they are shown as not
being owned by dbo.
Then, when I try to insert or update these tables, it says permission denied.
How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
shoyuld be able to modify its data. Whats going on here?
ThanksHi
Because these accounts are not member of sysadmin server role
Try do
CREATE TABLE dbo.Mytabale
(
blalala
)
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission
denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||Yes, I'm sure that would help create the tables as owned by dbo, but that
that is not my issue.
I still can't update or insert into these tables even though I am logging on
as a dbo.
Any ideas why not?
"Uri Dimant" wrote:
> Hi
> Because these accounts are not member of sysadmin server role
> Try do
> CREATE TABLE dbo.Mytabale
> (
> blalala
> )
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> > Hi,
> >
> > My NT and my Ad user account are both shown as dbo on a database.
> > However, when I create tables using either account they are shown as not
> > being owned by dbo.
> > Then, when I try to insert or update these tables, it says permission
> denied.
> >
> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> > shoyuld be able to modify its data. Whats going on here?
> >
> > Thanks
>
>|||"Firestarter" schrieb:
> Yes, I'm sure that would help create the tables as owned by dbo, but that
> that is not my issue.
> I still can't update or insert into these tables even though I am logging on
> as a dbo.
> Any ideas why not?
You can update the tables as dbo! You just have to add the ownername before
the objectname (as the dbo is not the owner of the object).
Objects that belong to the dbo can always be accessed by everybody without
the owner's name, because 'dbo' is the default owner ...|||> My NT and my Ad user account are both shown as dbo on a database.
Are these Windows or SQL Server logins?
Since you say that two different logins are "dbo" in a database, you are saying that both are
sysadmin? Right? (You cannot have two logins being the same user in a database).
How do you determine that both are dbo? What tools/commands do you use to determine this?
Or are you saying that both are in the db_owner role in the database? That is a different thing from
being the dbo of a database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> Hi,
> My NT and my Ad user account are both shown as dbo on a database.
> However, when I create tables using either account they are shown as not
> being owned by dbo.
> Then, when I try to insert or update these tables, it says permission denied.
> How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> shoyuld be able to modify its data. Whats going on here?
> Thanks|||No, I can't! Thats my point. Wether I put the owner name or not, I canot
update the table.
That is the issue I am trying to resolve.
"Christian Donner" wrote:
> "Firestarter" schrieb:
> > Yes, I'm sure that would help create the tables as owned by dbo, but that
> > that is not my issue.
> > I still can't update or insert into these tables even though I am logging on
> > as a dbo.
> > Any ideas why not?
> You can update the tables as dbo! You just have to add the ownername before
> the objectname (as the dbo is not the owner of the object).
> Objects that belong to the dbo can always be accessed by everybody without
> the owner's name, because 'dbo' is the default owner ...|||These are windows logins.
And I mean that both are in the db_owner role. Apologies for the lack of
prescsion in my post.
I am using EM to determine this,
"Tibor Karaszi" wrote:
> > My NT and my Ad user account are both shown as dbo on a database.
> Are these Windows or SQL Server logins?
> Since you say that two different logins are "dbo" in a database, you are saying that both are
> sysadmin? Right? (You cannot have two logins being the same user in a database).
> How do you determine that both are dbo? What tools/commands do you use to determine this?
> Or are you saying that both are in the db_owner role in the database? That is a different thing from
> being the dbo of a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
> > Hi,
> >
> > My NT and my Ad user account are both shown as dbo on a database.
> > However, when I create tables using either account they are shown as not
> > being owned by dbo.
> > Then, when I try to insert or update these tables, it says permission denied.
> >
> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
> > shoyuld be able to modify its data. Whats going on here?
> >
> > Thanks
>|||When a db_owner (who isn't dbo) creates an object, it will not be owned by dbo. It will be owned by
that persons user name in the database. You cannot change that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
news:D4179915-0813-403B-B075-A20DAD8C312D@.microsoft.com...
> These are windows logins.
> And I mean that both are in the db_owner role. Apologies for the lack of
> prescsion in my post.
> I am using EM to determine this,
> "Tibor Karaszi" wrote:
>> > My NT and my Ad user account are both shown as dbo on a database.
>> Are these Windows or SQL Server logins?
>> Since you say that two different logins are "dbo" in a database, you are saying that both are
>> sysadmin? Right? (You cannot have two logins being the same user in a database).
>> How do you determine that both are dbo? What tools/commands do you use to determine this?
>> Or are you saying that both are in the db_owner role in the database? That is a different thing
>> from
>> being the dbo of a database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Firestarter" <Firestarter@.discussions.microsoft.com> wrote in message
>> news:5C0E66A8-2327-456A-8605-A9B13D82C158@.microsoft.com...
>> > Hi,
>> >
>> > My NT and my Ad user account are both shown as dbo on a database.
>> > However, when I create tables using either account they are shown as not
>> > being owned by dbo.
>> > Then, when I try to insert or update these tables, it says permission denied.
>> >
>> > How can this be? I'm a dbo? Even if I wern't a dbo, as I own the table I
>> > shoyuld be able to modify its data. Whats going on here?
>> >
>> > Thanks
>>|||Firestarter wrote:
> No, I can't! Thats my point. Wether I put the owner name or not, I
> canot update the table.
> That is the issue I am trying to resolve.
>
Are you specifying the dbo owner name in the create statement as Uri
suggested? If not, try it that way.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In an attempt to avoid the confusion I am creating, this is what I've got:
--AD server, table created using an AD account which is a member of db_owner
fixed db role
create table felix1 (test varchar(10) null)
-- Shows in EM as being owned by londonfire\hedleyf, as expected
create table dbo.felix1 (test varchar(10) null)
-- Shows in EM as being owned by dbo, as expected
--
insert felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'LONDONFIRE\HEDLEYF'.
insert dbo.felix1 (test)
values (2)
--results
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'felix1', database 'CFS_HFSRA_V3_test',
owner 'dbo'.
So I am in the db_owner role, and seem unable to update a table.
What is going on?
Thanks for your continued patience...
"David Gugick" wrote:
> Firestarter wrote:
> > No, I can't! Thats my point. Wether I put the owner name or not, I
> > canot update the table.
> > That is the issue I am trying to resolve.
> >
> Are you specifying the dbo owner name in the create statement as Uri
> suggested? If not, try it that way.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Firestarter wrote:
> In an attempt to avoid the confusion I am creating, this is what I've
> got:
To avoid confusion, you should _always_ include the owner name is DDL,
DML, and SELECT statements. Try granting yourself rights to the table.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Tried that, didn't work. But I shouldn't have anyway, should I?
"David Gugick" wrote:
> Firestarter wrote:
> > In an attempt to avoid the confusion I am creating, this is what I've
> > got:
> To avoid confusion, you should _always_ include the owner name is DDL,
> DML, and SELECT statements. Try granting yourself rights to the table.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Perms on Tempdb?

Hey, All,
We need to create temp tables but when we do, we get an error that the user
doesn't have permission to the tempdb database. We are using:
CREATE TABLE #TEMP1
(COL1 INT)
...
If we do this in, say, the Northwind database, the error says something like
'Unable to create table in tempdb' and something about permission denied.
There are no permissions granted in Northwind or tempdb. We can fix the
error problem by granting "create table" on tempdb. Whenever SS restarts
all tempdb perms are lost.
Is there a reason why this happens, as well as a fix?
We're using SS2K and SP3.Temporary tables are just that. Temporary.
The user should have Public access to the database.
From Books Online:
tempdb is re-created every time SQL Server is started so the system starts
with a clean copy of the database. Because temporary tables and stored
procedures are dropped automatically on disconnect, and no connections are
active when the system is shut down, there is never anything in tempdb to
be saved from one session of SQL Server to another.
Temporary tables are automatically dropped when they go out of scope,
unless explicitly dropped using DROP TABLE:
A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of
the current session.
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them.
The association between a task and a table is maintained only for the life
of a single Transact-SQL statement. This means that a global temporary
table is dropped at the completion of the last Transact-SQL statement that
was actively referencing the table when the creating session ended.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We understood all of that.
It doesn't explain why we're getting a permissions error.
Anyone?
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:L5mdhtG5DHA.1988@.cpmsftngxa07.phx.gbl...

quote:

---

> Temporary tables are just that. Temporary.
> The user should have Public access to the database.
> From Books Online:
> tempdb is re-created every time SQL Server is started so the system starts
> with a clean copy of the database. Because temporary tables and stored
> procedures are dropped automatically on disconnect, and no connections are
> active when the system is shut down, there is never anything in tempdb to
> be saved from one session of SQL Server to another.
> Temporary tables are automatically dropped when they go out of scope,
> unless explicitly dropped using DROP TABLE:
> A local temporary table created in a stored procedure is dropped
> automatically when the stored procedure completes. The table can be
> referenced by any nested stored procedures executed by the stored

---

procedure

quote:

---

> that created the table. The table cannot be referenced by the process

---

which

quote:

---

> called the stored procedure that created the table.
>
> All other local temporary tables are dropped automatically at the end of
> the current session.
>
> Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
> The association between a task and a table is maintained only for the life
> of a single Transact-SQL statement. This means that a global temporary
> table is dropped at the completion of the last Transact-SQL statement that
> was actively referencing the table when the creating session ended.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||Rick,
Crazy questions:
1. Is there a startup stored procedure that (for example) removes 'unwanted'
rights from tempdb (and other databases)?
2. Has your copy of the 'model' database been altered?
Russell Fields
"Rick" <b@.bt.net> wrote in message
news:401667d8$0$49107$8f4e7992@.newsreade
r.goldengate.net...

quote:

---

> We understood all of that.
> It doesn't explain why we're getting a permissions error.
> Anyone?
>
> "Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
> news:L5mdhtG5DHA.1988@.cpmsftngxa07.phx.gbl...
starts[QUOTE]
are[QUOTE]
to[QUOTE]
> procedure
> which
them.[QUOTE]
life[QUOTE]
that[QUOTE]
rights.[QUOTE]
>

---

Permit view and deny table?

This has probably been asked before, but is there a way to allow a user to
access a view but deny access to the underlying table? If so, how is this
done (even as a kluge)?
Thanks!For a user to be given select permissions on a view but not a table
referenced by the view, the view and underlying table must have the same
owner. This forms an ownership chain. As long as that is unbroken then the
permission check will be on the view and not the underlying table. By not
granting explict permissions on your tables and the user will not be able to
select from them.
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
"Neil W." <neilw@.netlib.com> wrote in message
news:uflVC0shEHA.3320@.TK2MSFTNGP11.phx.gbl...
> This has probably been asked before, but is there a way to allow a user to
> access a view but deny access to the underlying table? If so, how is this
> done (even as a kluge)?
> Thanks!
>

Permit user to select columns for a report: why not use Dynamic SQL?

I certainly have read lots of negative comments about using Dynamic SQL. And I have read some of the excellent web pages on the subject, such as

http://www.sommarskog.se/dynamic_sql.html

I have learned much from those discussions.

But I still find myself faced with a problem for which Dynamic SQL seems like the only reasonable solution: letting a user select the columns to include in a report, and the sort order for the output data.

In the application of concern, there are maybe seven columns that one might want to have in a report. If the user can choose any combination of these seven columns, and the order in which they are displayed (as well as the SORT order), it would be necessary to write something like 7! (7-factorial = 5040) individual reports to provide all the possibilities that included all 7 columns, plus (7 choose 6, with concern for order) (or 7*6*5*4*3*2 = 5040) ways to choose 6 of the 7 columns (with a group-by taking care of the column not chosen), etc.

(Even if we chose to ignore the order, and let the "presentation layer" of a .NET UI take care of that, there are still (2^7 -1) = 127 different ways to choose the columns for the report, knowing that at least one column must be selected.)

And when another level of detail is added to the data, we must duplicate all the existing reports so that we can have them "with and without" the new column.

This would be hundreds or thousands of reports -- just dealing with 7 columns in the selection list, and possibly the desired order for those 7 columns.

In MS Access it seems one has a "query builder" that is essentially constructing Dynamic SQL statements based on the selections the user makes in the "query builder." In Access one can use the "SQL View" to see the SQL statement written by the "query builder."

Why does this user community seem to frown on Dynamic SQL as a way to make thousands of very similar reports (same tables JOINed together on the same columns) that merely need different columns in the SELECT and GROUP BY clauses? This would essentially be duplicating some of the operation of the "query builder" in MS Access. Is that a bad design?

What am I missing?

In the type of situation you describe, Dynamic SQL is a good option. However, I would never allow the end user to be able to type in column names, or even see the SQL code. That would open you to SQL injection.

Instead, build a form to create the SQL string. Use drop down lists and check boxes for the user to select columns and sort criteria. Build the SQL string 'behind the scene'. Do not allow the user to write anything that you put in the SQL String. If you follow such precautions, you 'may' be able to prevent the use of SQL injection by the end users, but not by anyone familiar with the code. (Upwards of 75% of economic damage comes from within...) Preventing even developers (and power users using Excel, etc.) from having the opportunity to use SQL injection would require using stored procedures.

|||

The key word is "report". For purely reporting, particularly reporting on a database that is not part of your OLTP system with live users trying to get work done, you are absolutely right. Giving users ad hoc access to write reports to your live system is just giving them a license to bring down your system. So be careful and make sure to constrain them some how (query governor, qa processes, etc.) My advice is always to provide a copy of the data (either transformed into a format for easy reporting (like OLAP), or just replicated/snapshotted) that the user can write reports to their hearts delight. If the query brings down the server, no revenue is likely lost.

But you are right, the only thing you gain by using stored procedures for reports is plan saving, and who cares? Reports may be run once a day, whereas OLTP queries are often run once a second (or more in some systems). We use stored procedures for reports only when the queries are so complex they won't fit in a single query (well, when the people writing them can't fathom how to make them into a single query :)

I definitely prefer the no dynamic SQL rule for reports that go off of the OLTP system though. The more fixed the set of queries, the more reasonable the support is, and your OLTP system is where money comes in, and that is where you get paid from. Never want to dissapoint there.

|||

Arnie,

Yes, as you mentioned, the user would select columns in some sort of drop-down list. There would be no room for SQL Injection. The User Interface in .NET would also allow the user to define the column order for the output report display, as well as the sort order for that display. The details have not yet been worked out.

Louis,

The reports I am discussing ARE for the "OLTP System." The main purpose of the OLTP system is to provide reports to users (people are producing reports more than any other sort of transaction). The current report menu tree is approx. 40 selections, each with its own stored procedure (it had been 23 reports, but we just recently added a dozen more, or so; soon we must add approx. 200 more if we don't use Dynamic SQL; each returns 2 or 3 data tables; each is maybe a 500-line stored procedure). But the users are asking for a wider variety of reports (to "slice and dice" the data different ways, to look at the same data from different perspectives, and with different levels of detail), which would cause the menu tree to expand to 10,000 entries or so, each with its own stored procedure unless we make a single stored procedure that uses Dynamic SQL.

So we could either write 5,000,000 lines of code and make a separate procedure for each report, and have the User Interface decide which of the 10,000 stored procedures to use (one per report), or we could write a few Dynamic SQL stored procedures that were each maybe 1000-1500 lines, and which could produce all 10,000 reports. (I hate to think of editing 10,000 stored procedures if we decide to rename a table, or a column in a table, or any similar maintenance activity!) (And there is also the issue of how to provide a .NET menu for the user to select from 10,000 reports, as opposed to providing merely a "select columns, display order, and sort order" for a list of 7 columns.)

These reports are run only hundreds to thousands of times per day, on a busy day; maybe dozens of times per day on a slow day. Because of the volume of the data a report may take a minute or two for the stored procedure to run and for the output to be formatted. Many users can only access a small amount of the data, so their reports run in a few seconds.

The users would not be writing any reports -- they would be merely choosing the columns they wanted in their output, which thereby defines the level of aggregation of any sums, etc., of numeric quantities. The "dynamic SQL builder" would be a stored procedure called by .NET, with the column selections and sort order as part of the input parameters.

The main advantage I see for doing this in Dynamic SQL is that it means we don't have 10,000 nearly identical stored procedures, all doing essentially the same thing -- just returning different columns, with different "group by" and "order by" clauses. Instead we have only one or two Dynamic SQL stored procedures, which build the SQL strings for the 10,000 different reports, based on user selections in the User Interface.

We get paid to provide the users with the reports they need. Different situations require different reports. How can you easily let users choose the data they want in their reports, and produce those somewhat-customized reports, without using Dynamic SQL?

|||

However, if this is a web based app, there still may be the opportunity for sQL injection.

It would be much more secure to collect the tables, columns, criteria, and sorting information into parameters, or simulated 'array' strings, and then pass those into a stored procedure and create the dynamic completely in the stored procedure.

For security purposes, I am against application embedded dynamic SQL. The DBA doens't know what's out there, and it is not easy to find and repair when business requirements force changes to the data store.

|||

>>The reports I am discussing ARE for the "OLTP System." The main purpose of the OLTP system is to provide reports to users (people are producing reports more than any other sort of transaction). <<

So are these realtime reports? Like show me exactly what is going on kinds of things? And what is the problem with returning all columns and tossing out the ones that they don't need? Are you returning that many columns with that much data?

Bottom line is that I would provide a reporting database for reporting, especially if these are not real time.

If they are real time needs, and the performance of returning 10 columns is noticably worse than 5, or 2, then dynamic SQL is not a "sin" it is just something to shy away from. I build dynamic SQL stored procedures to support some of the very same sort of things too. Just beware of the issues you can run into, is really all I think any of us are saying.

|||

Arnie,

I am not the .NET programmer, but I am sure he will not be allowing the users to enter any strings -- merely make selections from drop-down lists. So I think we will be safe from SQL Injection. We haven't yet decided on any parameter passing mechanism, or even whether we would provide these reports by a selection from a 10,000 item menu, or by having the user select columns for display, GROUP BY, and sort order.

Louis,

Yes, these are real-time reports, in the sense that a user wants to make edits to the data, and then run a report and see that the edits are reflected in the report. I suppose the user could tolerate some milliseconds of delay -- especially since it takes a while to make the selections for running a report. We are not dealing with anything like a point-of-sale system wherein the up-to-the-millisecond scanning of barcodes at the cash register might be important. Edits in the data tables are a result of numeric entries from the keyboard, so rather slow in terms of computer processing speed.

As for returning all columns and tossing out the ones they don't need, that doesn't really work since the numeric quantities must be summed according to the "GROUP BY" associated with the columns requested in the report.

The main performance issues I've noticed when returning more columns is that temporary tables used in the report procedures contain enough rows to make indexing beneficial, e.g., 200,000 rows. We will implement some way of informing the user that the number of rows returned exceed the limits of an Excel spreadsheet (approx. 65,000 rows), so the user must select a smaller portion of data for the report.

I fully agree with the notion of using Dynamic SQL only when it appears as the most appropriate solution to the problem at hand.

Thanks for your input in this discussion, from both of you.

Dan

sql

permit RESTORE

Hello,
is it possible to grant a "normal" user the right to RESTORE a special Datab
ase?
Thank You
JoachimHi,
No, "RESTORE DATABASE " can not be granted . User has to be part of any of
the below roles to perform a restore,
1. Systemadmin
2. dbcreator
3. db_owner
Thanks
Hari
MCDAB
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:403F4BF5.ADE7C3FE@.freenet.de...
> Hello,
> is it possible to grant a "normal" user the right to RESTORE a special
Database?
> Thank You
> Joachim

Permissons Question

Hi All
Is it possible to grant a user permissons so they are
only able to execute a SQL Job and not have access to
anything else in a database.
Thanks
Hi
Yes, altough not with job, but with stored procecdure.
Andras Jakus MCDBA
"anonymous@.discussions.microsoft.com" wrote:

> Hi All
> Is it possible to grant a user permissons so they are
> only able to execute a SQL Job and not have access to
> anything else in a database.
> Thanks
>

Permissions when linking to reports

Hi,

I've adding a link from my web app to reports sitting on another box. However, that box is asking the user to login. How do I set the permissions to avoid it?Would like to help!
What OS are your "boxes" running ?
Are you using Active Directory?
Are your "boxes" on the same intranet ?
What security schema are you using with SQL connections?
Best Regards,

permissions via roles query (SQL Server 2000)

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

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

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

permissions to view jobs but not change them

is there any combination of permissions or a role that will let a user view
stored procedures and their success without being able to modify them? Thanks.
What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
unless you have encrypted the procedure (then no-one can do that).
Also, what do you mean by "and their success"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> is there any combination of permissions or a role that will let a user view
> stored procedures and their success without being able to modify them? Thanks.
|||Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
there a combination of permissions or a role that would let a user view a job
and check its success without being able to modify it in any way. Thanks.
"Tibor Karaszi" wrote:

> What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
> unless you have encrypted the procedure (then no-one can do that).
> Also, what do you mean by "and their success"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
>
>
|||There is no such feature. SQL Server Agent doesn't have any "role" scheme or similar. You could try
playing with the TargetServersRole. It *might* do what you want, but it is designed for something
else...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:F0678877-B513-4AC6-BE4F-DA44830D9048@.microsoft.com...[vbcol=seagreen]
> Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
> there a combination of permissions or a role that would let a user view a job
> and check its success without being able to modify it in any way. Thanks.
> "Tibor Karaszi" wrote:
that[vbcol=seagreen]

permissions to view jobs but not change them

is there any combination of permissions or a role that will let a user view
stored procedures and their success without being able to modify them? Thanks.What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
unless you have encrypted the procedure (then no-one can do that).
Also, what do you mean by "and their success"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> is there any combination of permissions or a role that will let a user view
> stored procedures and their success without being able to modify them? Thanks.|||Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
there a combination of permissions or a role that would let a user view a job
and check its success without being able to modify it in any way. Thanks.
"Tibor Karaszi" wrote:
> What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
> unless you have encrypted the procedure (then no-one can do that).
> Also, what do you mean by "and their success"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> > is there any combination of permissions or a role that will let a user view
> > stored procedures and their success without being able to modify them? Thanks.
>
>|||There is no such feature. SQL Server Agent doesn't have any "role" scheme or similar. You could try
playing with the TargetServersRole. It *might* do what you want, but it is designed for something
else...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:F0678877-B513-4AC6-BE4F-DA44830D9048@.microsoft.com...
> Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
> there a combination of permissions or a role that would let a user view a job
> and check its success without being able to modify it in any way. Thanks.
> "Tibor Karaszi" wrote:
> > What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do
that
> > unless you have encrypted the procedure (then no-one can do that).
> >
> > Also, what do you mean by "and their success"?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> > news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> > > is there any combination of permissions or a role that will let a user view
> > > stored procedures and their success without being able to modify them? Thanks.
> >
> >
> >

permissions to run start and stop jobs

Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
only has db_owner permissions in a few databases. Should someone who is in
the db_owner role be able to start and stop jobs or run dts packages?
The user didn't used to be able to even see the jobs so I'm not sure what
changed.
--
Dan D.Normally, a user needs to be a member of sysadmins SQL
server role to start and stop any and all jobs. If not a
sysadmin, the user can only start and stop jobs that they
own.
Who owns the jobs? If the user owns the jobs, they can start
and stop the job. Also, did you verify that the user is
really using their login? Maybe they have EM registered with
a sysadmin login - that wouldn't be too hard to see in
sysprocesses when the user is connected - just check with
the hostname, program_name, loginame.
Another thing to check would be if the user is actually in
the sysamin server role inherited through Windows group
membership. If you left the BUILTIN\Administrators login in
SQL Server set to the default, was the user added to the
local administrators group on the server? That would be one
way...other ways would depend on what Windows groups are in
the syadmin SQL Server role. Then check to see if that user
is a member of any Windows group.
-Sue
On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>Using SS2000 SP4. I have a user who is able to start and stop jobs. The use
r
>only has db_owner permissions in a few databases. Should someone who is in
>the db_owner role be able to start and stop jobs or run dts packages?
>The user didn't used to be able to even see the jobs so I'm not sure what
>changed.|||BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
exist but they do not have any sysadmin type permissions.
I have another domain user setup and is the login for the SQLServer Agent
service and that user owns all jobs.
We checked the administrator groups for the domain and locally and the users
are not in there or part of a group that is in the admin group.
How would the user show up in sysprocesses? I see several entries for 'sa'
but they're all in the master database and background except for one that ha
s
'no database context' and is sleeping. Same for 'system' user.
There is an 'NT Authority\System' login that has sysadmin permisions. Is
there any way a user could use that login?
Thanks for so many choices.
--
Dan D.
"Sue Hoegemeier" wrote:

> Normally, a user needs to be a member of sysadmins SQL
> server role to start and stop any and all jobs. If not a
> sysadmin, the user can only start and stop jobs that they
> own.
> Who owns the jobs? If the user owns the jobs, they can start
> and stop the job. Also, did you verify that the user is
> really using their login? Maybe they have EM registered with
> a sysadmin login - that wouldn't be too hard to see in
> sysprocesses when the user is connected - just check with
> the hostname, program_name, loginame.
> Another thing to check would be if the user is actually in
> the sysamin server role inherited through Windows group
> membership. If you left the BUILTIN\Administrators login in
> SQL Server set to the default, was the user added to the
> local administrators group on the server? That would be one
> way...other ways would depend on what Windows groups are in
> the syadmin SQL Server role. Then check to see if that user
> is a member of any Windows group.
> -Sue
> On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Well...the user needs to be connected to see them in
sysprocesses. You'd want to know what PC they use - that
would show up in hostname.
But probably stepping back a bit is a good idea. All the
same things I posted apply but how do you know the user is
executing jobs? Do you know how the user is executing jobs -
such as does the user connect from Enterprise Manager and
just start and stop jobs at will?
-Sue
On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrato
r
>exist but they do not have any sysadmin type permissions.
>I have another domain user setup and is the login for the SQLServer Agent
>service and that user owns all jobs.
>We checked the administrator groups for the domain and locally and the user
s
>are not in there or part of a group that is in the admin group.
>How would the user show up in sysprocesses? I see several entries for 'sa'
>but they're all in the master database and background except for one that h
as
>'no database context' and is sleeping. Same for 'system' user.
>There is an 'NT Authority\System' login that has sysadmin permisions. Is
>there any way a user could use that login?
>Thanks for so many choices.|||The user said that he could start a job. And I know that someone stopped a
job the other day. Also, the user said that he could backup the database.
I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
also might change the 'sa' password on this server.
Thanks for your suggestions.
--
Dan D.
"Sue Hoegemeier" wrote:

> Well...the user needs to be connected to see them in
> sysprocesses. You'd want to know what PC they use - that
> would show up in hostname.
> But probably stepping back a bit is a good idea. All the
> same things I posted apply but how do you know the user is
> executing jobs? Do you know how the user is executing jobs -
> such as does the user connect from Enterprise Manager and
> just start and stop jobs at will?
> -Sue
> On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Yup...changing he password in case they got ahold of that is
a good idea. You may also want to run a trace when the user
is at work. Had to do those before. You tend to find other
more interesting activities they do. Often has some
entertainment value if nothing else.
-Sue
On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>The user said that he could start a job. And I know that someone stopped a
>job the other day. Also, the user said that he could backup the database.
>I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
>also might change the 'sa' password on this server.
>Thanks for your suggestions.|||A little extra entertainment is good. Thanks.
--
Dan D.
"Sue Hoegemeier" wrote:

> Yup...changing he password in case they got ahold of that is
> a good idea. You may also want to run a trace when the user
> is at work. Had to do those before. You tend to find other
> more interesting activities they do. Often has some
> entertainment value if nothing else.
> -Sue
> On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>sql

permissions to run start and stop jobs

Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
only has db_owner permissions in a few databases. Should someone who is in
the db_owner role be able to start and stop jobs or run dts packages?
The user didn't used to be able to even see the jobs so I'm not sure what
changed.
--
Dan D.Normally, a user needs to be a member of sysadmins SQL
server role to start and stop any and all jobs. If not a
sysadmin, the user can only start and stop jobs that they
own.
Who owns the jobs? If the user owns the jobs, they can start
and stop the job. Also, did you verify that the user is
really using their login? Maybe they have EM registered with
a sysadmin login - that wouldn't be too hard to see in
sysprocesses when the user is connected - just check with
the hostname, program_name, loginame.
Another thing to check would be if the user is actually in
the sysamin server role inherited through Windows group
membership. If you left the BUILTIN\Administrators login in
SQL Server set to the default, was the user added to the
local administrators group on the server? That would be one
way...other ways would depend on what Windows groups are in
the syadmin SQL Server role. Then check to see if that user
is a member of any Windows group.
-Sue
On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
>only has db_owner permissions in a few databases. Should someone who is in
>the db_owner role be able to start and stop jobs or run dts packages?
>The user didn't used to be able to even see the jobs so I'm not sure what
>changed.|||BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
exist but they do not have any sysadmin type permissions.
I have another domain user setup and is the login for the SQLServer Agent
service and that user owns all jobs.
We checked the administrator groups for the domain and locally and the users
are not in there or part of a group that is in the admin group.
How would the user show up in sysprocesses? I see several entries for 'sa'
but they're all in the master database and background except for one that has
'no database context' and is sleeping. Same for 'system' user.
There is an 'NT Authority\System' login that has sysadmin permisions. Is
there any way a user could use that login?
Thanks for so many choices.:)
--
Dan D.
"Sue Hoegemeier" wrote:
> Normally, a user needs to be a member of sysadmins SQL
> server role to start and stop any and all jobs. If not a
> sysadmin, the user can only start and stop jobs that they
> own.
> Who owns the jobs? If the user owns the jobs, they can start
> and stop the job. Also, did you verify that the user is
> really using their login? Maybe they have EM registered with
> a sysadmin login - that wouldn't be too hard to see in
> sysprocesses when the user is connected - just check with
> the hostname, program_name, loginame.
> Another thing to check would be if the user is actually in
> the sysamin server role inherited through Windows group
> membership. If you left the BUILTIN\Administrators login in
> SQL Server set to the default, was the user added to the
> local administrators group on the server? That would be one
> way...other ways would depend on what Windows groups are in
> the syadmin SQL Server role. Then check to see if that user
> is a member of any Windows group.
> -Sue
> On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
> >only has db_owner permissions in a few databases. Should someone who is in
> >the db_owner role be able to start and stop jobs or run dts packages?
> >
> >The user didn't used to be able to even see the jobs so I'm not sure what
> >changed.
>|||Well...the user needs to be connected to see them in
sysprocesses. You'd want to know what PC they use - that
would show up in hostname.
But probably stepping back a bit is a good idea. All the
same things I posted apply but how do you know the user is
executing jobs? Do you know how the user is executing jobs -
such as does the user connect from Enterprise Manager and
just start and stop jobs at will?
-Sue
On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
>exist but they do not have any sysadmin type permissions.
>I have another domain user setup and is the login for the SQLServer Agent
>service and that user owns all jobs.
>We checked the administrator groups for the domain and locally and the users
>are not in there or part of a group that is in the admin group.
>How would the user show up in sysprocesses? I see several entries for 'sa'
>but they're all in the master database and background except for one that has
>'no database context' and is sleeping. Same for 'system' user.
>There is an 'NT Authority\System' login that has sysadmin permisions. Is
>there any way a user could use that login?
>Thanks for so many choices.:)|||The user said that he could start a job. And I know that someone stopped a
job the other day. Also, the user said that he could backup the database.
I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
also might change the 'sa' password on this server.
Thanks for your suggestions.
--
Dan D.
"Sue Hoegemeier" wrote:
> Well...the user needs to be connected to see them in
> sysprocesses. You'd want to know what PC they use - that
> would show up in hostname.
> But probably stepping back a bit is a good idea. All the
> same things I posted apply but how do you know the user is
> executing jobs? Do you know how the user is executing jobs -
> such as does the user connect from Enterprise Manager and
> just start and stop jobs at will?
> -Sue
> On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
> >exist but they do not have any sysadmin type permissions.
> >
> >I have another domain user setup and is the login for the SQLServer Agent
> >service and that user owns all jobs.
> >
> >We checked the administrator groups for the domain and locally and the users
> >are not in there or part of a group that is in the admin group.
> >
> >How would the user show up in sysprocesses? I see several entries for 'sa'
> >but they're all in the master database and background except for one that has
> >'no database context' and is sleeping. Same for 'system' user.
> >
> >There is an 'NT Authority\System' login that has sysadmin permisions. Is
> >there any way a user could use that login?
> >
> >Thanks for so many choices.:)
>|||Yup...changing he password in case they got ahold of that is
a good idea. You may also want to run a trace when the user
is at work. Had to do those before. You tend to find other
more interesting activities they do. Often has some
entertainment value if nothing else.
-Sue
On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>The user said that he could start a job. And I know that someone stopped a
>job the other day. Also, the user said that he could backup the database.
>I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
>also might change the 'sa' password on this server.
>Thanks for your suggestions.|||A little extra entertainment is good. Thanks.
--
Dan D.
"Sue Hoegemeier" wrote:
> Yup...changing he password in case they got ahold of that is
> a good idea. You may also want to run a trace when the user
> is at work. Had to do those before. You tend to find other
> more interesting activities they do. Often has some
> entertainment value if nothing else.
> -Sue
> On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >The user said that he could start a job. And I know that someone stopped a
> >job the other day. Also, the user said that he could backup the database.
> >
> >I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
> >also might change the 'sa' password on this server.
> >
> >Thanks for your suggestions.
>

permissions to run sp_configure

Is possible to run sp_configure 'allow updates',1 by user
who is not granted sysadmin role?Yes, the servadmin role also has permissions. Here's the check inside
sp_configure:
if (not is_srvrolemember('serveradmin') = 1)
begin
raiserror(15247,-1,-1)
return (1)
end
Also, for this to be in effect, you need to run RECONFIGURE as well, and
according to BOL:
RECONFIGURE permissions default to members of the sysadmin and serveradmin
fixed server roles, and are not transferable.
--
Tibor Karaszi
"kim" <anonymous@.discussions.microsoft.com> wrote in message
news:040c01c3a31d$56d95a90$a401280a@.phx.gbl...
> Is possible to run sp_configure 'allow updates',1 by user
> who is not granted sysadmin role?

Permissions to create database diagrams...

Hello...
Am I able to grant permissions to sql server user logins to create and
manage database diagrams without making them db_owner or system
administrator? I am using SQL Server 2000. If I am able to, what do I need
to do to grant the permissions?
Please advise...
Thank you in advanced,
BrettBrett,
ddl_admin database fixed role membership ought to be sufficient.
Ilya
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:%2387%236oxAFHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hello...
> Am I able to grant permissions to sql server user logins to create and
> manage database diagrams without making them db_owner or system
> administrator? I am using SQL Server 2000. If I am able to, what do I
need
> to do to grant the permissions?
> Please advise...
> Thank you in advanced,
> Brett
>sql

Permissions to allow updating stored procedures

Has anyone worked out what permissions are required such
that a given database user can create, update and/or
delete stored procedures, but not do the same to
tables/views ?CREATE PROCEDURE permission is needed to modify SPs. CREATE VIEW is a
separate permission.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:fd1001c40d60$e1f1acb0$a301280a@.phx.gbl...
> Has anyone worked out what permissions are required such
> that a given database user can create, update and/or
> delete stored procedures, but not do the same to
> tables/views ?

Permissions system_function_schema

Hi all,
I have user defined functions in master databases such as
system_function_schema.fn_chariswhitespace
As default this kind of functions have a public permissions for EXEC, but I
want to remove this
When I execute the sentece
REVOKE EXEC ON system_function_schema.fn_chariswhitespace TO PUBLIC
I got nex error
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name 'system_function_schema.fn_chariswhitespace'.
I have SQL Server 2000 64 bits.
Can anybody help me?
Thanks in advance,
Javier RosasHi,
You are using an undocumented/unsupported featured. The
system_function_schema doesn't exist in SQL 2005 and you will have
difficulties upgrading your code.
I recommend you abandon use of the system_function_schema
Regards,
Clifford Dibble
"Javier Rosas" wrote:

> Hi all,
> I have user defined functions in master databases such as
> system_function_schema.fn_chariswhitespace
> As default this kind of functions have a public permissions for EXEC, but
I
> want to remove this
> When I execute the sentece
> REVOKE EXEC ON system_function_schema.fn_chariswhitespace TO PUBLIC
> I got nex error
> Server: Msg 208, Level 16, State 11, Line 1
> Invalid object name 'system_function_schema.fn_chariswhitespace'.
> I have SQL Server 2000 64 bits.
> Can anybody help me?
> Thanks in advance,
> Javier Rosas

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:

>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell wha
t
>access the public role gives a user?
>Thanks
>Don
>