Granting dbo ASSEMBLY EXTERNAL_ACCESS permissions

Hi all,

I have tried to create an asembly with PERMISSION_SET = EXTERNAL_ACCESS
under SQL Server 2005 but i failed so far.
I also see the TRUSTWORTHY Property for the database as disabled and
can't enable it.
I know that the sysadmin has the full authority for all activities, but
i'm logged in as the system xp admin account and i tried to add a
server role to grant the dbo sysadmin rights, but i also failed.
Can some one please help?

Best regardsTry to set the property with TSQL:

ALTER DATABASE <Database> SET TRUSTWORTHY ON

dbo is a database role, yo you can grant the dbo sysadmin rights.
Sysadmin rights have to be granted on the server basis.

HTH, Jens Suessmeyer.|||coosa (coosa76@.gmail.com) writes:
> I have tried to create an asembly with PERMISSION_SET = EXTERNAL_ACCESS
> under SQL Server 2005 but i failed so far.
> I also see the TRUSTWORTHY Property for the database as disabled and
> can't enable it.
> I know that the sysadmin has the full authority for all activities, but
> i'm logged in as the system xp admin account and i tried to add a
> server role to grant the dbo sysadmin rights, but i also failed.
> Can some one please help?

And the error messages are for the various things that fail are?

If you are logged into Windows with admin privs, and connect to SQL Server
with Windows authentication, you will have sysadmin rights in SQL Server,
unless you've removed BUILTIN\Administrator

--
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|||no i didn't, but the script Jens worked finally

grant select to public

I'm NOT the dbo
but I am allowed to create table/views/UDF
But I can't not do this:
grant select on vw_I_created to public
got this error:
SQL Server Database Error: Grantor does not have GRANT permission.
Is this right ?Your user was not given GRANT permission, which is the ability to grant a
specified permission to other principals.
"klabu" wrote:
> I'm NOT the dbo
> but I am allowed to create table/views/UDF
> But I can't not do this:
> grant select on vw_I_created to public
> got this error:
> SQL Server Database Error: Grantor does not have GRANT permission.
> Is this right ?
>
>|||Hi,
On specific objects if you have to give grant again to other securiy
accounts you can use or ask the admin for the grant with below option
WITH GRANT OPTION
Thanks
Ajay
"Edgardo Valdez, MCTS / MCITP"
<EdgardoValdezMCTSMCITP@.discussions.microsoft.com> wrote in message
news:B7485BF1-0937-4FE6-97A0-A4486985F37B@.microsoft.com...
> Your user was not given GRANT permission, which is the ability to grant a
> specified permission to other principals.
> "klabu" wrote:
>> I'm NOT the dbo
>> but I am allowed to create table/views/UDF
>> But I can't not do this:
>> grant select on vw_I_created to public
>> got this error:
>> SQL Server Database Error: Grantor does not have GRANT permission.
>> Is this right ?
>>

Grant rights to Truncate table to specific user 2000/2005

Hi
For certain "work" tables only, I would like to grant truncate table
ability.
In SQL2005 I can do
GRANT ALTER ON dbo.tbl#### TO myuser
Is there one syntax will work in both versions.
I don't mind if myuser is an owner of that table as long as dbo and
myuser can truncate it and other users can access it as dbo.tbl####.
I do not want to create myuser.tbl####
ThanksHi
In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
TO myuser
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>|||On 13 Nov, 14:14, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> In SQL Server if I remember well you need GRANT ALTER TABLE ON dbo.tbl####
> TO myuser
>
I tried that it returned "privilege alter table may not be granted or
revoked"
"incorrect syntax near 'on'".
Its a pain cos I want to clear these tables down quickly and don't
wanna fill log up. But do not wanna make this user dbo..|||I haven't used it yet, but I beleive you can write a proc that runs as dbo
and grant your user the rights to run the proc.
Should be easy enough to test.
<terryshamir@.gmail.com> wrote in message
news:1194962955.797517.209350@.19g2000hsx.googlegroups.com...
> Hi
> For certain "work" tables only, I would like to grant truncate table
> ability.
> In SQL2005 I can do
> GRANT ALTER ON dbo.tbl#### TO myuser
> Is there one syntax will work in both versions.
> I don't mind if myuser is an owner of that table as long as dbo and
> myuser can truncate it and other users can access it as dbo.tbl####.
> I do not want to create myuser.tbl####
> Thanks
>

Grant Premission

How do I grant permission to another user in a database to truncate a table which is owned by me.
By default only the owner of the table, DBO, SYSADMIN can truncate any table.
Is there any other alternat way that I can look for doing it ?
ThanksLike you've stated, the permiisions on TRUNCATE are;

Permissions
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

Two alternatives:
[list=1]
GRANT DELETE permission on the table to the user and then issue DELETE statement
Create a stored procedure that does the TRUNCATE and then GRANT EXECUTE on the stored procedure to the user
[/list=1]|||Thanks man.
The second option was preferable.

Actually I tried to create a procs using the users id and while he had the ddl_admin role granted, then till he had that role he was able to truncate it, but once i revoked the role from that user, he was not able to truncate.

Thanks for your help man.|||Some how after granting the execute permission to the proc, the user is still not able to tryncate the table.

It still gives the permission denied error.

Thanks

Grant permission to table for truncate table

hi,
we have object-level user security. That user need permission for truncate
one table. I can't set the user as sysadmin or DBO.
any way that I could grant the user to have truncate that table?
Thanks
If you are on 2005, you can grant CONTROL permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>

Grant permission to table for truncate table

hi,
we have object-level user security. That user need permission for truncate
one table. I can't set the user as sysadmin or DBO.
any way that I could grant the user to have truncate that table?
ThanksDELETE ?
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>|||For some reason we could NOT use delete.
1. Identity id reset.
2. large table
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
> DELETE ?
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
>|||"EXECUTE AS" ?
http://msdn2.microsoft.com/en-us/library/ms188354.aspx
"mecn" wrote:

> For some reason we could NOT use delete.
> 1. Identity id reset.
> 2. large table
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
>
>|||On Aug 2, 8:52 am, Pranil <Pra...@.discussions.microsoft.com> wrote:
> "EXECUTE AS" ?
> http://msdn2.microsoft.com/en-us/library/ms188354.aspx
>
> "mecn" wrote:
>
>
>
> - Show quoted text -
You need to have alter permission on the table to get the truncate
permission.
Thanks
Shiju Samuel|||If you are on 2005, you can grant CONTROL permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...

> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>

Grant permission to table for truncate table

hi,
we have object-level user security. That user need permission for truncate
one table. I can't set the user as sysadmin or DBO.
any way that I could grant the user to have truncate that table?
ThanksDELETE ?
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>|||For some reason we could NOT use delete.
1. Identity id reset.
2. large table
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
> DELETE ?
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
>> hi,
>> we have object-level user security. That user need permission for
>> truncate one table. I can't set the user as sysadmin or DBO.
>> any way that I could grant the user to have truncate that table?
>> Thanks
>|||"EXECUTE AS" ?
http://msdn2.microsoft.com/en-us/library/ms188354.aspx
"mecn" wrote:
> For some reason we could NOT use delete.
> 1. Identity id reset.
> 2. large table
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
> > DELETE ?
> >
> >
> >
> > "mecn" <mecn2002@.yahoo.com> wrote in message
> > news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> >> hi,
> >> we have object-level user security. That user need permission for
> >> truncate one table. I can't set the user as sysadmin or DBO.
> >> any way that I could grant the user to have truncate that table?
> >>
> >> Thanks
> >>
> >
> >
>
>|||On Aug 2, 8:52 am, Pranil <Pra...@.discussions.microsoft.com> wrote:
> "EXECUTE AS" ?
> http://msdn2.microsoft.com/en-us/library/ms188354.aspx
>
> "mecn" wrote:
> > For some reason we could NOT use delete.
> > 1. Identity id reset.
> > 2. large table
> > "Uri Dimant" <u...@.iscar.co.il> wrote in message
> >news:ODT5w1E1HHA.1100@.TK2MSFTNGP06.phx.gbl...
> > > DELETE ?
> > > "mecn" <mecn2...@.yahoo.com> wrote in message
> > >news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> > >> hi,
> > >> we have object-level user security. That user need permission for
> > >> truncate one table. I can't set the user as sysadmin or DBO.
> > >> any way that I could grant the user to have truncate that table?
> > >> Thanks- Hide quoted text -
> - Show quoted text -
You need to have alter permission on the table to get the truncate
permission.
Thanks
Shiju Samuel|||If you are on 2005, you can grant CONTROL permissions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%23HWk1zE1HHA.1336@.TK2MSFTNGP04.phx.gbl...
> hi,
> we have object-level user security. That user need permission for truncate
> one table. I can't set the user as sysadmin or DBO.
> any way that I could grant the user to have truncate that table?
> Thanks
>sql

Grant Insert Permission Freezing

I have one table that I cannot grant permissions on.
I have tried through Enterprise Manager and by executing at statement
like:
GRANT SELECT on dbo.YYY to ThisRoleName
I am db owner. I kicked everyone out of the database so I am the only
person in there. I can grant permissions to any other table in the
database. But when I try to grant permissions to table YYY the
request eventually times out.
Any ideas would be most appreciated!Run SQL Server Profiler to see what is going on while you grant permission
<juustagirl@.yahoo.com> wrote in message
news:1186787274.633524.104620@.q3g2000prf.googlegroups.com...
>I have one table that I cannot grant permissions on.
> I have tried through Enterprise Manager and by executing at statement
> like:
> GRANT SELECT on dbo.YYY to ThisRoleName
> I am db owner. I kicked everyone out of the database so I am the only
> person in there. I can grant permissions to any other table in the
> database. But when I try to grant permissions to table YYY the
> request eventually times out.
> Any ideas would be most appreciated!
>|||Execute sp_who to list current activity. Perhaps there is a rogue process
blocking your request.
Hope this helps.
Dan Guzman
SQL Server MVP
<juustagirl@.yahoo.com> wrote in message
news:1186787274.633524.104620@.q3g2000prf.googlegroups.com...
>I have one table that I cannot grant permissions on.
> I have tried through Enterprise Manager and by executing at statement
> like:
> GRANT SELECT on dbo.YYY to ThisRoleName
> I am db owner. I kicked everyone out of the database so I am the only
> person in there. I can grant permissions to any other table in the
> database. But when I try to grant permissions to table YYY the
> request eventually times out.
> Any ideas would be most appreciated!
>

global vars in script files ?

Is it possible to declare a global variable in a SQL script ... or some
workaround ?
[ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
In the following script, the local var @.ptId is lost once a "GO" is
executed.
USE [ContentDB]
GO
INSERT INTO [dbo].[PageTypes]
([ptName]
,[ptPath]
,[ptParamName])
VALUES
('unused'
,'/redirect.aspx'
,'url')
DECLARE @.ptId int
SET @.ptId = @.@.IDENTITY
.
.
.
.
<lots and lots of other SQL>
.
.
.
.
GO
.
.
.
.
<lots and lots of other SQL>
.
.
.There are no global variables in TSQL. You can use a temp table for this, or
check out SET
CONTEXT_INFO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eYtWs8JRGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Is it possible to declare a global variable in a SQL script ... or some wo
rkaround ?
> [ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
> In the following script, the local var @.ptId is lost once a "GO" is execut
ed.
> USE [ContentDB]
> GO
> INSERT INTO [dbo].[PageTypes]
> ([ptName]
> ,[ptPath]
> ,[ptParamName])
> VALUES
> ('unused'
> ,'/redirect.aspx'
> ,'url')
> DECLARE @.ptId int
> SET @.ptId = @.@.IDENTITY
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
> .
> GO
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
>