How to check if I have VIEW DEFINITION?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  June 28, 2012 at 7:24 am

  #258599

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Andrew Kernodle

  SSCertifiable

  Points: 5314

  More actions

  June 28, 2012 at 7:32 am

  #1506604

  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!

  -

• Lowell

  SSC Guru

  Points: 323522

  More actions

  June 28, 2012 at 7:34 am

  #1506605

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323522

  More actions

  June 28, 2012 at 8:04 am

  #1506635

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!