Attach database from network drive

Hello,

when I attach an database from a network drive in SQL Express 2005 the access permissions on the data and log files will be change.
This ends in a deadlock when the user doesn't have administrator right on the network server, because the permissions will allow access only for administrators and an unknown Usergroup on the network server. I think it's the SQLServer2005MSSQLUser$xxx$MSSQLSERVER group from the local server.

Example:

DBCC TRACEON(1807)
GO
PRINT 'ATTACH FIRST'
CREATE DATABASE TEST ON
( FILENAME = N'\\server\tmp\test.mdf' ),
( FILENAME = N'\\server\tmp\test_log.ldf' )
FOR ATTACH;
GO
PRINT 'Detach'
exec sp_detach_db TEST
GO

When this batch runs again after some minutes you will get

Msg 5120, Level 16, State 101, Line 2

Die physikalische Datei "\\server\tmp\test.mdf" kann nicht ge?ffnet werden. Betriebssystemfehler 5: "5(Zugriff verweigert)".

This could be translated like:

The physical file "\\server\tmp\test.mdf" could not be opend. OS Error 5 (Access Denied).

This error is the same when the user doesn't have full access on the share and db files.

In SQL2000/MSDE no security permissions will be changed, so the database could always be attached.

Is there any workaround?

Perhaps setting the security permissions could switched off?

You also need full access permissions on the share and files, wich also doesn't make sense.

Hi Dieter (thanks for translating ;-)),

you have to make sure that the SERVICE account which is starting up the SQL Server service has the appropiate permissions on the network drive. Unless this can′be accomplished you can′t attach the database via network.

HTH, Jens Suessmeyer.

|||

Hi Jens,

The Service account has permissions on the network drive.

The problem is that SQL Express need full rights because of changing the permissions when attaching the db files.

First time the database could attached, but then the permissions are changed an the database could not attach again.

Dieter Pelz

Assigning Roles to Roles

I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.

When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.

Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.I have to test it, but I find it hard to believe...

Why do you want to do this though?

Assigning Permissions!

Suppose a SQL Server 7.0 database table has, say, 9 columns
[Column1...Column9] & only 3 users can access this table. Each user
has a password. Assume that the passwords of the 3 users are 'pwd1',
'pwd2' & 'pwd3' (without the quotes).
I want that if the password is 'pwd1', then that user should be allowed
to access the records of the columns Column1, Column2 & Column3. If the
password is 'pwd2', then that user should be allowed to access the
records of the columns Column4, Column5 & Column6. If the password is
'pwd3', then that user should be allowed to access the records of the
columns Column7, Column8 & Column9.
Now is it possible to assign permissions to these 3 users column-wise
on such a table so that the first user can access the first 3 columns,
the second user can access the next 3 columns & the third user can
access the last 3 columns exisitng in that table? If so, how do I do
this?
Please note that by "access", I mean the user can do anything with the
records (edit them or delete them).
Thanks,
ArpanIt is possible to assign permissions on a per-column basis:
grant all on MyTable(MyCol1, MyCol2) to User1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1125796276.421444.185340@.g47g2000cwa.googlegroups.com...
> Suppose a SQL Server 7.0 database table has, say, 9 columns
> [Column1...Column9] & only 3 users can access this table. Each user
> has a password. Assume that the passwords of the 3 users are 'pwd1',
> 'pwd2' & 'pwd3' (without the quotes).
> I want that if the password is 'pwd1', then that user should be allowed
> to access the records of the columns Column1, Column2 & Column3. If the
> password is 'pwd2', then that user should be allowed to access the
> records of the columns Column4, Column5 & Column6. If the password is
> 'pwd3', then that user should be allowed to access the records of the
> columns Column7, Column8 & Column9.
> Now is it possible to assign permissions to these 3 users column-wise
> on such a table so that the first user can access the first 3 columns,
> the second user can access the next 3 columns & the third user can
> access the last 3 columns exisitng in that table? If so, how do I do
> this?
> Please note that by "access", I mean the user can do anything with the
> records (edit them or delete them).
> Thanks,
> Arpan
>

Assigning permissions to a table

I have a table that is being dropped and then recreated by "SELECT INTO"
through a scheduled stored procedure. So, therefore, all permissions are
being dropped as well.
I need to re-establish SELECT privileges for this table to a role containing
a user, or if it can't, a user alone. I have a role assigned to a specific
set of users who would need to SELECT this table. The stored procedure takes
care of all UPDATES/INSERTs so therefore doesn't need this.
JulianDo:
GRANT SELECT ON tbl TO <role>
Anith|||Looku GRANT in BOL
GRANT SELECT
ON authors
TO public
GO
GRANT SELECT
ON authors
TO Mary, John, Tom
GO
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Assigning Permissions to a Database

I'm trying to grant a user full access to a database.
Although I've given this user the role of db_owner, he still doesnt have the
same access as ,for example, the "dbo" user.
When I look at the table permissions, the "dbo" user has everything selected
while my user has nothing selected. Do I have to go into each table and
manually assign these permissions or can I somhow apply all of the
permissions using the database roles?
Thanks!Db_owner role members and the 'dbo' user hare full permissions over all
objects in the database. There is no need to grant object permissions
because object permissions are not checked for db_owner role members.
Hope this helps.
Dan Guzman
SQL Server MVP
"newbie" <newbie@.sql.com> wrote in message
news:eI8ZEZ7ZGHA.4788@.TK2MSFTNGP02.phx.gbl...
> I'm trying to grant a user full access to a database.
> Although I've given this user the role of db_owner, he still doesnt have
> the same access as ,for example, the "dbo" user.
> When I look at the table permissions, the "dbo" user has everything
> selected while my user has nothing selected. Do I have to go into each
> table and manually assign these permissions or can I somhow apply all of
> the permissions using the database roles?
>
> Thanks!
>|||Besides, if a user belongs to db_owner role, he isn't dbo user. For example,
if that user creates a table, this table will be created as user.tabla but
if the dbo user creates a table, this table will be created as dbo.tabla .
Then, the user should create the table hardcoding the dbo.tabla
Mpia
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23W$9p9$ZGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Db_owner role members and the 'dbo' user hare full permissions over all
> objects in the database. There is no need to grant object permissions
> because object permissions are not checked for db_owner role members.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "newbie" <newbie@.sql.com> wrote in message
> news:eI8ZEZ7ZGHA.4788@.TK2MSFTNGP02.phx.gbl...
>

Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10
Hi
"sck10" wrote:

> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got the
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:

> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute the
procedure. See below:

> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John
|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
> This would imply that the user is not actually a member of the role. See
> below:
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
> No, see below:
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>

Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10Hi
"sck10" wrote:
> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got the
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:
> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute the
procedure. See below:
> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername ='MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername ='MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
>> Hello,
>> I have the following:
>> schema: xproject
>> Database Role: WebPublisher
>> User: MyUserHandle
>> sproc: xproject.MyProcedure
>> I added the role "WebPublisher" to the schema "xproject" with
>> Delete
>> Execute
>> Insert
>> Select
>> Update
>> I added the user "MyUserHandle" to the database role "WebPublisher"
>>
>> When I tried to execute the stored procedure from an ASP.NET page, I got
>> the
>> error
>> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
>> schema 'xproject'
> This would imply that the user is not actually a member of the role. See
> below:
>> However, when I added the user to the schema and gave it the same
>> permissions as the database role, the page rendered.
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
>> My question is why do I have to assign the user to the schema when the
>> database role already has the user?
> No, see below:
>> Thanks, sck10
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername => 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername => 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>

Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10Hi
"sck10" wrote:

> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got t
he
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:

> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute th
e
procedure. See below:

> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
>
> This would imply that the user is not actually a member of the role. See
> below:
>
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
>
> No, see below:
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
Simon
This might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select@.uid = uid
from sysusers
where[name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David
|||Thanks guys!
|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh
|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegr oups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
SimonThis might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select @.uid = uid
from sysusers
where [name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David|||Thanks guys!|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegroups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
>> Hi All,
>> I have a database with about 250 stored procedures. I need to give some
>> users execute permissions on all of these procedures.
>> Can anyone tell me if there is an easy way to select a group and grant
>> execute permissions to it for all 250? I basically need the group to be
>> able to execute every SProc that I've created
>> Surely I don't have to go through every SProc one by one?
>> Please, please say it aint so!
>> :-(
>> Thanks
>> Simon
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
>>From sysobjects o
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
SimonThis might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select @.uid = uid
from sysusers
where [name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David|||Thanks guys!|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegroups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

Assign permissions to multiple objects

I know I should know the answer to this question, but unfortunately I
do not.
We have a SQL Server 7.0 production database (will be upgraded soon).
I am attempting to restore this database in my development environment
which is running SQL Server 2005 developer edition. The restore
appeared to succeed without problem, but one of the logins ended up not
getting any of the correct permissions assigned to any objects for some
reason. Is there any way to easily assign permissions to multiple
objects for a single login? If I have to do it manually and
individually it will take hours.
Thanks in advance for any advice.Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:
> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>

Assign permissions to multiple objects

Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:

> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>I know I should know the answer to this question, but unfortunately I
do not.
We have a SQL Server 7.0 production database (will be upgraded soon).
I am attempting to restore this database in my development environment
which is running SQL Server 2005 developer edition. The restore
appeared to succeed without problem, but one of the logins ended up not
getting any of the correct permissions assigned to any objects for some
reason. Is there any way to easily assign permissions to multiple
objects for a single login? If I have to do it manually and
individually it will take hours.
Thanks in advance for any advice.|||Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:

> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>

Assign permissions to allow updates but deny select on table

I have a table that I want to deny users to select, but allow inserts,
updates and deletes. So in the permissions for that table, I have
given the security Group Update, Insert and Delete rights and Denied
Select access. I connect to the table via odbc and have been able to
get the Insert queries working fine. However, my attempts to run
Update or Delete queries continually produce errors.
Here is a sample query I'm trying to run
DELETE tblPasswordsforDirector.PassMasterID
FROM tblPasswordsforDirector
WHERE (((tblPasswordsforDirector.PassMasterID)=339));
Here is a summary of the odbc error I get:
Select Permission denied on column 'password' of object
tblPasswordsforDirector.
In troubleshooting, I tried to use the COLUMN permissions to give
Select access to the two columns I don't care if the users see, and
then Deny Select access on the two columns I don't want them to see
(one of which happens to be the 'password' column shown in error
above).
I guess I'm a little confused at why a DELETE query requires Select
permissions. And if it does, then why does SQL give you the ability to
Allow DELETE permissions and Deny SELECT permissions on the same
table?
Any help would be most appreciated.
Thanks in advance.Tanzen (aaron.nasby@.gmail.com) writes:
> I have a table that I want to deny users to select, but allow inserts,
> updates and deletes. So in the permissions for that table, I have
> given the security Group Update, Insert and Delete rights and Denied
> Select access. I connect to the table via odbc and have been able to
> get the Insert queries working fine. However, my attempts to run
> Update or Delete queries continually produce errors.
> Here is a sample query I'm trying to run
> DELETE tblPasswordsforDirector.PassMasterID
> FROM tblPasswordsforDirector
> WHERE (((tblPasswordsforDirector.PassMasterID)=339));
> Here is a summary of the odbc error I get:
> Select Permission denied on column 'password' of object
> tblPasswordsforDirector.
> In troubleshooting, I tried to use the COLUMN permissions to give
> Select access to the two columns I don't care if the users see, and
> then Deny Select access on the two columns I don't want them to see
> (one of which happens to be the 'password' column shown in error
> above).
I'm not sure what you did, but this appears to work as you want:
CREATE LOGIN Julle WITH PASSWORD = 'Vetebulle'
go
CREATE USER Julle
go
CREATE TABLE spezial (a int NOT NULL,
secret varchar(23) NOT NULL)
go
GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
go
DENY SELECT ON spezial (secret) TO Julle
go
INSERT spezial (a, secret) VALUES (1, ')(2344')
INSERT spezial (a, secret) VALUES (13, '25344')
go
EXECUTE AS LOGIN = 'Julle'
go
DELETE spezial WHERE a = 13
go
REVERT
go
DROP TABLE spezial
DROP USER Julle
DROP LOGIN Julle
I tested this on SQL 2005. I also ran a similar script on SQL 2000.
Could you post a repro that demonstrates the problem?
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|||Some of the commands you show in your example don't seem to work for
me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
those commands should be in SQL 2000|||Tanzen (aaron.nasby@.gmail.com) writes:
> Some of the commands you show in your example don't seem to work for
> me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
> those commands should be in SQL 2000
I hope you've now learnt that you should always say which version of SQL
Server you are using. :-)
Below is the SQL 2000 version of the script that I posted. By the way,
you did not include the full text of your error message.
sp_addlogin Julle, 'Vetebulle'
go
sp_adduser Julle
go
CREATE TABLE spezial (a int NOT NULL,
secret varchar(23) NOT NULL)
go
GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
go
DENY SELECT ON spezial (secret) TO Julle
go
INSERT spezial (a, secret) VALUES (1, ')(2344')
INSERT spezial (a, secret) VALUES (13, '25344')
go
SETUSER 'Julle'
go
DELETE spezial WHERE a = 13
go
SETUSER
go
DROP TABLE spezial
EXEC sp_dropuser Julle
EXEC sp_droplogin Julle
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|||On Jul 11, 3:24 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Tanzen (aaron.na...@.gmail.com) writes:
> I hope you've now learnt that you should always say which version of SQL
> Server you are using. :-)
> Below is the SQL 2000 version of the script that I posted. By the way,
> you did not include the full text of your error message.
> sp_addlogin Julle, 'Vetebulle'
> go
> sp_adduser Julle
> go
> CREATE TABLE spezial (a int NOT NULL,
> secret varchar(23) NOT NULL)
> go
> GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
> go
> DENY SELECT ON spezial (secret) TO Julle
> go
> INSERT spezial (a, secret) VALUES (1, ')(2344')
> INSERT spezial (a, secret) VALUES (13, '25344')
> go
> SETUSER 'Julle'
> go
> DELETE spezial WHERE a = 13
> go
> SETUSER
> go
> DROP TABLE spezial
> EXEC sp_dropuser Julle
> EXEC sp_droplogin Julle
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodte
chnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousv
ersions/books.mspx
Your commands were very helpful. I was able to change the script for
my existing table and confirmed the security settings are working when
I use query analyzer. If I try to do a select queryI am denied
permission. If I run a Delete query, it succeeds.
I am using an MS Access 2003 database with an ODBC connection to the
tables to perform the same delete queries, but they fail. The error
when I run the delete query from MS Access 2003 is:
--
ODBC --call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Select permissio
n
denied on column 'password' of object 'tblPasswordsforDirector',
database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
Server Driver][SQL server]SELECT permission denied on column
'UserName' of object 'tblPasswordsforDirector', database
'Passwords_IT', owner 'dbo'. (#230)
--
I'm assuming I need to post this to an Access forum.
So unless you have any ideas, I'll move this on to another forum.
Thank you again for your help.|||Tanzen (aaron.nasby@.gmail.com) writes:
> I am using an MS Access 2003 database with an ODBC connection to the
> tables to perform the same delete queries, but they fail. The error
> when I run the delete query from MS Access 2003 is:
> --
> ODBC --call failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server] Select permiss
ion
> denied on column 'password' of object 'tblPasswordsforDirector',
> database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
> Server Driver][SQL server]SELECT permission denied on column
> 'UserName' of object 'tblPasswordsforDirector', database
> 'Passwords_IT', owner 'dbo'. (#230)
> --
> I'm assuming I need to post this to an Access forum.
Yes, they may be more helpful. But you could use SQL Server profiler
to see what statements that Access is submitting. Maybe it's better to
use a stored procedure instead. But the Access folks may know how tell
Access to do the right thing.
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

Assembly permissions for accessing db

Hi there, I need to know how to set permissions for my assembly, since
it has to access a db and without permissions I could not distribute
it...
Can somebody explain it in a breath, please?This article gives a great overview of permissions.
http://www.codeproject.com/dotnet/CustomAssemblies.asp
I wish I had found this before I figured it out for myself.
Craig
"Tur" <cenci.cristiano@.gmail.com> wrote in message
news:1142245992.537105.156310@.j33g2000cwa.googlegroups.com...
> Hi there, I need to know how to set permissions for my assembly, since
> it has to access a db and without permissions I could not distribute
> it...
> Can somebody explain it in a breath, please?
>

ASPState DB Permissions

I need to find out what the 'Required' permissions on on the ASPState database? We have been granting dbo to the login. Thanks.

I finaly figured out how to do this, I created a database role. and assigned implicit permissions to the stored procedures for the ASPState database. The assigned the owner to be dbo. Then I assigned the custom role to the login that needed access. This way I avoided applying db_owner permissions to the ASPState database for my login.

ASPNET permissions error when trying to open an Oracle connectin from a custom assembly.

Hello:
I'm receiving the following error while trying to open an Oracle connection
from a custom assembly; it seems that ASP.NET has insufficient permissions.
OracleConnection oc = null;
// v-- creating connection object, this
is where the exception throws..
oc = new OracleConnection(Def.ConnectString +
";User Id=" + Def.UserName + ";Password=pwdtxt");
I've assigned "FullTrust" to all the CodeGroup entries on my config files,
and I can execute the code in the custom assembly but I can not open a
database connection.
Is there any security setting related with this?
Thanks,
Daniel Bello.
<ErrorCode
xmlns="http://www.microsoft.com/sql/reportingservices">rsAccessDenied</ErrorCode><HttpStatus
xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message
xmlns="http://www.microsoft.com/sql/reportingservices">The permissions
granted to user 'WCORPTWJTCCPB1\ASPNET' are insufficient for performing this
operation.</Message><HelpLink
xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsAccessDenied&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00</HelpLink><ProductName
xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server
Reporting Services</ProductName><ProductVersion
xmlns="http://www.microsoft.com/sql/reportingservices">9.00.1399.00</ProductVersion><ProductLocaleId
xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem
xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId
xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation
xmlns="http://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Message
msrs:ErrorCode="rsAccessDenied"
msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsAccessDenied&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00"
xmlns:msrs="http://www.microsoft.com/sql/reportingservices">The permissions
granted to user 'WCORPTWJTCCPB1\ASPNET' are insufficient for performing this
operation.</Message></MoreInformation><Warnings
xmlns="http://www.microsoft.com/sql/reportingservices" />I added the ASPNET user to HOME with Browser and Content Manager permissions
and it works now.
Does anyoune has an explanation for this?
Thanks,
Daniel Bello.
"Daniel Bello" <dburizarri@.yahoo.es> wrote in message
news:OzjxxzYRHHA.4000@.TK2MSFTNGP04.phx.gbl...
> Hello:
> I'm receiving the following error while trying to open an Oracle
> connection from a custom assembly; it seems that ASP.NET has insufficient
> permissions.
> OracleConnection oc = null;
> // v-- creating connection object, this
> is where the exception throws..
> oc = new OracleConnection(Def.ConnectString +
> ";User Id=" + Def.UserName + ";Password=pwdtxt");
> I've assigned "FullTrust" to all the CodeGroup entries on my config files,
> and I can execute the code in the custom assembly but I can not open a
> database connection.
> Is there any security setting related with this?
> Thanks,
> Daniel Bello.
> <ErrorCode
> xmlns="rsAccessDenied</ErrorCode><HttpStatus">http://www.microsoft.com/sql/reportingservices">rsAccessDenied</ErrorCode><HttpStatus
> xmlns="400</HttpStatus><Message">http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message
> xmlns="The">http://www.microsoft.com/sql/reportingservices">The permissions
> granted to user 'WCORPTWJTCCPB1\ASPNET' are insufficient for performing
> this operation.</Message><HelpLink
> xmlns="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsAccessDenied&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00</HelpLink><ProductName">http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsAccessDenied&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00</HelpLink><ProductName
> xmlns="Microsoft">http://www.microsoft.com/sql/reportingservices">Microsoft SQL
> Server Reporting Services</ProductName><ProductVersion
> xmlns="9.00.1399.00</ProductVersion><ProductLocaleId">http://www.microsoft.com/sql/reportingservices">9.00.1399.00</ProductVersion><ProductLocaleId
> xmlns="127</ProductLocaleId><OperatingSystem">http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem
> xmlns="OsIndependent</OperatingSystem><CountryLocaleId">http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId
> xmlns="1033</CountryLocaleId><MoreInformation">http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation
> xmlns="<Source>ReportingServicesLibrary</Source><Message">http://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Message
> msrs:ErrorCode="rsAccessDenied"
> msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsAccessDenied&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00"
> xmlns:msrs="The">http://www.microsoft.com/sql/reportingservices">The
> permissions granted to user 'WCORPTWJTCCPB1\ASPNET' are insufficient for
> performing this operation.</Message></MoreInformation><Warnings
> xmlns="http://www.microsoft.com/sql/reportingservices" />
>

ASPNET account permissions

I am new to Reporting Services and still learning, but it is a very nice exciting program. I built a new Report and was experimenting with Grouping in reports and using the parameters. Now I would like to Deploy the report to the Report Server so I can use this report on our intranet, however I get the error that the ASPNET account does not have the proper permissions. I am sure that I need to set up ASPNET to have "write" abilities.

What folder(s) do I need to make sure that ASPNET has this? Or should I be looking elsewhere to set up the ASPNET account on the Report Server?

Thanks for the information.

Will you post the exact error message you are seeing? Does it indicate which resource is not permissioned?|||

This is the exact error message when I try to deploy the report:

Error 1 The permissions granted to user 'BRAD\ASPNET' are insufficient for performing this operation. 0 0

|||The folder that needs permission is the root folder in the report server namespace. But running as the ASPNet account is usually not what you want, since all users will be connecting as the same user account. It looks like your report server config file has impersonation turned off. Is this something you changed intentionally?|||

Brian,

For now I did go into Report Services through the SQL Management Studios, went into properties and did see that ASPNET was unchecked. I checked it and now the reports deploy just fine.

Thanks for the great information.

ASP.Net Permissions

Hi
Try as I might I really can't work out to do this (I've searched
usenet and help).
My ASP.NET application needs to access an SQL Server 2000 database (on
a Windows 2003 box). I appear to be running mixed mode authentication.
I want to connect to the box using intigrated security. I think all I
need to do is apply appropriate permissions to 'NT AUTHORITY\NETWORK
SERVICE'.
I can't work out where and how to do this! What permissions and which
app I use to make the changes etc?
Can anybody help?
Sorry for the lame question.
Cheers
AlexIf you want to use integrated security, then you need to enable the
ASPNET windows account and grant it permissions in the database since
that is the worker process account that asp.net uses. However, it may
not be that simple if SQLS and your web server are running on
different boxes. There is an excellent asp.net security best practices
whitepaper you should read before proceeding further:
http://www.microsoft.com/downloads/...ReleaseID=44047
--Mary
On 30 Jul 2004 04:13:22 -0700, postings@.alexshirley.com (Alex Shirley)
wrote:

>Hi
>Try as I might I really can't work out to do this (I've searched
>usenet and help).
>My ASP.NET application needs to access an SQL Server 2000 database (on
>a Windows 2003 box). I appear to be running mixed mode authentication.
>I want to connect to the box using intigrated security. I think all I
>need to do is apply appropriate permissions to 'NT AUTHORITY\NETWORK
>SERVICE'.
>I can't work out where and how to do this! What permissions and which
>app I use to make the changes etc?
>Can anybody help?
>Sorry for the lame question.
>Cheers
>Alex|||Thanks. They are both running on the same box. I'll look into the article.
I simply want to assign permissions to that service and I don't know how.
Thanks
Alex
Mary Chipman <mchip@.online.microsoft.com> wrote in message news:<oholg09nuoo7j58rbufdo5fcldu
efc3ch4@.4ax.com>...[vbcol=seagreen]
> If you want to use integrated security, then you need to enable the
> ASPNET windows account and grant it permissions in the database since
> that is the worker process account that asp.net uses. However, it may
> not be that simple if SQLS and your web server are running on
> different boxes. There is an excellent asp.net security best practices
> whitepaper you should read before proceeding further:
> http://www.microsoft.com/downloads/...ReleaseID=44047
> --Mary
> On 30 Jul 2004 04:13:22 -0700, postings@.alexshirley.com (Alex Shirley)
> wrote:
>