Hi
I have two databases: Customers and Operations. In Customers database I have
made a view based on a few tables from both Customers and Operations (left
join - customers without any operations). In the same database (Customers) I
have created a stored procedure based on the view. Finally I'd like to give
to some users permission only to exec the stored procedure.
Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no rights
to open tables or view from Customers).
Regards,
Grzegorz
Ps. I had sent the post on microsoft.publi c.sqlserver.sec urity, but I had no
answer.
5  2198 
By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.
--
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@serwer ze__poczta.onet .pl> wrote in message
news:dt******** **@inews.gazeta .pl... Hi
I have two databases: Customers and Operations. In Customers database I
have made a view based on a few tables from both Customers and Operations
(left join - customers without any operations). In the same database
(Customers) I have created a stored procedure based on the view. Finally
I'd like to give to some users permission only to exec the stored
procedure.
Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no
rights to open tables or view from Customers).
Regards,
Grzegorz
Ps. I had sent the post on microsoft.publi c.sqlserver.sec urity, but I had
no answer.
Well, it works fine in situations when all tables are in the same database,
but it doesn't work when tables are in two databases. If user have no rights
to read source table from other database SQL Server shows error:
"SELECT permission denied on object 'CustomersData' , database 'Customers',
owner 'dbo'."
Grzegorz
Użytkownik "Andrew J. Kelly" <sq************ @shadhawk.com> napisał w
wiadomości news:%2******** ********@TK2MSF TNGP12.phx.gbl. .. By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.
--
Andrew J. Kelly SQL MVP
Are the objects owned by the same owner in both db's? If so you may have to
specify rights on the other tables. Is Cross database Ownership chaining
turned on? http://support.microsoft.com/?kbid=810474
--
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@serwer ze__poczta.onet .pl> wrote in message
news:dt******** **@inews.gazeta .pl... Well, it works fine in situations when all tables are in the same
database, but it doesn't work when tables are in two databases. If user
have no rights to read source table from other database SQL Server shows
error:
"SELECT permission denied on object 'CustomersData' , database 'Customers',
owner 'dbo'."
Grzegorz
Użytkownik "Andrew J. Kelly" <sq************ @shadhawk.com> napisał w
wiadomości news:%2******** ********@TK2MSF TNGP12.phx.gbl. .. By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.
--
Andrew J. Kelly SQL MVP
Thanks, it works.
Grzegorz
Użytkownik "Andrew J. Kelly" <sq************ @shadhawk.com> napisał w
wiadomości news:O0******** ******@TK2MSFTN GP09.phx.gbl... Are the objects owned by the same owner in both db's? If so you may have
to specify rights on the other tables. Is Cross database Ownership
chaining turned on?
http://support.microsoft.com/?kbid=810474
--
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@serwer ze__poczta.onet .pl> wrote in message
news:dt******** **@inews.gazeta .pl... Well, it works fine in situations when all tables are in the same
database, but it doesn't work when tables are in two databases. If user
have no rights to read source table from other database SQL Server shows
error:
"SELECT permission denied on object 'CustomersData' , database
'Customers', owner 'dbo'."
Grzegorz
Użytkownik "Andrew J. Kelly" <sq************ @shadhawk.com> napisał w
wiadomości news:%2******** ********@TK2MSF TNGP12.phx.gbl. .. By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.
--
Andrew J. Kelly SQL MVP
It is a in SQL that permission is check on the first called element i.e.
in case of stored procedure the there's a check made if the user has
the right to execute a procedure. Thus it's possiblie to allow a user to
execure a procedure and e.g. display a subset of data without allowing
to read tables content. Up to sp3 it was valid for the whole server.
From sp3 you've got to set cross-databse chaining explicitly.
Grzegorz Danowski wrote: Hi
I have two databases: Customers and Operations. In Customers database I
have made a view based on a few tables from both Customers and
Operations (left join - customers without any operations). In the same
database (Customers) I have created a stored procedure based on the
view. Finally I'd like to give to some users permission only to exec the
stored procedure.
Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no
rights to open tables or view from Customers).
Regards,
Grzegorz
Ps. I had sent the post on microsoft.publi c.sqlserver.sec urity, but I
had no answer. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: sci |
last post by:
Could someone help me by answering the questions below?
What's a cursor?
What's difference between Query and View?
Is a RecordSet just part of a table? Can it be part of a query of view?
If the content in a table changed, is it necessary for a old recordset to
renew itself by do "Requery()"?
Thanks for your help!
|
by: LizP |
last post by:
Hi, guys - looking for a bit of help/advice on moving a database from
SQL 7 to SQL 2000.
We've a third party application which currently uses a SQL7 database.
This database has a number of views which are owned by user
INFORMATION_SCHEMA and are set as system tables. This user doesn't
actually have a login on the SQL server.
We've tried the following to recreate the database on SQL2000, but
whatever we try, the views owned by...
|
by: Ezekiël |
last post by:
Hello,
I need some help with implenting the following:
I recently migrated from access to sql server and i now i want to use
maintainable permissions on my tables, views, etc. The access database will
serve as a front-end.
I've created for testing purposes an testaccount with only a public role to
access to my database.
|
by: ralphm1 |
last post by:
Hello,
I am running SQL Server 2000 standard in mixed mode security and have
two problems.
1.) I created a database as sa and assigned a login as db_owner,
however, the design view is grayed out for all tables. All tables are
owned by dbo and no user defined role exists for the database.
2.) Any login other than sa will time-out in Enterprise Manager or take
|
by: Robert Stearns |
last post by:
Can I construct a restricted view and grant select access on it to a
user without granting select access to the underlying tables and views?
This seems to be an easy way to restrict a user to just 'his part' of
the database.
| | |
by: Lyle Fairfield |
last post by:
I have developed ADPs now for three years, in Ac2K and AcXP. I have sold
two ADP applications for > $30,000 USD, one to a large company in Atlanta,
and one to a local (Ontario, Canada) school board, and numerous smaller
ones.
Initially my applications have started as accessing MS-SQL Server over the
internet. For development purposes I use MSDE, or rent an SQL Server DB
from my site provider for $15 USD monthly. The "school board"...
|
by: Lyle Fairfield |
last post by:
There is an MS-SQL table named Bugs_Comments_and_Suggestions.
There is a form named Bugs_Comments_and_Suggestions.
To allow John Doe to use this form, we GRANT him LOGIN and ACCESS permissions
to the db and SELECT permissions on the stored procedure which is the record
source for the BOUND form.
To allow John Doe to "UPDATE, INSERT, DELETE" using the BOUND form, we GRANT
John Doe "UPDATE, INSERT, DELETE" permissions on the table,
|
by: Dizzzz |
last post by:
I am creating a front end that will access a SQL database and I want
to create a DROP DOWN control that will display the databases and
tables available from a SQL2000 server. Much like the one in the
Import/Export menus in SQL2000 Enterprise Manager.
Does anyone know the class in C# to do that? I can connect fine, I
just want to display the tables and db's.
If I am asking for the wrong thing, by all means, straighten me out
and put...
|
by: jc |
last post by:
Hello, a question for the MS Access community, from someone who
programs with SQL in SQL2000. I currently create a table with varying
column names and data within SQL2000. I then need to reproduce the
same in a Access file DB.
The normal functions available with SQL2000 such as OpenRowSet are
good for Insert, Select etc., but not the simple "DDL" task of
creating a table.
Can another help/suggest ?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
