June 28, 2012 at 7:24 am
assuming someone gave you a database user, when you are logged in, how can that user determine if you have VIEW DEFINITION rights?
For example, supposed a login that is supposed to validate a schema agaisnt another source...i want to check that i have the assumed VIEW DEFINITION rights,a nd log it.
I had kind of assumed that the function fn_my_permissions would show that, but it doesn't.
here's a perfect example;
CREATE ROLE [DailyPlanet]
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [DailyPlanet]
CREATE USER [ClarkKent] WITHOUT LOGIN
EXEC sp_addrolemember N'DailyPlanet', N'ClarkKent'
EXECUTE AS USER= 'ClarkKent'
SELECT USER_NAME() --I'm Clark Kent
SELECT * FROM fn_my_permissions(NULL,'DATABASE')
SELECT * FROM sys.tables --lots of tables, so i must have view definition on *something*
-- i was really expecting to see something refering to my ability to *view definition* here!
/*
databaseCONNECT
*/
--cleanup
REVERT;
DROP USER [ClarkKent]
DROP ROLE [DailyPlanet]
Lowell
June 28, 2012 at 7:32 am
I might be completely wrong here, but here's a shot in the dark!
Try:
USE [DatabaseName]
GO
sp_helprotect
This seems to return a list of all logins for the database in question that have been granted the VIEW DEFINITION permission, if I'm reading this article correctly:
http://www.mssqltips.com/sqlservertip/1593/granting-view-definition-permission-to-a-user-or-role-in-sql-server/"> http://www.mssqltips.com/sqlservertip/1593/granting-view-definition-permission-to-a-user-or-role-in-sql-server/
Hope that helps!
- 😀
June 28, 2012 at 7:34 am
ok a follow up to my own question:
in SQL2005,
SELECT * FROM fn_my_permissions(NULL,'DATABASE') for my example above only shows connect permission, but on SQL2008 and above, it shows the "VIEW DATABASE STATE" i was expecting:
SELECT * FROM fn_my_permissions(NULL,'DATABASE')
databaseCONNECT
databaseVIEW DATABASE STATE
so now my question is really leaning towards cross version compatibility: how will my user know, in 2005 or above, if I have VIEW DEFINITION?
Lowell
June 28, 2012 at 8:04 am
i'm getting closer:
this query shows me my permissions, as well as the permissions i directly inherit from roles i've been assigned;
it does not handle any recursive role-within-a-role yet;
i'm thinking that's going to require a recursive CTE:
SELECT
*
FROM sys.database_permissions permz
INNER JOIN sys.database_principals userz
ON permz.grantee_principal_id = userz.principal_id
WHERE userz.principal_id IN( --the userid and also all the roles i'm directly in.
--that doesn't seem to get the role-within-a-role info those
SELECT
USER_ID() AS id
UNION ALL
SELECT
rolezx.role_principal_id
FROM sys.database_principals userzx
LEFT OUTER JOIN sys.database_role_members rolezx
ON userzx.principal_id = rolezx.member_principal_id
LEFT OUTER JOIN sys.database_principals decripz
ON rolezx.role_principal_id = decripz.principal_id
WHERE userzx.name = USER_NAME())
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply