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]
>

---

Permisson on views only

All,
Is that possible to have a role that has admin permission on views, but only
read/write permission on tables?
Thanks!
TinaSure, you can create your own role e.g.
exec sp_addrole 'ViewCreators'
grant create view to ViewCreators
exec sp_addrolemember 'db_datareader','ViewCreators'
exec sp_addrolemember 'db_datawriter','ViewCreators'
You can then just add users to that role.
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
"Tina Ma" <tina.ma@.parsons.com> wrote in message
news:etFp$YyfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> All,
> Is that possible to have a role that has admin permission on views, but
> only
> read/write permission on tables?
> Thanks!
> Tina
>

Permissions to read error log.

I want to give developers permission to read the error log on the production
server. How can I do this without making them Administrator or
Securityadmin?"Eric w" <ewientzek@.hotmail.com> wrote in message
news:OfqjBDn8EHA.3260@.TK2MSFTNGP14.phx.gbl...
> I want to give developers permission to read the error log on the
production
> server. How can I do this without making them Administrator or
> Securityadmin?
>
Since this is a text file, I'd suggest creating a share pointing to the
"LOG" folder (should be in "Program Files\Microsoft SQL Server\MSSQL\LOG" if
you took the default installation path). Allow the developers RX permission
to the NTFS layer of that folder.
Stevesql

permissions resetting on a View

I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx
.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||
>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx
.gbl...
>
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell

permissions resetting on a View

I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
Terrell
When you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell
|||
>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell
|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> that matter, you
> objects. If you what to
> view and recreate,
> object the permissions
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell

permissions resetting on a View

I have a View that pulls data from one table.
I have assigned a Role with Select-only permission for
that View.
For some reason the permissions for that Role/View keep
getting deleted, so I have to go back and re-grant Select
access over and over again.
Anybody know why this is happening, and is there any way
to prevent it? There should be no change to the
permissions for that Role at all.
TIA,
TerrellWhen you delete a Role or a View, or any other object for that matter, you
also delete the permissions associated with those objects. If you what to
keep the permissions for the View then don't delete the view and recreate,
but instead just ALTER the view. When you ALTER an object the permissions
that are associated with the object stay intact.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2afa201c46819$3b6e4510$a501280a@.phx.gbl...
> I have a View that pulls data from one table.
> I have assigned a Role with Select-only permission for
> that View.
> For some reason the permissions for that Role/View keep
> getting deleted, so I have to go back and re-grant Select
> access over and over again.
> Anybody know why this is happening, and is there any way
> to prevent it? There should be no change to the
> permissions for that Role at all.
> TIA,
> Terrell|||>--Original Message--
>When you delete a Role or a View, or any other object for
that matter, you
>also delete the permissions associated with those
objects. If you what to
>keep the permissions for the View then don't delete the
view and recreate,
>but instead just ALTER the view. When you ALTER an
object the permissions
>that are associated with the object stay intact.
Greg, we aren't changing the view or the roles. It's just
that from time to time the Select permission on that View
gets removed.
Question: when you use sp_refreshview does that actually
delete the view and recreate it? I can't set up an ALTER
inside a sproc (because the ALTER has to be the first line
in a batch, but the CREATE PROCEDURE statement has to
execute before it), which is why I'm using sp_refreshview.
Thanks again,
Terrell|||Since the sp_renameview is a system store procedure, I'm not exactly sure
whether it drops and recreates the view. I did a little test I did, when
you run the sp_refreshview it appears to keep the permissions on a view.
If you really want to create or alter a view via a stored procedure you can
do that with dynamic SQL. Something like so:
create procedure yoursp as
declare @.cmd char(1000)
set @.cmd = 'alter view yourview as select bing, bang, boom from yourtable'
exec(@.cmd)
-- rest of sp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Terrell Miller" <millerto@.bellsouth.net> wrote in message
news:2b1a001c4682f$259d8b90$a501280a@.phx.gbl...
> >--Original Message--
> >When you delete a Role or a View, or any other object for
> that matter, you
> >also delete the permissions associated with those
> objects. If you what to
> >keep the permissions for the View then don't delete the
> view and recreate,
> >but instead just ALTER the view. When you ALTER an
> object the permissions
> >that are associated with the object stay intact.
> Greg, we aren't changing the view or the roles. It's just
> that from time to time the Select permission on that View
> gets removed.
> Question: when you use sp_refreshview does that actually
> delete the view and recreate it? I can't set up an ALTER
> inside a sproc (because the ALTER has to be the first line
> in a batch, but the CREATE PROCEDURE statement has to
> execute before it), which is why I'm using sp_refreshview.
> Thanks again,
> Terrell

Permissions required to run DTS packages and jobs

What SQL Server permission(s) are required to run DTS packages and jobs in
SQL Server 2000 and SQL Server 2005?
Thank you.
--
MikeMike
SQL Server 2000
1) You are the owner of the package
2) Jobs are ran under account that SQL Server Agent are runing under.
3) Lookuop TargerServerRole in the BOL
SQL Server 2005
1) I haven't played to much with this issue. If I remember well you can
create a proxy account to run jobs
with an acount that is not a member of sysadmin server role.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:57249ED9-D4A8-4733-BF06-F903C65CCD83@.microsoft.com...
> What SQL Server permission(s) are required to run DTS packages and jobs in
> SQL Server 2000 and SQL Server 2005?
> Thank you.
> --
> Mikesql

Permissions Question

I've got a permission question for you. In SQL Server 2000, if a user
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.codecomments.com ***Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:

> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||Thank you for the quick response. I don't have any denies so that tells
me what I need to know.
Thanks!
Toni
*** Sent via Developersdex http://www.codecomments.com ***

Permissions Question

I've got a permission question for you. In SQL Server 2000, if a user
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.codecomments.com ***
Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:

> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thank you for the quick response. I don't have any denies so that tells
me what I need to know.
Thanks!
Toni
*** Sent via Developersdex http://www.codecomments.com ***

Permissions Question

I've got a permission question for you. In SQL Server 2000, if a user
is given a read-only role (which is select to all the tables in a db)
AND another role that allows updates to those same tables, what rights
will prevail?
Toni
*** Sent via Developersdex http://www.developersdex.com ***Rights are addtive, so additional "allow" rulwes which ensure that the user
will have a greater access to the data, except the deny roles which prevent
him regardless in how many roles he is in to allow him doing something.
HTH, Jens Suessmeyer.
"Toni" wrote:
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Toni
Both, unless you give them DENY permission.
"Toni" <teibner@.SQLallina.com> wrote in message
news:OQNBKc5hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> I've got a permission question for you. In SQL Server 2000, if a user
> is given a read-only role (which is select to all the tables in a db)
> AND another role that allows updates to those same tables, what rights
> will prevail?
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4iyb.asp
Davidsql

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
MG
"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/de...urity_4iyb.asp
David

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/d...>
ity_4iyb.asp
David

permissions on a column

hi,

i run a sqlserver 2000 and im having problems setting a permission a
column in a table..

under a database i have a User that has dataread rights on each table
in the database, but in one table i want to prevent the user from
seeing a column in one perticular table.

i have created the user under security and then i choose the database
user - properties..and i set a X in the specified column...

when i log on as the datareader user i cant see any colummn at all in
the table..

what have a done wrong?

Steve[posted and mailed, vnligen svara i nys]

steve (stebo@.privat.utfors.se) writes:
> i run a sqlserver 2000 and im having problems setting a permission a
> column in a table..
> under a database i have a User that has dataread rights on each table
> in the database, but in one table i want to prevent the user from
> seeing a column in one perticular table.
> i have created the user under security and then i choose the database
> user - properties..and i set a X in the specified column...
> when i log on as the datareader user i cant see any colummn at all in
> the table..
> what have a done wrong?

Used a GUI instead of looking up the commands in Books Online. GUIs may
do the what you expect, or they may do something else. The command to
use is DENY. Here is an example:

use tempdb
go
exec sp_addlogin accesstest, secret
exec sp_adduser accesstest
exec sp_addrolemember db_datareader, accesstest
go
CREATE TABLE tbl (a int NOT NULL, b varchar(23) NOT NULL)
INSERT tbl (a, b) VALUES (9, 'Top secret')
go
SETUSER 'accesstest'
go
SELECT * FROM tbl
go
SETUSER
go
DENY ALL ON tbl (b) TO accesstest
go
SETUSER 'accesstest'
go
SELECT * FROM tbl
go
SETUSER
go
DROP TABLE tbl
EXEC sp_dropuser accesstest
EXEC sp_droplogin accesstest

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks for your help!

I will try this, what do you think about using a VIEW, is this a good choice?

BR

Steve
--

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns959DF3D2FD2D0Yazorman@.127.0.0.1>...
> [posted and mailed, vnligen svara i nys]
> steve (stebo@.privat.utfors.se) writes:
> > i run a sqlserver 2000 and im having problems setting a permission a
> > column in a table..
> > under a database i have a User that has dataread rights on each table
> > in the database, but in one table i want to prevent the user from
> > seeing a column in one perticular table.
> > i have created the user under security and then i choose the database
> > user - properties..and i set a X in the specified column...
> > when i log on as the datareader user i cant see any colummn at all in
> > the table..
> > what have a done wrong?
> Used a GUI instead of looking up the commands in Books Online. GUIs may
> do the what you expect, or they may do something else. The command to
> use is DENY. Here is an example:
> use tempdb
> go
> exec sp_addlogin accesstest, secret
> exec sp_adduser accesstest
> exec sp_addrolemember db_datareader, accesstest
> go
> CREATE TABLE tbl (a int NOT NULL, b varchar(23) NOT NULL)
> INSERT tbl (a, b) VALUES (9, 'Top secret')
> go
> SETUSER 'accesstest'
> go
> SELECT * FROM tbl
> go
> SETUSER
> go
> DENY ALL ON tbl (b) TO accesstest
> go
> SETUSER 'accesstest'
> go
> SELECT * FROM tbl
> go
> SETUSER
> go
> DROP TABLE tbl
> EXEC sp_dropuser accesstest
> EXEC sp_droplogin accesstest|||steve (stebo@.privat.utfors.se) writes:
> thanks for your help!
> I will try this, what do you think about using a VIEW, is this a good
> choice?

I don't know your business problem, so I cannot comment on that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Permissions not applied

Hi everyone,
I'm facing a weird problem trying to set permissions on a column in Sql
Server 2000. When using Enterprise Manager the permission on some columns
don't get applied, that is setting Select permissions on Column1, Column2 an
d
Column3 only saves the permission on Column1 and Column2 but not on Column3.
I tried setting the permissions with a GRANT script in the Query Analyzer,
but still without any luck.
Service Pack 4 has been applied to SQL Server 2000.
Has anyone had the same type of troubles setting permissions on columns?
TIA,
Mogens NielsenMogens Nielsen - WM-Data A/S
(MogensNielsenWMDataAS@.discussions.microsoft.com) writes:
> I'm facing a weird problem trying to set permissions on a column in Sql
> Server 2000. When using Enterprise Manager the permission on some
> columns don't get applied, that is setting Select permissions on
> Column1, Column2 and Column3 only saves the permission on Column1 and
> Column2 but not on Column3. I tried setting the permissions with a GRANT
> script in the Query Analyzer, but still without any luck.
Would it be possible for your to post the CREATE TABLE statement for
the table, and the GRANT statements you are using?
It would also be interesting to know how you conclude that the
permission is not applied.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
I did some testing but not usimg EM and it seems to be worked fine
use demo
create table test (c1 int,c2 int,c3 int)
go
grant select on test to test --test no syadmin,db_owner
go
deny select on test (c3) to test
--connected as test
select * from test
--Server: Msg 229, Level 14, State 5, Line 1
--SELECT permission denied on object 'test', database 'demo', owner 'dbo'.
select c1,c2,c3 from test
--Server: Msg 229, Level 14, State 5, Line 1
--SELECT permission denied on object 'test', database 'demo', owner 'dbo'.
select c1,c2 from test
--works fine
"Mogens Nielsen - WM-Data A/S"
<MogensNielsenWMDataAS@.discussions.microsoft.com> wrote in message
news:FC8AFD90-255F-4942-BC78-BE674669A1FE@.microsoft.com...
> Hi everyone,
> I'm facing a weird problem trying to set permissions on a column in Sql
> Server 2000. When using Enterprise Manager the permission on some columns
> don't get applied, that is setting Select permissions on Column1, Column2
> and
> Column3 only saves the permission on Column1 and Column2 but not on
> Column3.
> I tried setting the permissions with a GRANT script in the Query Analyzer,
> but still without any luck.
> Service Pack 4 has been applied to SQL Server 2000.
> Has anyone had the same type of troubles setting permissions on columns?
> TIA,
> Mogens Nielsen|||Hi Uri and Erland,
thank you both for trying to solve my problems... :-)
The table consists of more than 350 columns, which may be the cause. I don't
know, really. As a solution I created a view on a subset of the columns
instead, in which I've managed to set the permissions.
So problem solved or at least avoided...
Regards,
Mogens
"Uri Dimant" wrote:

> Hi
> I did some testing but not usimg EM and it seems to be worked fine
> use demo
> create table test (c1 int,c2 int,c3 int)
> go
> grant select on test to test --test no syadmin,db_owner
> go
> deny select on test (c3) to test
> --connected as test
> select * from test
> --Server: Msg 229, Level 14, State 5, Line 1
> --SELECT permission denied on object 'test', database 'demo', owner 'dbo'
.
> select c1,c2,c3 from test
> --Server: Msg 229, Level 14, State 5, Line 1
> --SELECT permission denied on object 'test', database 'demo', owner 'dbo'
.
> select c1,c2 from test
> --works fine
>
>
>
> "Mogens Nielsen - WM-Data A/S"
> <MogensNielsenWMDataAS@.discussions.microsoft.com> wrote in message
> news:FC8AFD90-255F-4942-BC78-BE674669A1FE@.microsoft.com...
>
>sql

permissions mystery: ActiveDirectory issue?

I don't see why my user cannot see a view when she can see the tables that
underlie it and has also been granted select permission on the view.
exec sp_grantlogin [OURDOMAIN\user99]
exec sp_grantdbaccess [OURDOMAIN\user99], 'SARAH'
exec sp_addrole 'TheRole'
exec sp_addrolemember 'TheRole', 'SARAH'create view TestView
as select * from table1
inner join table2
on t1.id = t2.anotherid
grant select on table1 to TheRole
grant select on table2 to TheRole
grant select on TestView to TheRole
User SARAH can see the tables but not the view.
We're using SQL Server 2000 and Windows 2003 Server with ActiveDirectory.
Thanks
TimoI've installed Query Analyzer on the user's desktop, and she CAN see the
view. So the problem has to do with the client application (Access 2000 ADP)
and/or ActiveDirectory. Our domain admin put the Access 2000 ADP on his PC
and he can see the view fine.
Timo
"Timo" <Timo@.unspam.biz> wrote in message
news:eRc2XxAeFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I don't see why my user cannot see a view when she can see the tables that
> underlie it and has also been granted select permission on the view.
> exec sp_grantlogin [OURDOMAIN\user99]
> exec sp_grantdbaccess [OURDOMAIN\user99], 'SARAH'
> exec sp_addrole 'TheRole'
> exec sp_addrolemember 'TheRole', 'SARAH'create view TestView
> as select * from table1
> inner join table2
> on t1.id = t2.anotherid
>
> grant select on table1 to TheRole
> grant select on table2 to TheRole
> grant select on TestView to TheRole
> User SARAH can see the tables but not the view.
> We're using SQL Server 2000 and Windows 2003 Server with ActiveDirectory.
> Thanks
> Timo
>

Permissions

Hi
How to solve next problem
a) I have database TEST
b) Login and user - webguest
Now i need set SELECT permission to all tables and views and INSERT,UPDATE,
DELETE perimissions
on couple tables.
I have tried management studio but no luck )
Regards;
Red
Hi
Is 'webguest' member of db_owner database role? Add him to this role
"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and
> INSERT,UPDATE, DELETE perimissions
> on couple tables.
> I have tried management studio but no luck )
>
> Regards;
> Red
>
|||yes webguest is db_owner
but setting all permissions manually is veeeery hard there are over 100
tables in db.
how to set GRANT SELECT all tables and DENY UPDATE,INSERT,DELETE to all
tables
and then manually set GRANT INSERT, UPDATE, DELETE to specific tables
Red.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:elI30eFdHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi
> Is 'webguest' member of db_owner database role? Add him to this role
> "Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
>
|||You can run the following select statement within the database you wish to
grant the permission to - then execute the resulting code.
select 'grant select,insert,update,delete on ' + name + ' to webguest' +
char(13) + ';' from sysobjects where xtype in( 'U','V')
Thanks,
Scott H.
"Redivivus" wrote:

> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and INSERT,UPDATE,
> DELETE perimissions
> on couple tables.
> I have tried management studio but no luck )
>
> Regards;
> Red
>
>

Permissions

