CODEWITHSUNDEEP
sql-server
TGrey
TGrey

Reputation: 33

SQL Server Management Studio, Beginner

So, I have this question: Use the following query to determine the principle_id values for suzie and jimmy.

 select *
 from sys.database_principals
 where name in ('suzie','jimmy')

Using the principle_id values obtained from the query above; write a SELECT query using the sys.database_permissions table that lists ALL permissions that have been granted to suzie and jimmy.

Principal id's I got were "5 & 6"

Then I used:

SELECT*
FROM sys.database_permissions
WHERE grantee_principal_id in ('5','6')

Now the question is we're asked now to use the OBJECT_NAME function in your query to show the view names instead of just their major_id values. I don't quite understand how to use the OBJECT_NAME function, I have been playing around and can't get it. Any hints or help would be greatly appreciated. Thanks!

Upvotes: 0

Views: 40

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Are you looking for something like this?

grator_principal_id will return all objects where these principal gave the grant and grantee_Prinicipal_id return all objects where Suzie and Jimmy were granted Details here

SELECT *
     ,OBJECT_NAME(major_id) AS TheObject
FROM sys.database_permissions
WHERE grantor_principal_id in (select principal_id
                               from sys.database_principals
                               where name in ('suzie','jimmy'))

Upvotes: 2

Related Questions