Solved

DB level permissions

Posted on 2010-09-16
6
316 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:anushahanna
  • 3
  • 3
6 Comments
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 33696030
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
 
LVL 6

Author Comment

by:anushahanna
ID: 33710094
so if a sysadmin runs it, should he/she get 105 rows back for the second query?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 33710097
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anushahanna
ID: 33728775
OK.

doesn't a sysadmin 'implicitly' have permission on 'all' that is possible in SQL Server?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 33731340
Yes, that is correct.  There is nothing that a sysadmin cannot do on a SQL Server.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33749901
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 42
SQL Help 27 45
SQL Server - Set Value of Multiple Fields in One Query 10 25
Need help constructing a conditional update query 16 47
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question