Hi
How to solve next problem
a) I have database TEST
b) Login and user - webguest
Now i need set SELECT permission to all tables and views and INSERT,UPDATE,
DELETE perimissions
on couple tables.
I have tried management studio but no luck )
Regards;
RedHi
Is 'webguest' member of db_owner database role? Add him to this role
"Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and
> INSERT,UPDATE, DELETE perimissions
> on couple tables.
> I have tried management studio but no luck )
>
> Regards;
> Red
>|||yes webguest is db_owner
but setting all permissions manually is veeeery hard there are over 100
tables in db.
how to set GRANT SELECT all tables and DENY UPDATE,INSERT,DELETE to all
tables
and then manually set GRANT INSERT, UPDATE, DELETE to specific tables
Red.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:elI30eFdHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi
> Is 'webguest' member of db_owner database role? Add him to this role
> "Redivivus" <meelis.lilbok@.deltmar.ee> wrote in message
> news:uGnVUsEdHHA.3632@.TK2MSFTNGP02.phx.gbl...
>|||You can run the following select statement within the database you wish to
grant the permission to - then execute the resulting code.
select 'grant select,insert,update,delete on ' + name + ' to webguest' +
char(13) + ';' from sysobjects where xtype in( 'U','V')
--
Thanks,
Scott H.
"Redivivus" wrote:

> Hi
> How to solve next problem
> a) I have database TEST
> b) Login and user - webguest
> Now i need set SELECT permission to all tables and views and INSERT,UPDATE
,
> DELETE perimissions
> on couple tables.
> I have tried management studio but no luck )
>
> Regards;
> Red
>
>

permissions

I am working http location and using sql server 2005 ,it is showing an error as " DELETE permission denied on object 'CourseDetails', database 'LOGIN', schema 'dbo'." CourseDetails is my table name and LOGIN is my database name.

Goto SQL Server Management Studio 2005--> Goto your database (LOGIN)--> Right click on the table name (CourseDetails)--> Select Properties

There goto the Permissions tab and click add... You can add the permission here...

But the question is WHY would you want to add delete permissions on your SQL Table? What you should do is write a Stored Procedure to delete the data in there and give the procedure execute permissions.

|||

Thank u deepak

permissions

Hi,
I am sending parameters to the report from asp.net.
In my aspx pages I have a permission check where I check whether or not the
user can access the page:
Dim myCurrentUser As PnClassLib.currentUser
If IsNothing(Session("currUserObj")) Then
Page.Response.Redirect("../AccessDenied.aspx")
I want to add the above to rss so that if the user didn't login or if he
doesn't have the right permissions he won't be able to view the report.
Is it possible to do that in rss?
ThanksRS supports login permissions directly, so you could just add the
appropriate permissions.
Another thing you could do is to put an Asp.net page in front of the report,
and do the checking/redirecting or call the report from your asp.netpage...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"collie" <collie@.discussions.microsoft.com> wrote in message
news:7EC86BD7-099C-448C-841B-409FA4F7294B@.microsoft.com...
> Hi,
> I am sending parameters to the report from asp.net.
> In my aspx pages I have a permission check where I check whether or not
> the
> user can access the page:
> Dim myCurrentUser As PnClassLib.currentUser
> If IsNothing(Session("currUserObj")) Then
> Page.Response.Redirect("../AccessDenied.aspx")
> I want to add the above to rss so that if the user didn't login or if he
> doesn't have the right permissions he won't be able to view the report.
> Is it possible to do that in rss?
> Thanks
>|||Thanks for the reply.
I am redirecting the user to the report from asp.net. In asp.net I check if
the user logged in and who he is and if he has the right permissions he can
select parameters from asp.net and sent them to the report.
The problem is that anyone can cut and paste the address line in the browser
without first having logged in and be able to view the report.
I need to prevent this from happening.
Is it possible in the report itself to add something like this:
Dim myCurrentUser As PnClassLib.currentUser
If IsNothing(Session("currUserObj")) Then
Page.Response.Redirect("../AccessDenied.aspx")
Thanks
"Wayne Snyder" wrote:
> RS supports login permissions directly, so you could just add the
> appropriate permissions.
> Another thing you could do is to put an Asp.net page in front of the report,
> and do the checking/redirecting or call the report from your asp.netpage...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:7EC86BD7-099C-448C-841B-409FA4F7294B@.microsoft.com...
> > Hi,
> >
> > I am sending parameters to the report from asp.net.
> > In my aspx pages I have a permission check where I check whether or not
> > the
> > user can access the page:
> > Dim myCurrentUser As PnClassLib.currentUser
> > If IsNothing(Session("currUserObj")) Then
> > Page.Response.Redirect("../AccessDenied.aspx")
> >
> > I want to add the above to rss so that if the user didn't login or if he
> > doesn't have the right permissions he won't be able to view the report.
> >
> > Is it possible to do that in rss?
> >
> > Thanks
> >
>
>