permissions xp_cmdshell

Hi!!!
which permissions are necessary to execute xp_cmdshell?
-->without being SA
Error:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 87 from GetProxyAccount on line 604
tanks!!!You will need to set up a proxy account first using
"xp_sqlagent_proxy_account". Then you should get the permission associated
with the account you "set" up as your proxy account.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Joao Mossmann" <mossmann@.feevale.br> wrote in message
news:%234YPh1rLEHA.1556@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> which permissions are necessary to execute xp_cmdshell?
> -->without being SA
> Error:
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 87 from GetProxyAccount on line 604
>
> tanks!!!
>
>|||Heres what Ive done in SQL 2000. RClick the SQL Agent/ props/ job system/
uncheck Only users with SA bla bla.../ fill in appropriate credentials. Most
imprtantly, DO NOT set permissions on xp_cmdshell itself.
"Joao Mossmann" <mossmann@.feevale.br> wrote in message
news:%234YPh1rLEHA.1556@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> which permissions are necessary to execute xp_cmdshell?
> -->without being SA
> Error:
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 87 from GetProxyAccount on line 604
>
> tanks!!!
>
>

permissions xp_cmdshell

Hi!!!
which permissions are necessary to execute xp_cmdshell?
-->without being SA
Error:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 87 from GetProxyAccount on line 604
tanks!!!
You will need to set up a proxy account first using
"xp_sqlagent_proxy_account". Then you should get the permission associated
with the account you "set" up as your proxy account.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Joao Mossmann" <mossmann@.feevale.br> wrote in message
news:%234YPh1rLEHA.1556@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> which permissions are necessary to execute xp_cmdshell?
> -->without being SA
> Error:
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 87 from GetProxyAccount on line 604
>
> tanks!!!
>
>
|||Heres what Ive done in SQL 2000. RClick the SQL Agent/ props/ job system/
uncheck Only users with SA bla bla.../ fill in appropriate credentials. Most
imprtantly, DO NOT set permissions on xp_cmdshell itself.
"Joao Mossmann" <mossmann@.feevale.br> wrote in message
news:%234YPh1rLEHA.1556@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> which permissions are necessary to execute xp_cmdshell?
> -->without being SA
> Error:
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 87 from GetProxyAccount on line 604
>
> tanks!!!
>
>

permissions xp_cmdshell

Hi!!!
which permissions are necessary to execute xp_cmdshell?
-->without being SA
Error:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 87 from GetProxyAccount on line 604
tanks!!!You will need to set up a proxy account first using
"xp_sqlagent_proxy_account". Then you should get the permission associated
with the account you "set" up as your proxy account.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Joao Mossmann" <mossmann@.feevale.br> wrote in message
news:%234YPh1rLEHA.1556@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> which permissions are necessary to execute xp_cmdshell?
> -->without being SA
> Error:
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 87 from GetProxyAccount on line 604
>
> tanks!!!
>
>|||Heres what Ive done in SQL 2000. RClick the SQL Agent/ props/ job system/
uncheck Only users with SA bla bla.../ fill in appropriate credentials. Most
imprtantly, DO NOT set permissions on xp_cmdshell itself.
"Joao Mossmann" <mossmann@.feevale.br> wrote in message
news:%234YPh1rLEHA.1556@.TK2MSFTNGP10.phx.gbl...
> Hi!!!
> which permissions are necessary to execute xp_cmdshell?
> -->without being SA
> Error:
> Msg 50001, Level 1, State 50001
> xpsql.cpp: Error 87 from GetProxyAccount on line 604
>
> tanks!!!
>
>sql

Permissions Management

Hello,
I am trying to set up permissions to secure databases in our Developement. I
have set up "View any Database" to Deny at the Database level.
when i login as admin i see all the database while the rest see Master and
Temp DB. What do i have to do at the database level so i can make other
databases visible to appropriate personnel and also give them control such as
Create Tables, Update SP etc.
Thanks
MannyManny
If the user is an owner of db try the following
Create a new SQL login "login1"
? Create a user named ?login1? in master database
? Grant CREATE DATABASE to login1
? While impersonating login1, create a database called ?dbteste?
? Revoke CREATE DATABASE permission from login1
? Revoke VIEW ANY DATABASE permission from PUBLIC
? Register this server as login1
? From the ?login1? session, expand database tree. Now, you should see
master, tempdb, dbteste
? Grant VIEW ANY DATABASE to PUBLIC
? From the ?login1? session, you should see all the databases
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny|||So basically the user who is creating the database will be only one who can
view the database in the tree.
What i am trying to do is following:
I have users User1, User2, User3 and Databases as DB1, DB2 and DB3.
I would like to revoke Public from seeing all the databases.
User 1 should have access to DB1 (as DBO) and DB2 as read/write
User 2 should only have access to DB2 (DBO)
User 3 should have access to DB2 (DBO) and DB3 as read only.
Hope i am clear in explaining things
THanks
Manny
"Uri Dimant" wrote:
> Manny
> If the user is an owner of db try the following
> Create a new SQL login "login1"
> â?¢ Create a user named â'login1â' in master database
> â?¢ Grant CREATE DATABASE to login1
> â?¢ While impersonating login1, create a database called â'dbtesteâ'
> â?¢ Revoke CREATE DATABASE permission from login1
> â?¢ Revoke VIEW ANY DATABASE permission from PUBLIC
> â?¢ Register this server as login1
> â?¢ From the â'login1â' session, expand database tree. Now, you should see
> master, tempdb, dbteste
> â?¢ Grant VIEW ANY DATABASE to PUBLIC
> â?¢ From the â'login1â' session, you should see all the databases
>
>
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> > Hello,
> >
> > I am trying to set up permissions to secure databases in our Developement.
> > I
> > have set up "View any Database" to Deny at the Database level.
> >
> > when i login as admin i see all the database while the rest see Master and
> > Temp DB. What do i have to do at the database level so i can make other
> > databases visible to appropriate personnel and also give them control such
> > as
> > Create Tables, Update SP etc.
> >
> > Thanks
> >
> > Manny
>
>|||Manny,
VIEW ANY DATABASE is a server level permission and cannot be granted nor
denied at the database level. To accomplish what you wish to do, you can
deny the permission to the public role (rather than to individual users) and
put users in the db_owner role of the database(s) they should have access.
This would then allow them to see only databases which they have ownership
of.
With regards to granting/revoking DDL permissions, I suggest you try out the
tool SQL CodeSecure. It allows you to set DDL permissions very easily, as
well as audit all changes modifications made to a database and even rollback
changes or recover deleted objects.
You can download the application from this location:
http://www.sql-labs.com/downloads/SQLCodeSecure.zip
HTH.
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny

permissions issue?

I am having a problem running this statement:
GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
I get this error:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'MyStoredProcedure', because it does not exist
or you do not have permission.
MyStoredProcedure definitely DOES exist in the database, and I'm
running the statement as a sysadmin user. I even tried "sa" just in
case. But no dice. This seems like a very straightforward matter.
If I use the interface, not command line, I am able to grant
permissions easily. But there's something it doesn't like about my
above statement. Any ideas appreciated!
Thanks
> If I use the interface, not command line, I am able to grant
> permissions easily.
Script the TSQL command submitted by the GUI and you will see the difference. Do you really have a
schema named MyDatabase in your database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegr oups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>
|||Here's the syntax, from Books Online:
GRANT <permission> ON
[ OBJECT :: ][ schema_name ]. object_name TO <database_principal>
So in your example:
schema_name = MyDatabase
object_name = MyStoredProcedure
database_principal = MyUser
Note that the name of the database should not be included, only the schema
name within the database.
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158610855.925197.188970@.i42g2000cwa.googlegr oups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>
|||
> Script the TSQL command submitted by the GUI and you will see the difference. Do you really have a
> schema named MyDatabase in your database?
No, of course not. But I don't like posting private company information
on the net :-)
[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegr oups.com...
|||Removing the db name worked, thanks
Kalen Delaney wrote:[vbcol=seagreen]
> Here's the syntax, from Books Online:
> GRANT <permission> ON
> [ OBJECT :: ][ schema_name ]. object_name TO <database_principal>
> So in your example:
> schema_name = MyDatabase
> object_name = MyStoredProcedure
> database_principal = MyUser
> Note that the name of the database should not be included, only the schema
> name within the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message
> news:1158610855.925197.188970@.i42g2000cwa.googlegr oups.com...
|||I think Tibor was actually asking the same question I was. Is whatever you
used instead of MyDatabase a database name or a schema name?
Our guess is that you were confusing the two. If you had used the schema
name, it would have worked.
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158614641.882490.40330@.m7g2000cwm.googlegrou ps.com...
>
> No, of course not. But I don't like posting private company information
> on the net :-)
>
|||>I think Tibor was actually asking the same question I was.
Indeed. Thanks for clarifying Kalen. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>I think Tibor was actually asking the same question I was. Is whatever you used instead of
>MyDatabase a database name or a schema name?
> Our guess is that you were confusing the two. If you had used the schema name, it would have
> worked.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message news:1158614641.882490.40330@.m7g2000cwm.googlegrou ps.com...
>
|||And it seems from his answer to me that his "MyDatabase" was a database
name, not a schema name.
As trainers, I can see we have our work cut out for us getting people to
understand this new concept!
Kalen Delaney, SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTyVtdA3GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Indeed. Thanks for clarifying Kalen. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>
|||On Tue, 19 Sep 2006 09:55:10 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:

>As trainers, I can see we have our work cut out for us getting people to
>understand this new concept!
Think of it as job security. 8-)
Roy

permissions issue?

I am having a problem running this statement:
GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
I get this error:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'MyStoredProcedure', because it does not exist
or you do not have permission.
MyStoredProcedure definitely DOES exist in the database, and I'm
running the statement as a sysadmin user. I even tried "sa" just in
case. But no dice. This seems like a very straightforward matter.
If I use the interface, not command line, I am able to grant
permissions easily. But there's something it doesn't like about my
above statement. Any ideas appreciated!
Thanks> If I use the interface, not command line, I am able to grant
> permissions easily.
Script the TSQL command submitted by the GUI and you will see the difference
. Do you really have a
schema named MyDatabase in your database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegroup
s.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>|||Here's the syntax, from Books Online:
GRANT <permission> ON
[ OBJECT :: ][ schema_name ]. object_name TO <database_principal
>
So in your example:
schema_name = MyDatabase
object_name = MyStoredProcedure
database_principal = MyUser
Note that the name of the database should not be included, only the schema
name within the database.
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>|||
> Script the TSQL command submitted by the GUI and you will see the differen
ce. Do you really have a
> schema named MyDatabase in your database?
No, of course not. But I don't like posting private company information
on the net :-)
[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2
000cwa.googlegroups.com...|||Removing the db name worked, thanks
Kalen Delaney wrote:[vbcol=seagreen]
> Here's the syntax, from Books Online:
> GRANT <permission> ON
> [ OBJECT :: ][ schema_name ]. object_name TO <database_pri
ncipal>
> So in your example:
> schema_name = MyDatabase
> object_name = MyStoredProcedure
> database_principal = MyUser
> Note that the name of the database should not be included, only the schema
> name within the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message
> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...|||I think Tibor was actually asking the same question I was. Is whatever you
used instead of MyDatabase a database name or a schema name?
Our guess is that you were confusing the two. If you had used the schema
name, it would have worked.
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158614641.882490.40330@.m7g2000cwm.googlegroups.com...
>
> No, of course not. But I don't like posting private company information
> on the net :-)
>
>|||>I think Tibor was actually asking the same question I was.
Indeed. Thanks for clarifying Kalen. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>I think Tibor was actually asking the same question I was. Is whatever you
used instead of
>MyDatabase a database name or a schema name?
> Our guess is that you were confusing the two. If you had used the schema n
ame, it would have
> worked.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message news:1158614641.882490.40330@.m7g200
0cwm.googlegroups.com...
>|||And it seems from his answer to me that his "MyDatabase" was a database
name, not a schema name.
As trainers, I can see we have our work cut out for us getting people to
understand this new concept!
Kalen Delaney, SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTyVtdA3GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Indeed. Thanks for clarifying Kalen. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>|||On Tue, 19 Sep 2006 09:55:10 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:

>As trainers, I can see we have our work cut out for us getting people to
>understand this new concept!
Think of it as job security. 8-)
Roy

permissions hierarchy

Hi,
With Yukon the permissions which are applicable to a securable can also
be applied at parent level. In case of database the permissions are
applicable to all schemas, all objects in that schemas. On the database
permissions tab there are two permissions 'Alter' & 'Alter any schema'. I
guess this is to allow just the 'alter' on schema but not ont all other
types e.g. assemblies. But then why not 'control any schema'
Thanks.
AjeyPlease post Yukon/SQL Server 2005 questions to the beta newsgroup for Yukon.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

I tried to create a sp on one of the databases on my lap top and got this: Pls help i need it bad

Msg 10314, Level 16, State 11, Procedure ap_Hello, Line 5

An error occurred in the Microsoft .NET Framework while trying to load

assembly id 65695. The server may be running out of resources, or the

assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or

UNSAFE. Run the query again, or check documentation to see how to solve

the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly

'vbtriggers, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or

one of its dependencies. An error relating to security occurred.

(Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException:

at System.Reflection.Assembly.nLoad(AssemblyName fileName, String

codeBase, Evidence assemblySecurity, Assembly locationHint,

StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean

forIntrospection)

at System.Reflection.Assembly.InternalLoad(AssemblyNa me assemblyRef,

Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean

forIntrospection)

at System.Reflection.Assembly.InternalLoad(String assemblyString,

Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean

forIntrospection)

at System.Reflection.Assembly.Load(String assemblyString)

The statement has been terminated.

Please post the CLR code here. Do you use reflection or an Assembly load function within the CLR code ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
-|||Ok what clr code?

I downloaded this db from a book website (not recomending the book at this time)

it was a rar file i unziped and ran a script to attach. I think it has to be on a network, im not sure

EXEC sp_attach_db 'Asset5',

C:\......Asset5.mdf',

C:\......Asset5.ldf'
Pls help asap i bought the book to cram for an interview and I cant tell you where i would like to cram the book now|||Please post the script you are using to create the ap_Hello SP here.|||Create proc ap_Hello
AS
Declare @.myVar char(5)
SET @.MyVar='Hello'
SELECT @.MyVar

Simple as can be, this works on all other dbs on this laptop except for the one installed from the book, im not sure but seems the book says you have to be running it on a server not locally can that be the prob?

Also i used the scripts for a few tables to recreate them in a new db on the same laptop then selected from the book db into my new db and ran the a few sp from the book and they worked, it is this Asset5 db only that i have trouble with|||

So you encounter the problem when creating the SP? It looks like that there is DDL trigger, named vbtriggers, defined in the database but failed to be loaded correctly.

Please open Management Studio and click Database -> Your database -> Programmablity -> Database Triggers, and see if there is any defined. If any, script them out so that you can re-create them if necessary and then delete all the database triggers.

After this, please try creating the SP again. What will be the result?

|||EXCELLENT!!!!!!!!!! the vbTrigger is an Assembly, i had to drop a trig that depended on it so i scripted that also, when i tried to create the assembly agian in go a huge error list, but i guess i have about a month of studying until i get there so no worries.

THANK YOU VERY MUCH!!!!!!!

Permission at the row level associated with user/login

Hi,
I have the following scenario:
I have a table X with 1000 rows. I want to allow select access to only 300
particular rows to a user/login while another user/login can see the other
700 (for example).
Is this granularity level possible in SQL Server 2005?
Thanks in advance,
Juan Dent, M.Sc.Juan Dent (juanjr@.nospam.nospam) writes:
> I have the following scenario:
> I have a table X with 1000 rows. I want to allow select access to only 300
> particular rows to a user/login while another user/login can see the other
> 700 (for example).
> Is this granularity level possible in SQL Server 2005?
Yes and no. In theory it is simple. You add a table that specifies which
keys that a certain user may see:
CREATE VIEW rowlevelsec_view AS
SELECT ...
FROM tbl t
JOIN accesscontrol c ON t.keycol = c.keycol
WHERE c.userid = SYSTEM_USER
You grant users access on the view, but not on the table. Users can then
only see the rows they are entitled to.
However, it is possible for crafty users to wrestle out information from
the view that they are not permitted to see. It's not that they can read
the rows, but they can infer things from query plans and error messages.
It's not really trivial, but this could matter if the data is very
sensitive.
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|||Thnaks, but I was thinking something perhaps new to SQLServer 2005 and at th
e
Transact-SQL level or the like, you know something declarative and part of
the language - not a construction.
Anyone?
Thanks in advance,
Juan Dent, M.Sc.
"Erland Sommarskog" wrote:

> Juan Dent (juanjr@.nospam.nospam) writes:
> Yes and no. In theory it is simple. You add a table that specifies which
> keys that a certain user may see:
> CREATE VIEW rowlevelsec_view AS
> SELECT ...
> FROM tbl t
> JOIN accesscontrol c ON t.keycol = c.keycol
> WHERE c.userid = SYSTEM_USER
> You grant users access on the view, but not on the table. Users can then
> only see the rows they are entitled to.
> However, it is possible for crafty users to wrestle out information from
> the view that they are not permitted to see. It's not that they can read
> the rows, but they can infer things from query plans and error messages.
> It's not really trivial, but this could matter if the data is very
> sensitive.
>
>
> --
> 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
>|||Juan Dent (juanjr@.nospam.nospam) writes:
> Thnaks, but I was thinking something perhaps new to SQLServer 2005 and
> at the Transact-SQL level or the like, you know something declarative
> and part of the language - not a construction.
I'm afraid that what I presented is what SQL 2005 offers.
See also this white-paper on the topic:
http://www.microsoft.com/technet/pr.../multisec.mspx-
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|||Juan
http://vyaskn.tripod.com/ row_level...as
es.htm
"Juan Dent" <juanjr@.nospam.nospam> wrote in message
news:6D23828E-4AD9-4456-9E6E-B3836C1E0099@.microsoft.com...
> Hi,
> I have the following scenario:
> I have a table X with 1000 rows. I want to allow select access to only 300
> particular rows to a user/login while another user/login can see the other
> 700 (for example).
> Is this granularity level possible in SQL Server 2005?
> --
> Thanks in advance,
> Juan Dent, M.Sc.