DB level permissions

are these 2 talking about the same permissions?

sp_dbfixedrolepermission -- brings back 105 records
SELECT * FROM fn_my_permissions (NULL, 'DATABASE') -- brings back 61 records
LVL 6
anushahannaAsked:
Who is Participating?
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
No they are not the same.  The first one simply tells you what permissions each fixed db role has given to it.

The second is more contextual to you and what you have permissions on for that database.
0
 
anushahannaAuthor Commented:
so if a sysadmin runs it, should he/she get 105 rows back for the second query?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
No, because it will just show what he/she has permissions on and does not correspond to the db fixed roles.  So the results will vary.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
anushahannaAuthor Commented:
OK.

doesn't a sysadmin 'implicitly' have permission on 'all' that is possible in SQL Server?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Yes, that is correct.  There is nothing that a sysadmin cannot do on a SQL Server.
0
 
anushahannaAuthor Commented:
OK. but sorry, I am not getting it, in that reason, should not the sysadmin have all the 105 permissions mentioned in dbfixedrolepermission sp?

thanks dbaduck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.