Changing Security Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NC3 - 27 Mar 2008 16:11 GMT

I need to change the security access policy in my environment. We are
consolidating several older servers for licensing considerations and
we want to change our security to a group based security where
possible. I've created the groups that are required and gave it a go
on on of our servers.

Needless to say many jobs began to break - some were expected because
we didn't have any documentation for the accounts.

What is the best way to execute such a change. Can someone direct me
to resources for auditing security in SQL i.e. account names and what
they're priveleges are so I can effectively make the changes that I
want with minimal disruption.

Any pointer or gotchas are welcome.

Reply to this Message

John Bell - 28 Mar 2008 09:08 GMT

Hi

You can query syspermissions in SQL 2000 and sys.database_permissions in
SQL 2005 to get what permissions have been granted or denied to a specific
group/user

e.g.

select o.name, u.name, p.actadd,
CASE WHEN p.actadd & 1 = 1 THEN 'Select' END AS [Select Permission Granted],
CASE WHEN p.actadd & 2 = 2 THEN 'Update' END AS [Update Permission Granted],
CASE WHEN p.actadd & 4 = 4 THEN 'DRI' END AS [DRI Permission Granted],
CASE WHEN p.actadd & 8 = 8 THEN 'Insert' END AS [Insert Permission Granted],
CASE WHEN p.actadd & 16 = 16 THEN 'Delete' END AS [Delete Permission Granted],
CASE WHEN p.actadd & 32 = 23 THEN 'Execute' END AS [Exec Permission Granted],
p.*
from syspermissions p
join sysusers u on p.grantee = u.uid
join sysobjects o on o.id = p.id
ORDER BY o.name

John

> I need to change the security access policy in my environment. We are
> consolidating several older servers for licensing considerations and
[quoted text clipped - 11 lines]
>
> Any pointer or gotchas are welcome.

Reply to this Message