July 1, 2009 at 9:34 am
For auditing purposes, we need to produce a monthly report showing any logins on our production database, along with permissions to any objects they have been granted.
We've used sp_helplogins to produce this information, but we wondered if there is a more comprehensive system stored procedure for this purpose, or if anyone has a custom script they use for retrieving login information?
Thanks,
Kay
July 2, 2009 at 9:52 am
I have something that I use that may be a little more encompassing then what you are asking for but it does include login role membership and object permissions. You should be able to get what you need out of it.
The code attached collects role and permission info across multiple databases so if you want to use the code as-is you will have to create all the tables in the script as well as linked servers to any of the servers you would like to audit. You will then have to populate the servers table with the servername(s)/linked servername(s) you wish to audit.
note: this is a work in progress so some columns may not get populated.
July 2, 2009 at 10:00 am
Thanks for the response, Bob. I'll tweak the script and see if I can pull out the information we need. I appreciate your help.
Kay
February 23, 2010 at 9:34 am
This script was creting tables but not undating data. Please help me what do I need to do next.
February 23, 2010 at 1:15 pm
February 23, 2010 at 1:27 pm
I executed complete script successfully. But only tables were created no rows wer inserted into tables.
I did not get any error meassage
February 24, 2010 at 4:37 am
Hi,
Thanks for this very comprehensive script. I tried to help and run it on one of my servers but I see the same as kwilt.
Script runs through reporting success but none of the tables have data. First issue must be on the servers table as this is used later on.
Cheers
February 24, 2010 at 10:50 am
I stated in my original post that the servers table has to be populated manually with the server/linkedservername. All other columns in server table are handled by the script.
ie.
insert into servers(name)
values('Server1')
February 25, 2010 at 1:43 am
Being able to read sometime helps :blush:
December 7, 2010 at 5:30 pm
Thanks for this script Bob.
I have made a number of tweaks to suit my purposes, but it has saved me a heap of investigation time to find out where everything is stored.
March 22, 2011 at 10:10 am
Hi All,
My sql knowledge is very limited, but it would be very helpful to be able to collate this sort of information. I have ran the script and understand that i have to populate the server table with the server names. What I don't yet understand is how i am able to get the final result. I would appreciate it if you could direct me a little further please.
🙂
March 22, 2011 at 11:15 am
March 22, 2011 at 11:29 am
Hi Bob, thanks for your immediate response to my post. I think i may have misread your original posts and what your script can do.
What is the next step to take for me to collate a list of all logins and permissions per database after i have run the script and entered a server name in the server.table. Just for info: i am using the one server for now, but would eventually like to collate this across all sql servers. I hope i am making sense :unsure:
thanks in advance for your advice.
March 22, 2011 at 12:25 pm
The script provided only collects information. How the information is used is really up to you.
What kind of data are you looking for?
here is a simple query that will put the data together but will be very impractical with many servers and/or databases.
SELECT a.name,b.name,c.role,c.,d.ObjectName,d.permission_name,d.state_desc
FROM servers a
inner join databases b
on a.id = b.serverid
inner join role_membership c
on a.id = c.serverid
and b.databaseid = c.databaseid
inner join object_permissions d
on a.id = d.serverid
and b.databaseid = d.databaseid
and c.role = d.GranteeName
March 23, 2011 at 3:18 am
Hi Bob, thanks for your advice on this. The information i wanted to collect is the server/database level logins/permissions that are assigned to each login. I have used sp_helplogins and that gives me the the role that the user is a member of but i really wanted to capture all permissions and securables.
So basically i want to know that if a user login is mapped to db1 and is assigned to the ddl_admin role if they can execute sp's and if so what sp's do they have execute permissions to.
I hope i am making sense.:unsure:
Thanks in advance!
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply