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 licensing. Show all posts
Showing posts with label licensing. Show all posts
Sunday, March 11, 2012
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 Licensing Model on production server
Hello All,
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!
Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:
> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> to change the licensing model on the server to reflect the proper licensing
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!
|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that value.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something the
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!
Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:
> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> to change the licensing model on the server to reflect the proper licensing
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!
|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that value.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something the
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:
Changing Licensing Model on production server
Hello All,
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:
> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> to change the licensing model on the server to reflect the proper licensing
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that value.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something the
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:
> > Hello All,
> >
> > I have a pre-existing SQL server that appears to have been installed with
> > the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> > to change the licensing model on the server to reflect the proper licensing
> > configuration that is in place.
> >
> > Is there a way that this can ben done absent of having to reinstall SQL?
> >
> > Thanks!
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:
> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> to change the licensing model on the server to reflect the proper licensing
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that value.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something the
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:
> > Hello All,
> >
> > I have a pre-existing SQL server that appears to have been installed with
> > the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I need
> > to change the licensing model on the server to reflect the proper licensing
> > configuration that is in place.
> >
> > Is there a way that this can ben done absent of having to reinstall SQL?
> >
> > Thanks!
Changing Licensing Model on production server
Hello All,
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I nee
d
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:
> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I n
eed
> to change the licensing model on the server to reflect the proper licensin
g
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that valu
e.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something th
e
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:
>
I have a pre-existing SQL server that appears to have been installed with
the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I nee
d
to change the licensing model on the server to reflect the proper licensing
configuration that is in place.
Is there a way that this can ben done absent of having to reinstall SQL?
Thanks!Licensing should'nt affect QSL Operation; if you change from CAL to
Processor, just verify that the number of concurrent connections (Server
properties - Connections) arises to 0; eventually reset manually that value.
Passing from Processor to CAL you should set the same value to the right
value.
Remember however that the respect of your licensing option is something the
YOU and Microsoft agreed, and is'nt controlled by some software feature.
To be completely advised about licensing terms i suggest a call to your
Microsoft representative.
Gilberto Zampatti
"DBADave" wrote:
> Hello All,
> I have a pre-existing SQL server that appears to have been installed with
> the incorrect licensing model (ie CAL/Per-Seat vs Processor). As such I n
eed
> to change the licensing model on the server to reflect the proper licensin
g
> configuration that is in place.
> Is there a way that this can ben done absent of having to reinstall SQL?
> Thanks!|||Thanks Gilberto,
Specifically I need to know how I can change the licensing model from CAL to
Processor within Control Panel, as by default there is no option to switch
between the 2 modes. I understand that outside of reinstalling SQL, there
may be a registry change that is required and am interested to understand
what registry change (or alternate method other then reinstalling) can be
employed.
Thanks,
Dave
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Licensing should'nt affect QSL Operation; if you change from CAL to
> Processor, just verify that the number of concurrent connections (Server
> properties - Connections) arises to 0; eventually reset manually that valu
e.
> Passing from Processor to CAL you should set the same value to the right
> value.
> Remember however that the respect of your licensing option is something th
e
> YOU and Microsoft agreed, and is'nt controlled by some software feature.
> To be completely advised about licensing terms i suggest a call to your
> Microsoft representative.
> Gilberto Zampatti
> "DBADave" wrote:
>
Changing Licensing mode of SQL server
Hi All
Please can someone tell me if there is a way of changing the Licensing mode
of SQL Server 2000 from Device/User CALs to a Single CPU License mode ?
I ask because I am currently running Windows SBS 2000 and want to use the
SBS Migration Pack to split it down to the separate packages. And from what
I can see all the packages, including SQL server, are put in Device/User CAL
licensing more with 5 CALs (although I could be wrong!).
I need around 50+ CALs. So it would be cheaper to go for a Single CPU
license rather than all those CALs !
Any advice or suggestions much appreciated.
Benny
The following is unsupported but you may want to check the
link:
Q:How to change the license mode after SQL2000 setup?
http://www.tkdinesh.com/faq/ans/license.html
-Sue
On Wed, 4 Aug 2004 18:56:27 +0100, "Benny"
<NoSpam@.Thanks.net> wrote:
>Hi All
>Please can someone tell me if there is a way of changing the Licensing mode
>of SQL Server 2000 from Device/User CALs to a Single CPU License mode ?
>I ask because I am currently running Windows SBS 2000 and want to use the
>SBS Migration Pack to split it down to the separate packages. And from what
>I can see all the packages, including SQL server, are put in Device/User CAL
>licensing more with 5 CALs (although I could be wrong!).
>I need around 50+ CALs. So it would be cheaper to go for a Single CPU
>license rather than all those CALs !
>Any advice or suggestions much appreciated.
>Benny
>
Please can someone tell me if there is a way of changing the Licensing mode
of SQL Server 2000 from Device/User CALs to a Single CPU License mode ?
I ask because I am currently running Windows SBS 2000 and want to use the
SBS Migration Pack to split it down to the separate packages. And from what
I can see all the packages, including SQL server, are put in Device/User CAL
licensing more with 5 CALs (although I could be wrong!).
I need around 50+ CALs. So it would be cheaper to go for a Single CPU
license rather than all those CALs !
Any advice or suggestions much appreciated.
Benny
The following is unsupported but you may want to check the
link:
Q:How to change the license mode after SQL2000 setup?
http://www.tkdinesh.com/faq/ans/license.html
-Sue
On Wed, 4 Aug 2004 18:56:27 +0100, "Benny"
<NoSpam@.Thanks.net> wrote:
>Hi All
>Please can someone tell me if there is a way of changing the Licensing mode
>of SQL Server 2000 from Device/User CALs to a Single CPU License mode ?
>I ask because I am currently running Windows SBS 2000 and want to use the
>SBS Migration Pack to split it down to the separate packages. And from what
>I can see all the packages, including SQL server, are put in Device/User CAL
>licensing more with 5 CALs (although I could be wrong!).
>I need around 50+ CALs. So it would be cheaper to go for a Single CPU
>license rather than all those CALs !
>Any advice or suggestions much appreciated.
>Benny
>
Subscribe to:
Posts (Atom)