|
Home > Archive > Microsoft SQL Server forum > February 2006 > Re: [SQL2000] permissions to use view based on tables from many databases
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [SQL2000] permissions to use view based on tables from many databases
|
|
| Andrew J. Kelly 2006-02-21, 7:23 am |
| 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__po
czta.onet.pl> wrote in message
news:dteimk$1b7$1@in
ews.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.
| |
| Grzegorz Danowski 2006-02-21, 9:23 am |
| 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" < sqlmvpnooospam@shadh
awk.com> napisał w
wiadomości news:%23Iia6cuNGHA.2336@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
| |
| Andrew J. Kelly 2006-02-21, 9:23 am |
| 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__po
czta.onet.pl> wrote in message
news:dtf589$5h3$1@in
ews.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" < sqlmvpnooospam@shadh
awk.com> napisał w
> wiadomości news:%23Iia6cuNGHA.2336@TK2MSFTNGP12.phx.gbl...
>
| |
| Grzegorz Danowski 2006-02-21, 9:23 am |
| Thanks, it works.
Grzegorz
Użytkownik "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> napisał w
wiadomości news:O07NN2uNGHA.2064@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__po
czta.onet.pl> wrote in message
> news:dtf589$5h3$1@in
ews.gazeta.pl...
>
>
|
|
|
|
|