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/
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy