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.public.sqlserver.security, but I had no
answer. 5 2181
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@serwerze__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.public.sqlserver.security, 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****************@TK2MSFTNGP12.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@serwerze__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****************@TK2MSFTNGP12.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**************@TK2MSFTNGP09.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@serwerze__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****************@TK2MSFTNGP12.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.public.sqlserver.security, 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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |