Pages

Sunday 5 August 2012

SQL Server : List Login's Database and Server Level Role Permission

In my last post, we have discussed about the orphaned users and fixing the orphaned users. The next step in the security audit of our server was to review the login's database role membership and server role membership. 

There is a system stored procedure sp_helpsrvrolemember to list server role membership of login, but it is difficult to review the result of this procedure . Below script will give us the server role membership in more readable format.By copying the the result to Excel, it is more easy to review the server role membership of logins. More over system store procedure will not list the logins which have control server permission.

WITH CTE_Role (name,role,type_desc)
AS
(SELECT PRN.name,
srvrole.name AS [role]
Prn.Type_Desc 
FROM sys.server_role_members membership 
INNER JOIN (SELECT * FROM sys.server_principals  WHERE type_desc='SERVER_ROLE') srvrole 
ON srvrole.Principal_id= membership.Role_principal_id 
RIGHT JOIN sys.server_principals  PRN 
ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0

UNION ALL


SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p
  
JOIN sys.server_permissions Sp
  
ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
  
AND sp.[type] = 'CL'
  
AND state = 'G' )

SELECT 
name,
Type_Desc ,
CASE WHEN [public]=1 THEN 'Y' ELSE 'N' END AS 'Public',
CASE WHEN [sysadmin] =1 THEN 'Y' ELSE 'N' END AS 'SysAdmin' ,
CASE WHEN [securityadmin] =1 THEN 'Y' ELSE 'N' END AS 'SecurityAdmin',
CASE WHEN [serveradmin] =1 THEN 'Y' ELSE 'N' END AS 'ServerAdmin',
CASE WHEN [setupadmin] =1 THEN 'Y' ELSE 'N' END AS 'SetupAdmin',
CASE WHEN [processadmin] =1 THEN 'Y' ELSE 'N' END AS 'ProcessAdmin',
CASE WHEN [diskadmin] =1 THEN 'Y' ELSE 'N' END AS 'DiskAdmin',
CASE WHEN [dbcreator] =1 THEN 'Y' ELSE 'N' END AS 'DBCreator',
CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE 'N' END AS 'BulkAdmin' ,
CASE WHEN [ControlServer] =1 THEN 'Y' ELSE 'N' END AS 'ControlServer' 
FROM CTE_Role
PIVOT
(
COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer])
)
AS pvtWHERE Type_Desc NOT IN ('SERVER_ROLE')ORDER BY name,type_desc



The system stored procedure sp_helprolemember  will help us to list the login's database role membership (system defined and user defined database role membership),but this will work only on database level. To get membership across all databases in an instance, this procedure has to run in each databases. The below script will list all users in all  databases and its database role membership.


SET NOCOUNT ON

CREATE TABLE
#DatabaseRoleMemberShip
  
(
        
Username VARCHAR(100),
        
Rolename VARCHAR(100),
        
Databasename VARCHAR(100)
        
    )
DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000)           SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip
select u.name,r.name,''?'' from sys.database_role_members RM inner join
sys.database_principals U on U.principal_id=RM.member_principal_id
inner join sys.database_principals R on R.principal_id=RM.role_principal_id
where u.type<>''R'''
EXEC sp_MSforeachdb @command1=@cmd

SELECT  @PivotColumnHeaders =                        
  
COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']'                     
  
)                     
  
FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename  ASC


SET
@Cmd = 'select
databasename,username,'
+@PivotColumnHeaders+'
from
(
select   * from #DatabaseRoleMemberShip) as p
pivot
(
count(rolename  )
for rolename in ('
+@PivotColumnHeaders+') )as pvt'EXECUTE(@Cmd )        DROP TABLE #DatabaseRoleMemberShip 



The same scripts are available Server and Database Role Membership.sql



If you liked this post, do like my page on FaceBook





128 comments:

  1. The second SQL is neat. However, it shows dbroles for disabled logins as well. Can we elimiate disabled users?

    ReplyDelete
  2. Nice CTE! However ... everyone is member of the PUBLIC role and membership cannot be revoked:

    Msg 15081, Level 16, State 1, Procedure sp_dropsrvrolemember, Line 34 - Membership of the public role cannot be changed.

    You might consider removing the PUBIC role from your query. It is incorrectly never reported on, which is detrimental to the reliability of the outcome of your query. Commenting out the first CASE statement will suffice.

    ReplyDelete
  3. Thanks for sharing this valuable information about sql server database.

    ReplyDelete
  4. If you are Packers and Movers gives you stress free relocation services provide transport and moving services
    Packers and Movers Delhi
    Packers and Movers Noida
    Packers and Movers Faridabad
    Packers and Movers Ghaziabad

    ReplyDelete
  5. All Packers and Movers India relocation services provided this company services are available pricrs for Top3th.
    Packers and Movers in Gurgaon
    Packers and Movers in Thane
    Packers and Movers in Navi Mumbai

    ReplyDelete
  6. Get a wide range of packing and moving companies at helloenquiry.in. We endeavour to furnish the best packers and movers companies in Kanpur at ease of Internet. Packers and Movers Kanpur

    ReplyDelete
  7. Hi

    I would appreciate if someone could send me a link where I can download a copy of sp_Msforeachdb.

    Thanks

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Considering over the reasons of this monster fan taking after that WordPress has ended up being constantly; we go over some stunning segments and inspirations driving interest that are select just to WordPress. On the off chance that you are an administrator and wish to have a strong website page that web crawlers love, then PSD to WordPress change is the decision you ought to make! Need to analyze more about the supernatural occurrences WordPress can do to your business site?


    PSD to Wordpress Conversion
    PDF to Wordpress Conversion
    PNG to Wordpress Conversion
    JPG to Wordpress Conversion

    ReplyDelete
  11. Get government jobs latest exam date and result at FindSarkariJobs.
    Ibps Exam Date 2017

    ReplyDelete
  12. You want the best spa to massage your body. But you are not able to find the cheapest and good spa.That is dealkare, here you will get a list of spas and there price. You can choose according to your budget.
    Body Massage in Noida
    Body to Body Massage in Gurgaon
    Body Massage in Gurgaon

    ReplyDelete
  13. If you looking for Home and Vehicle Moving Services in Gurugram......

    Packers and Movers in Gurugram

    ReplyDelete
  14. Plus each of the field names must be 100% unique. You cannot have two field names with the same name or again you will have a list that will not work.
    create mysql dashboard

    ReplyDelete
  15. Much obliged to you again for all the information you distribute,Good post. I was extremely keen on the article, it's entirely motivating I ought to concede.
    Digital Marketing Institute in Delhi
    Digital Marketing Course in Delhi
    Digital Marketing Course in Laxmi Nagar
    Digital Marketing Institute in Laxmi Nagar

    ReplyDelete
  16. Packers and Movers Hyderabad for 100% Affordable and Professional Packers and Movers in Hyderabad Compare Charges of Movers and Packers, Household Shifting Services at.
    Packers And Movers Hyderabad

    ReplyDelete
  17. it. For cookie baking, I prefer insulated cookie sheets to prevent undersides from over browning. movers

    ReplyDelete
  18. Transporting of personal goods or commercial goods should always done in the most cost effective manner. We would recommend you to consider our “Instant Call” services to compare quotes and shortlist your provider.

    READ MORE
    Packers and Movers in Bangalore
    Packers and Movers in Pune
    Packers and Movers in Mumbai
    Packers and Movers in Hyderabad
    Packers and Movers in Delhi
    packers and movers in panchkula
    Packers and Movers in Kolkata
    Packers and Movers in Chennai

    ReplyDelete
  19. Packing and unpacking is part and parcel of moving goods. Through our vendors you could get professional packing for the wide range of items for your home and office.
    packers and movers kharagpur
    packers & movers in nagpur



    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Hello,How are you all .Very good feel after read this .Really amazing info.Learn how you can also do it by joining one of the digital marketing course in delhi .This one is very good career option for all the youth who have a lot of creativity but they did not get the platform to show that. I will provide a right career opportunity to all of them.This is the motive of
    kodereytechstack
    Happy reading :)

    ReplyDelete
  23. Very Interesting and wonderful information keep sharing this post kindly check
    amazon prime video anime

    ReplyDelete
  24. QuickBooks endeavor bolster has a group of very much qualified experts who are good to go to determine any of your inquiries, with QuickBooks undertaking. Through QuickBooks Enterprise Phone Number these specialized specialists stay in touch, while giving master conclusion, relating QuickBooks undertaking. The QuickBooks Customer Support 1888-557-6950 has possessed the capacity to take care of the issues of clients in brisk time.

    Quickbooks Enterprise Support Number
    Quickbooks Enterprise Support Phone Number
    Quickbooks Enterprise Tech Support Number
    Quickbooks Enterprise Technical Support Number

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. Hiring packers and movers in Gurgaon is not too hard if you take help of the best moving professionals. Best movers and packers Gurgaon assist with their skilled and trained team to pack, load, unload and unpack your belongings.
    So be aware and hire the best mover packer to save your valuables.
    packers and movers Gurgaon
    packers and movers Gurgaon Charges

    ReplyDelete
  27. Such a nice article. I appreciate it.
    Mobile marketing has been very much in trend and also the need of any marketer. Got to learn many new things out of this guide. Thanks

    Digital Marketing Course in Delhi
    English speaking institute Course in Noida

    ReplyDelete
  28. Here we are offering Blockchain Training through which your future will be bright . So come and join this Institute. For more informatiion click on the link.
    Blockchain Certification Training in Delhi.

    ReplyDelete


  29. interior designer in noida
    Sense Interiors - India's leading & most trusted interior design firm since last 2 decades. Employ our top-notch services now & say goodbye to all your problems! Highly proficient in providing the best turnkey solutions, interior designing & architectural services all across NCR & pan India; Sense Interiors is a name you can trust. Explore our website for more or give us a call now to see how we can help!
    https://www.senseinteriors.in/interior-designing-in-noida/

    ReplyDelete

  30. construction-company-in-faridabad
    Sense Projects - offering a striking fusion of luxury & glory since the last 2 decades Having high competence in providing top-grade construction services, interiors execution, fire fighting, collaboration services & a lot more in faridabad. Go through our website or give us a call now for more information & assistance!
    https://www.senseprojects.in/construction-company-in-faridabad/

    ReplyDelete
  31. great post. Thanks for sharing this information.

    Salesforce Development India

    ReplyDelete
  32. https://www.databaseserviceprovider.com/car-owners-database/

    ReplyDelete
  33. Great
    we love your skills,
    keep sharing!

    ReplyDelete
  34. Do you hesitate while speaking in English? Then join English Guru for the best English speaking classes in Noida to improve your speaking skills and speak fluently.
    English Spoken Training in Noida
    English Spoken coaching in Noida
    English Spoken institute in Noida
    English Spoken classes in Noida

    ReplyDelete
  35. It is very interesting and informative. Such a nice post. Are you looking for Best Python training in Greater Noida? Mirorsoft Technologies offering the best Python Courses in Greater Noida.

    Python Training in Greater Noida

    ReplyDelete
  36. this is an interesting sites and i like this sites, thank you sharing knowledge with us.
    https://www.databaseprovidersonline.com/mumbai-mobile-number-database/

    ReplyDelete
  37. It is very pleasant to read your article. I am attached since long time in this virtual world. I think it will be very helpful to others.more information click here

    ReplyDelete
  38. Thank you so much for share such a wonderful information and ideas.The author clearly describe all the parts of the article

    ReplyDelete
  39. Great blog, learned many things. Thanks for sharing this useful information. I hope you will share some more content. Please keep sharing!
    Database Marketing
    Business Email Lists
    Consumer Email Lists
    USA Phone Number List
    United States Phone Number

    ReplyDelete
  40. Thanks for sharing such helpful information with all of us I appreciate your effort of writing a value able piece of content.

    Mobile Repairing Course in Delhi
    Mobile Repairing Institute Laxmi Nagar

    ReplyDelete
  41. I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
    Network Security Projects for CSE

    JavaScript Training in Chennai

    Project Centers in Chennai

    JavaScript Training in Chennai

    ReplyDelete
  42. This is very nice post . please keep continue. Basil Badwan is a motivational speaker . he always try to give best possible result to their customer.


    Basil badwan
    Basil badwan

    ReplyDelete
  43. This is very nice post . please keep continue. Basil Badwan is a motivational speaker . he always try to give best possible result to their customer.


    Basil badwan

    ReplyDelete
  44. This is very nice post . please keep continue. Basil Badwan is a motivational speaker . he always try to give best possible result to their customer.

    <a href=" https://www.facebook.com/public/Basil-Badwan”> Basil badwan </a>

    ReplyDelete
  45. Yogesh Gaur is the famous digital marketing consultant based in New Delhi. I usually gives digital marketing techniques in order to rank higher in search engines.I write blogs on social media marketing,email marketing,search engine optimization,content marketing and much more.I usually give tips and tricks to money online.

    High pr ping submission sites
    Free classified website list without registration
    classified websites india
    Slide sharing sites
    Local business listing sites usa

    ReplyDelete
  46. Anson Sports is the established brand in the field of gym equipment manufactureres in india.We provide wide range of gym equipments liks treadmill, dumbell,weights,gym clothing and much more.To know more about us visit at.

    Buy dumbbell online in india
    Buy exercise bikes in india
    Gym equipments manufacturers in delhi
    Gym equipments manufacturers in mumbai

    ReplyDelete
  47. This comment has been removed by the author.

    ReplyDelete
  48. This comment has been removed by the author.

    ReplyDelete
  49. Thanks for sharing this marvelous post. I like your content. Really it was so helpful article. Fashion bloggers in India

    ReplyDelete
  50. Seo company in Varanasi, India : Best SEO Companies in Varanasi, India: Hire Kashi Digital Agency, best SEO Agency in varanasi, india, who Can Boost Your SEO Ranking, guaranteed SEO Services; Free SEO Analysis.

    Best Website Designing company in Varanasi, India : Web Design Companies in varanasi We design amazing website designing, development and maintenance services running from start-ups to the huge players


    Wordpress Development Company Varanasi, India : Wordpress development Company In varanasi, india: Kashi Digital Agency is one of the Best wordpress developer companies in varanasi, india. Ranked among the Top website designing agencies in varanasi, india. wordpress website designing Company.

    E-commerce Website designing company varanasi, India : Ecommerce website designing company in Varanasi, India: Kashi Digital Agency is one of the Best Shopping Ecommerce website designing agency in Varanasi, India, which provides you the right services.

    ReplyDelete
  51. Thanks for SQL information, its really helpful.
    Recently I joined in web design firm have no experience on programming this kinds of blog help me a lot to gain new knowledge on programming languages. Now I started to design web and blogs, one such blog i designed recently is MOST EXCITING THINGS TO DO IN AND AROUND MUMBAI.
    once again thanking you...

    ReplyDelete
  52. its been long since i saw a post that's so educative and informational. i will make sure to share this my facebook group. you can also view contents on our websites below.

    French Bulldog Puppies For Sale

    French Bulldog Breeders

    French Bulldog Puppies For Sale Near Me

    French Bulldog Puppies For adoption

    French Bulldog Puppies


    Blue French Bulldog Puppies

    ReplyDelete
  53. The article you have shared here is very awesome. I really like and appreciate your work. Thanks for sharing us.
    Buy Instagram Followers India

    ReplyDelete
  54. Such a Really very helpful blog ,thanks for sharing more information. Digitak Agency In India

    ReplyDelete
  55. Parshuram Packers, fastly become the best Packers and movers in Bangalore to Ahmedabad. Book Now: 9812128186 for packers and movers services in Bangalore to Ahmedabad with 100% security at best price.

    ReplyDelete
  56. Are you trying to find a Dubai Ping Submission sites List 2021?. Here is checklist of Dubai Organization Listing Sites which you can use to obtain top quality backlink for your website as well as checklist your business today to rank higher in local search and expand your online.

    ReplyDelete
  57. Escort service in aerocity prepared this segment very care full. We give 24*7 hour administration so we just keep autonomous escorts in our call girls of Delhi Escorts office and our these escorts are completely agreeable in giving in call and outcall administration.https://jareena.com/?page_id=344

    ReplyDelete
  58. Ak Info Institute is a most appropriate coaching organization.It became mounted by using a group of experts so that scarcity of successfull and efficient mobile phone engineers may be fulfilled.

    How To Learn Mobile Chip Level Training

    ReplyDelete
  59. Demat account is now open for trading at a low brokerage so that you will be able to trade more by getting more margin, apart from the facilities of free Demat Account, advisory and other important information will also be made available to you.
    Stock Market
    Open Free Demat Account

    ReplyDelete
  60. I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
    Mobile Repairing Course In Delhi
    Mobile Repairing Course In Laxmi Nagar Delhi

    ReplyDelete
  61. I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
    Best Mobile Repairing Course In Delhi
    Best Mobile Repairing Course In Laxmi Nagar Delhi

    ReplyDelete
  62. 9xflix Com 2022 – is a top fav site for movies lovers who loved the download the latest movies sitting at home. With the help of 9x Flix.Com website.

    ReplyDelete
  63. Are you looking for best low cost oca lamination machine in Delhi, so you can choose baba tools official they have variety of tools and machine at an affordable price because they are direct importer from china baba tools has the latest and Advanced Technology to repair the broken display glass & touch or display. for visit Now!

    baba 1200 oca machine
    best oca lamination in Delhi
    oca lamination machine

    ReplyDelete
  64. Anyway your information on sql is well good i am also java developer thanks for information

    oca lamination machine
    oca machine price in karol bagh

    ReplyDelete
  65. This is a very interesting post about website development company in dehradun . Your information is very important to me. Thanks for sharing.

    ReplyDelete
  66. Great post by the great author, it is very massive and informative about App development companies UAE but still preaches the way to sounds like that it has some beautiful thoughts described so I really appreciate this post .

    ReplyDelete
  67. افضل خدمة نقل اثاث في دبي شركة نقل اثاث توفر خدمات نقل وتخزين وتغليف الاثاث في الامارات بأسعار منافسة
    نقل اثاث دبي
    شركة نقل اثاث دبي
    نقل اثاث الشارقة
    نقل اثاث ابوظبي
    نقل اثاث العين
    نقل اثاث الفجيرة

    ReplyDelete
  68. Very well post. It was an awesome post to read about Domain Name Registration Usa. Complete rich content and fully informative. I totally Loved it .

    ReplyDelete
  69. Very well post. It was an awesome post to read about Current Trending Tech News Online. Complete rich content and fully informative. I totally Loved it .

    ReplyDelete
  70. Great post. Post that have meaningful and insightful comments are more enjoyable, at least to me. Visit also WP Engine Web Hosting Online Review

    ReplyDelete
  71. I always prefer to read the quality content and this thing I found in your post. I am really thankful to you for this post. Visit also Ecommerce Website Hosting Services Provider .

    ReplyDelete
  72. I am attracted by the presentation of this article. It is a genuinely a gainful article for us. Keep posting, Thank you.Managed Database Services

    ReplyDelete
  73. Your work is really appreciable as the post you have shared here about google calendar is very helpful for me. Thanks for posting such post. business phone service providerPublic events sharing calendar online app

    ReplyDelete
  74. It enhances healthy blood circulation towards hair follicles and prevents hair fall. Apply only 5-6 drops of this oil on your scalp or mix a few drops of this oil with the best paraben free shampoo to yield the best results.
    aloe vera for oily skin

    ReplyDelete
  75. با وجود دقت بالای سیستم‌های کامپیوتری بر بازار و وجود انبوهی از اندیکاتورها، سم سیدن به لطف استراتژی عرضه و تقاضا
    ، از تحلیل تکنیکال سنتی اجتناب می‌کند. او برخلاف بسیاری از معامله‌گران این حوزه، رویکرد تقریبا ساده‌ای جهت تحلیل بازار دارد و اصلا در معاملات خود به اندیکاتورها، اسیلاتورها، بیانیه‌های بانک‌های مرکزی و سایر عواملی که معامله‌گران انتظار دارند که روی قیمت تاثیر داشته باشد، توجهی ندارد؛ زیرا آن‌ها را عامل زیان اکثریت معامله‌گران می‌داند.

    ReplyDelete
  76. I'm very curious about how you write such a good article. Are you an expert on this subject? I think so. Thank you again for allowing me to read these posts, and have a nice day today. Thank you.Digital Marketing Course In Laxmi Nagar

    ReplyDelete
  77. Thank you for sharing beautiful content. In case you want to read article based on life science, Astrology. Then visit below link
    Digital Marketing Course In Nirman Vihar

    ReplyDelete
  78. This comment has been removed by the author.

    ReplyDelete
  79. The representation of this article is really fantastic. I think this is really beneficial and instructive article for everyone website development company tampa I appreciate such writing and thank you for sharing such an article.

    ReplyDelete
  80. Thank you for sharing this valuable information! Here's a valuable info about photo booths https://photoboothrentalsanantoniotx.com/corporate-event-photo-booth-rental/

    ReplyDelete
  81. I always prefer to read the quality content and this thing I found in your post. I am really thankful to you for this post. Visit also White Label Credit Repair

    ReplyDelete
  82. ABCMIT institute offers the best mobile repairing course in Delhi
    ? Demo lessons are available to our students.

    ReplyDelete
  83. Come to our ABCMIT training center if you're looking for a led tv repairing course in Delhi. The Laxmi Nagar LCD LED Smart TV repair company offers an effective training programme to help students learn the subject.

    ReplyDelete
  84. Learn from the best digital marketing institute in chandigarh from webskydigitalmarketing
    How to generate airline calls

    Google ads for flight booking from webskydigitalmarketing

    Training for airline calls generation

    ReplyDelete
  85. suonerie telefono gratis I would like to thank the author for the effort and effort put into writing this blog post. Thanks for sharing valuable information and bringing new understanding to me.

    ReplyDelete
  86. This comment has been removed by the author.

    ReplyDelete
  87. That was a very informative post, thank you. Find motivation in the Bible

    ReplyDelete