Showing posts with label retrieve. Show all posts
Showing posts with label retrieve. Show all posts

Friday, March 9, 2012

Permissioned users... and permissioned stored proceudres

anyone know how to query system tables to retrieve a list of users that are permissioned for a stored procedure?

and vice versa... a list of stored procedure a user has permission to execute?

i searched for it online and came up with nothing... can someone please help. thanks, all.

Below is a statement to get somewhat of an idea of what you are looking for. When the user is setup to have databse level access it does not list the procedures out one by one of what they have access to, because they have access to everything. However if the user is granted access only to specific objects or columns it tells you what they do have permissions to.

select class_desc,prin.[name],obj.[name]

from sys.database_permissions p

left join sys.database_principals prin

on p.grantee_principal_id = prin.principal_id

left join sys.objects obj

on p.major_id = obj.object_id

If this doesn't totally answer your question at least it might help you get an idea of how you can get the information you are looking for.

Let me know if I can be of any further assistance.

Saturday, February 25, 2012

Permission on view. Is there way to avoid granting persmission on the underlying table?

I want to grant access on the below view for an end user so that he connect to our SQL server and retrieve data. The view looks like the below

CREATE VIEW DB1.[dbo].[View1]
AS

-- For brevity, I made it as simple statement.
SELECT *

From DB2.dbo.table2

GO

For the above view, it looks like I have to grant select and connect permission for the DB1. [dbo].[View1] as well as DB2.dbo.table2.

1. Is my understanding correct?

2. I want the user to access only DB1. [dbo].[View1] and not the underlying tables. Is there a way to grant access only on the view and execute the statement on a different security context so that the user can’t access DB2.dbo.table2 directly?

3. When the user uses SQL Server Management Studio to connect to SQL server, he is able to connect and select DB2.dbo.table2 directly. Is there any way to restrict user from viewing and executing select statement on DB2 database from SQL Server Management Studio

Thanks in advance for your help

With regards

Ganesh

Because you are crossing databases, regular ownership chaining doesn’t apply. While it is possible to use cross-database ownership chaining (CDOC) for this scenario, I strongly discourage using it as enabling CDOC also enables some escalation of privileges opportunities.

I wrote an article in my blog that describes how to use digital signatures as an alternative, and it probably will be useful in your scenario:http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

NOTE: It is not possible to sign views directly, but it is possible to sign SP and multi-statement user-defined functions.

Also notice that using digital signatures it is possible to grant access to the target DB (DB2) via the signature without granting explicit CONNEC permission to the database on any other principal.

For the last part of your question; it is not possible to restrict actions based on the application, we have a separate discussion on this topic in the following link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=790497&SiteID=1

I hope this information helps, let us know if you have any additional questions .

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||Thanks a lot for your help. Does the digital signature concept applicable to view also?|||

No, unfortunately views cannot be signed. An alternative is to create a multistatement table-valued function (See BOL for details: http://msdn2.microsoft.com/en-us/library/ms186755.aspx) instead of a view, and you can insert into the return value of the view the same content the view would return.

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine