By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,900 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,900 IT Pros & Developers. It's quick & easy.

[SQL2000] permissions to use view based on tables from many databases

P: n/a
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.

Feb 21 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

Feb 21 '06 #2

P: n/a
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


Feb 21 '06 #3

P: n/a
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

Feb 21 '06 #4

P: n/a
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



Feb 21 '06 #5

P: n/a
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.

Feb 28 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.