March 23, 2011 at 10:56 am
For any fixed database roles(db_ddladmin), this script will not give you which objects they have access to because they are unchangeable. If you want to read up on fixed database roles and what they give access to follow this link (http://msdn.microsoft.com/en-us/library/ms189121%28v=SQL.90%29.aspx).
The role membership table does include fixed database role in its data so you can tell which users are members of the db_ddladmin role.
To get the info you ask about in your example, you could just take the code I supplied and modify it to suit your needs.
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
where d.class_desc = 'SQL_STORED_PROCEDURE'
and d.permission_name = 'EXECUTE'
and d.state_desc = 'GRANT'
and a.name = <servername> --insert your servername
and b.name = <databasename> --insert your databasename
and c. = <login> -- insert login
and c.[role] = <rolename> --insert role
March 29, 2011 at 5:12 am
Apologies for the delayed response Bob, thanks for your advice and script i will give it a go 🙂
July 20, 2011 at 3:42 pm
Looks like you invested lots of time in writing up this great script.
Thanks for sharing it...
:satisfied:
July 21, 2011 at 10:49 am
Here is a script I wrote up a while back. It uses impersonation and the has_perms_by_name() function to test access permissions for a specific account against each database object. If needed, you can retrofit it to loop though every account in the database.
/*
Query what objects a user account has access to in the current database.
http://msdn.microsoft.com/en-us/library/ms189802(v=sql.90).aspx
*/
revert;
go
declare @user varchar(8000);
select @user = '<accountname>';
/*
Returns information about Windows or domain group membership.
For example, it's useful to know if the account a member of
the local BUILTIN\Administrators group.
http://msdn.microsoft.com/en-us/library/ms190369.aspx
*/
exec master..xp_logininfo @user;
/* Impersonate the user, so the has_perms_by_name() function
can test their various levels of access to database objects. */
execute as user = @user;
go
select SYSTEM_USER sys_user_name, *
from
(
select
SCHEMA_NAME(schema_id) as schema_name,
type_desc as obj_type,
name as obj_name,
has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name,'OBJECT', 'ANY') AS has_any,
has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name,'OBJECT', 'VIEW DEFINITION') AS can_view_definition,
has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name,'OBJECT', 'EXEC') AS can_exec,
has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name,'OBJECT', 'SELECT') AS can_select,
has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name,'OBJECT', 'INSERT') AS can_insert,
has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name,'OBJECT', 'UPDATE') AS can_update,
has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name,'OBJECT', 'DELETE') AS can_delete,
has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name,'OBJECT', 'ALTER') AS can_alter
from sys.objects
where parent_object_id = 0
) x
order by has_any desc, can_exec desc, can_alter desc, can_delete desc, can_update desc, can_insert desc, can_select desc, can_view_definition desc;
go
revert;
go
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 1, 2011 at 6:34 pm
I am trying to run the script on my desktop system which has SQL Server 2008 R2 express edition. I added the server name to "Servers" table but when I ran the code for "load server info", I got an error that says "Server is not configured for Data Access.
November 1, 2011 at 6:41 pm
Here is another little script you could take a look at.
http://jasonbrimhall.info/2010/03/19/security-audit/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 15, 2012 at 4:59 am
Thanks Guys, Its a real time saver
March 15, 2012 at 6:39 am
Here is another script to compliment the scripting of logins and permissions; it will tell you what domain accounts have sysadmin access.
Query accounts, domain groups, and members who have admin membership.
http://www.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 16, 2012 at 6:57 am
And yet, another one....
Here is a link to one that I wrote to do an Instance Audit. It shows all users and roles assigned by database, and their individual permissions. Change the variable (@outputtype) and it prints out the assignment statements for the permissions.
Hope it helps.
http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply