November 15, 2007 at 2:13 am
Hi,
I would like to do a link between system table. My goal is to link the security’s tables with the session’s tables, To see the security for the current user. For example I would to do a request select * from sys. sys.dm_exec_connections inner join….sys.database_permissions to see if this user has the privilège for doing a "select"
Therefore how can I do a link between sys.dm_exec_connections (Current session @@spid) and sys.database_permissions(define all permissions on db) or perhaps other to see what I want.
Thank a lot for your response…
November 15, 2007 at 3:18 am
If you want to return the permissions for the current connection, you don't need to do that. Something as simple as this will work:
SELECT *
FROM sys.database_permissions
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID()
K. Brian Kelley
@kbriankelley
November 15, 2007 at 6:12 am
Thank a lot for your response. Sometimes, I have some users who are in several groups in the Active directory ....
With this mothode I see only main group
Thank in advance
November 15, 2007 at 7:57 am
Some info
IF PERMISSIONS()&2=2
CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.'
--------
This example determines whether the current user can
grant the INSERT permission on the authors table to another user.
IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
PRINT 'INSERT on authors is grantable.'
ELSE
PRINT 'You may not GRANT INSERT permissions on authors.'
------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsql...>
How can I retrieve a list of objects and permissions for a specified role?
------------------------------------------------------------------------------
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
This is an example of usage:
SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o
November 15, 2007 at 8:41 am
If you're looking for a complete list, try something like this, otherwise, the function you cited works well for individual securables:
SELECT dperms.*
FROM sys.database_permissions dperms
JOIN sys.database_principals dprins
ON dperms.grantee_principal_id = dprins.principal_id
JOIN master.sys.server_principals sprins
ON dprins.sid = sprins.sid
Notice I didn't specify a WHERE clause. You'll need to include it in order to filter the results accordingly.
K. Brian Kelley
@kbriankelley
November 15, 2007 at 11:29 pm
Excellent you are a real guru.......
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply