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)
(SELECT, AS [role]
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


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' )

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' 
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.


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,,''?'' 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

@Cmd = 'select
select   * from #DatabaseRoleMemberShip) as p
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


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

  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.

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

  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

  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

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

  7. Hi

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


  8. This comment has been removed by the author.

  9. This comment has been removed by the author.

  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

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

  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

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

    Packers and Movers in Gurugram

  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

  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

  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

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

  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.

    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

  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

  20. This comment has been removed by the author.

  21. This comment has been removed by the author.

  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
    Happy reading :)

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

  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

  25. This comment has been removed by the author.

  26. It is an informative post.

  27. 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

  28. 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

  29. 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.


  30. 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!


  31. 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!

  32. great post. Thanks for sharing this information.

    Salesforce Development India


  34. Great
    we love your skills,
    keep sharing!

  35. 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

  36. 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

  37. this is an interesting sites and i like this sites, thank you sharing knowledge with us.

  38. 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

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

  40. 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

  41. 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

  42. 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

  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
    Basil badwan

  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.

    Basil badwan

  45. 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="”> Basil badwan </a>

  46. 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

  47. 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

  48. This comment has been removed by the author.

  49. This comment has been removed by the author.

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

  51. 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.

  52. 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...

  53. 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

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

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

  56. 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.

  57. 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.

  58. 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.

  59. 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

  60. 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

  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.
    Mobile Repairing Course In Delhi
    Mobile Repairing Course In Laxmi Nagar Delhi

  62. 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

  63. 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.

  64. 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

  65. 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

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

  67. 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 .

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

  69. 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 .

  70. 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 .

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

  72. 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 .

  73. 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

  74. 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

  75. 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

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

  77. 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

  78. 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

  79. This comment has been removed by the author.

  80. 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.

  81. Thank you for sharing this valuable information! Here's a valuable info about photo booths

  82. 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

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

  84. 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.

  85. 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

  86. 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.

  87. This comment has been removed by the author.

  88. Looking for mobile parts, tools, accessories, and more? Buy them all online at Shopzoneapp! Discover a vast selection of high-quality products at unbeatable prices, perfect for all your device needs. Whether you need replacement parts, repair tools, or stylish accessories, Shopzoneapp has it all. Enjoy the convenience of online shopping and have everything delivered right to your doorstep. Shop now at Shopzoneapp!