Requirement to get User info like permmissions / roles
I have a requirement where I need a procedure which can connect to my all sql Servers and get roles / permmissions for all the users.
March 11th, 2011 12:31am
I think you just need to create the right tsql statement and use that as a
OleDB Source in your dataflow or in a Execute SQL Task.
A good start for that query is:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/245b3e97-2f8d-4c52-b3ac-56875236cf4b
That's for one server... and you can do that in a Foreach Loop to do that for all your servers.Please mark the post as answered if it answers your question | My SSIS Blog:
http://microsoft-ssis.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
March 11th, 2011 7:58am
try this :
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx
you can use it as SSISJoost said with foreach to find for all servers.http://www.rad.pasfu.com
March 11th, 2011 6:53pm
Many Thanks for your reply.
I have around 100 instances.
So I need a script where i can input instance names as a file and it generates a nice report which is readable to auditors.
Can you help on this?
Free Windows Admin Tool Kit Click here and download it now
March 11th, 2011 10:27pm
Many Thanks for your reply.
I have around 100 instances.
So I need a script where i can input instance names as a file and it generates a nice report which is readable to auditors.
Can you help on this?
March 11th, 2011 10:27pm
Many Thanks for your reply.
I have around 100 instances.
So I need a script where i can input instance names as a file and it generates a nice report which is readable to auditors.
Can you help on this?
Free Windows Admin Tool Kit Click here and download it now
March 11th, 2011 10:27pm
Many Thanks for your reply.
I have around 100 instances.
So I need a script where i can input instance names as a file and it generates a nice report which is readable to auditors.
Can you help on this?
March 11th, 2011 10:27pm
Put all the servers in a database table (or file) and use a
Foreach Loop Container to loop through that list of servers.Please mark the post as answered if it answers your question | My SSIS Blog:
http://microsoft-ssis.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2011 2:09pm
you can also use foreach loop container with Foreach Item Enumerator,
this will allow you to create a data table as enumerator, add a column for server names, and add server name values there, then you can map a variable in variable mappings tab with index 0,
then inside foreach loop you can add a data flow task to connect to this server with expression property of data source.
http://www.rad.pasfu.com
March 12th, 2011 4:36pm
I used he below command as suggested by you:-
WITH
perms_cte
as
(
select USER_NAME(p.grantee_principal_id)
AS principal_name,
dp.principal_id,
dp.type_desc
AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id)
AS object_name,
p.permission_name,
p.state_desc
AS permission_state_desc
from sys.database_permissions p
inner JOIN
sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)
--users
SELECT
p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc,
cast(NULL as sysname)
as role_name
FROM
perms_cte p
WHERE
principal_type_desc <>
'DATABASE_ROLE'
UNION
--role members
SELECT
rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM
perms_cte p
right outer JOIN
(
select role_principal_id, dp.type_desc
as principal_type_desc, member_principal_id,user_name(member_principal_id)
as member_principal_name,user_name(role_principal_id)
as role_name--,*
from sys.database_role_members rm
INNER JOIN
sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON
rm.role_principal_id = p.principal_id
order by
1
But this is only giving me the system privs not database level roles.
Can I have a single stored procedure to get sys privs and db roles which accepts an argument a table contaning all instance names?
Free Windows Admin Tool Kit Click here and download it now
March 15th, 2011 12:29am
Can you help me how can I use reporting services for extracting user permmissions / database roles for n number of instances?
March 15th, 2011 8:53pm