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.
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
"NC3" wrote:
> 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.
>
Showing posts with label older. Show all posts
Showing posts with label older. Show all posts
Sunday, March 11, 2012
Changing Security Access
Changing Security Access
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.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
"NC3" wrote:
> 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.
>
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.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
"NC3" wrote:
> 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.
>
Friday, February 24, 2012
Changing machine name & IP number on SQL Server 7.
Hi all!
We run a NT4 server machine running SQL 7 which contains some vital data on older databases. This machine was PDC for a long time, untill we got a new win2k server, which got the same machine name and IP number as the old NT4 machine which isn't yet connected to the AD. This new server does SQL 2000.
Since it's still important for us to have access to the SQL 7 data which cannot be used on SQL2000 server, I want to put the NT4 machine in the Win2k domain, but when I change the server's name and IP adress into something free (machine name and IP number are both taken over by new server) the SQL 7 server won't start anymore.
Is there anyone out there that knows how to solve this problem?
Thanks!!!! A LOT! :D :DI think you can run SQL 7 on the same machine as SQL 2000 and they won't bump into eachother.
blindman|||You can have SQL 2000 and one of the previous versions on the same machine. See if you want to do this. If not, see if the following article resolves your issues.
http://www.databasejournal.com/features/mssql/print.php/10894_2194711_3
All the best
We run a NT4 server machine running SQL 7 which contains some vital data on older databases. This machine was PDC for a long time, untill we got a new win2k server, which got the same machine name and IP number as the old NT4 machine which isn't yet connected to the AD. This new server does SQL 2000.
Since it's still important for us to have access to the SQL 7 data which cannot be used on SQL2000 server, I want to put the NT4 machine in the Win2k domain, but when I change the server's name and IP adress into something free (machine name and IP number are both taken over by new server) the SQL 7 server won't start anymore.
Is there anyone out there that knows how to solve this problem?
Thanks!!!! A LOT! :D :DI think you can run SQL 7 on the same machine as SQL 2000 and they won't bump into eachother.
blindman|||You can have SQL 2000 and one of the previous versions on the same machine. See if you want to do this. If not, see if the following article resolves your issues.
http://www.databasejournal.com/features/mssql/print.php/10894_2194711_3
All the best
Subscribe to:
Posts (Atom)