I am having an issue with persistent database corruption when copying
databases between instances. We have a production system with an 8GB (or
so) database that I copy over to our test systems on a regular basis.
A DBCC CHECKDB on this production database reports no issues. After
restoring the database (I do a backup and use the backup file to effect
the restore on a remote system via SQL Scripts) I have consistent
reports of corruption on the database when it is restored on the test
system.
A RESTORE VERIFYONLY on the backup file before restore only says "The
backup set is valid."
The amount of corruption varies; I have seen anywhere from 6 to 60
consistency errors.
I know this may be caused by underlying network or disk issues, but I'm
not sure how to go about confirming this when I don't see any other
related errors in the SQL or Windows event logs on the machine in question.
Stats:
SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
Windows Server 2003 Enterprise
4GB RAM
iSCSI SAN for all cluster-related disks using MS iSCSI drivers
Thanks,
JoshHmm. iSCSI drives. I have seen way too many problems with those. The MS
drivers may be just fine, but the iSCSI devices often cannot keep up with
the I/O load a SQL server can dish out. If he is paying attention, maybe
Dr. Tom Moreau will describe the hell he went through trying to use iSCSI
devices for a client.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joshua Andrews" <jrandrew@.nospam.hotmail.com> wrote in message
news:uNmE2Pu0GHA.4796@.TK2MSFTNGP06.phx.gbl...
>I am having an issue with persistent database corruption when copying
>databases between instances. We have a production system with an 8GB (or
>so) database that I copy over to our test systems on a regular basis.
> A DBCC CHECKDB on this production database reports no issues. After
> restoring the database (I do a backup and use the backup file to effect
> the restore on a remote system via SQL Scripts) I have consistent reports
> of corruption on the database when it is restored on the test system.
> A RESTORE VERIFYONLY on the backup file before restore only says "The
> backup set is valid."
> The amount of corruption varies; I have seen anywhere from 6 to 60
> consistency errors.
> I know this may be caused by underlying network or disk issues, but I'm
> not sure how to go about confirming this when I don't see any other
> related errors in the SQL or Windows event logs on the machine in
> question.
> Stats:
> SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
> Windows Server 2003 Enterprise
> 4GB RAM
> iSCSI SAN for all cluster-related disks using MS iSCSI drivers
> Thanks,
> Josh|||Can you post few consistency error you facing?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Joshua Andrews" wrote:
> I am having an issue with persistent database corruption when copying
> databases between instances. We have a production system with an 8GB (or
> so) database that I copy over to our test systems on a regular basis.
> A DBCC CHECKDB on this production database reports no issues. After
> restoring the database (I do a backup and use the backup file to effect
> the restore on a remote system via SQL Scripts) I have consistent
> reports of corruption on the database when it is restored on the test
> system.
> A RESTORE VERIFYONLY on the backup file before restore only says "The
> backup set is valid."
> The amount of corruption varies; I have seen anywhere from 6 to 60
> consistency errors.
> I know this may be caused by underlying network or disk issues, but I'm
> not sure how to go about confirming this when I don't see any other
> related errors in the SQL or Windows event logs on the machine in question.
> Stats:
> SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
> Windows Server 2003 Enterprise
> 4GB RAM
> iSCSI SAN for all cluster-related disks using MS iSCSI drivers
> Thanks,
> Josh
>|||Sreejith G wrote:
> Can you post few consistency error you facing?
Sure.
DBCC results for 'Table1'.
There are 3211496 rows in 94093 pages for object 'Table1'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'Table1' (object ID 1793857903).
*********
Bunch of clean tables
*********
DBCC results for 'Table2'.
There are 89870 rows in 2124 pages for object 'Table2'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CMN_PersonsSearchInfo' (object ID 1629248859).
*********
Bunch of clean tables
*********
DBCC results for 'Table3'.
There are 196205 rows in 3616 pages for object 'Table3'.
CHECKDB found 0 allocation errors and 6 consistency errors in table
'Table3' (object ID 1565248631).
*********
Bunch of clean tables
*********
CHECKDB found 0 allocation errors and 64 consistency errors in database
'DataDB'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (DataDB ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Of course, there are more, but the above is just a sample.
Thanks!
Josh
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Permissions with Windows groups and view to other databases
I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.
In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?
I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.
Thanks
Rob
Why not simply deny SELECT permission to group1 for that particular table? The rule that you need to remember is that a deny will trump a grant, so the deny will take precedence over the db_datareader membership.
Thanks
Laurentiu
|||Well, that won't quite work. The two Windows groups we are talking about have some overlapping members, but one group is not a complete subset of the other. So if I deny SELECT to group1 then some people that I want to access the data (group2) because they are in both groups and as you point out deny has a higher priority. Is there any reason group permissions are not valid in database2 when selecting from the view in database1?|||This is the expected behavior; but it sounds like you may be trying to attempt cross-database ownership chaining (also known as CDOC, look for “Using ownership chains” topic in BOL). CDOC is a feature that is disabled by default and we recommend against using it because of the security risks inherent from this feature. For more information on CDOC look for “Using ownership chains” topic in BOL.
The reason why user2 is failing to access the table is that there is a separate user token for database1 and database2 (both derived from the same Windows login token). On database1 the user2 token will look similar to this:
Primary identity:
· user2, Windows user
Secondary identities:
· group2, Windows group
· db_datareader, role
When accessing the view, the permissions are checked against this token, and they will succeed, but the view is making reference to database2.<some_schema>.restricted_table, therefore it is necessary to create a token for database2.
On your first attempt (without creating a user in Database2, and granting permission to access the table) the user token creation process for database2 should have failed with a “user cannot access this database”-type of error.
Here are a few potential workarounds that may help you:
Instead of using db_datareader you can use different schemas and grant SELECT based on the schemas to differentiate groups, for example:
GRANT SELECT ON SCHEMA::[Schema_group1] TO group1
GRANT SELECT ON SCHEMA::[Schema_group2] TO group2
GRANT SELECT ON SCHEMA::[Schema_all] TO group1, group2
That way the SELECT permission would be restricted to only the schemas you defined.
Another alternative for cross-DB access could be using signatures, similar to the one I described in the following article: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx
Please, let us know if any of this alternatives worked for you or if you have any additional questions.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
Permissions with Windows groups and view to other databases
I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.
In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?
I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.
Thanks
Rob
Why not simply deny SELECT permission to group1 for that particular table? The rule that you need to remember is that a deny will trump a grant, so the deny will take precedence over the db_datareader membership.
Thanks
Laurentiu
|||Well, that won't quite work. The two Windows groups we are talking about have some overlapping members, but one group is not a complete subset of the other. So if I deny SELECT to group1 then some people that I want to access the data (group2) because they are in both groups and as you point out deny has a higher priority. Is there any reason group permissions are not valid in database2 when selecting from the view in database1?|||This is the expected behavior; but it sounds like you may be trying to attempt cross-database ownership chaining (also known as CDOC, look for “Using ownership chains” topic in BOL). CDOC is a feature that is disabled by default and we recommend against using it because of the security risks inherent from this feature. For more information on CDOC look for “Using ownership chains” topic in BOL.
The reason why user2 is failing to access the table is that there is a separate user token for database1 and database2 (both derived from the same Windows login token). On database1 the user2 token will look similar to this:
Primary identity:
· user2, Windows user
Secondary identities:
· group2, Windows group
· db_datareader, role
When accessing the view, the permissions are checked against this token, and they will succeed, but the view is making reference to database2.<some_schema>.restricted_table, therefore it is necessary to create a token for database2.
On your first attempt (without creating a user in Database2, and granting permission to access the table) the user token creation process for database2 should have failed with a “user cannot access this database”-type of error.
Here are a few potential workarounds that may help you:
Instead of using db_datareader you can use different schemas and grant SELECT based on the schemas to differentiate groups, for example:
GRANT SELECT ON SCHEMA::[Schema_group1] TO group1
GRANT SELECT ON SCHEMA::[Schema_group2] TO group2
GRANT SELECT ON SCHEMA::[Schema_all] TO group1, group2
That way the SELECT permission would be restricted to only the schemas you defined.
Another alternative for cross-DB access could be using signatures, similar to the one I described in the following article: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx
Please, let us know if any of this alternatives worked for you or if you have any additional questions.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
Friday, March 23, 2012
permissions to run start and stop jobs
Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
only has db_owner permissions in a few databases. Should someone who is in
the db_owner role be able to start and stop jobs or run dts packages?
The user didn't used to be able to even see the jobs so I'm not sure what
changed.
--
Dan D.Normally, a user needs to be a member of sysadmins SQL
server role to start and stop any and all jobs. If not a
sysadmin, the user can only start and stop jobs that they
own.
Who owns the jobs? If the user owns the jobs, they can start
and stop the job. Also, did you verify that the user is
really using their login? Maybe they have EM registered with
a sysadmin login - that wouldn't be too hard to see in
sysprocesses when the user is connected - just check with
the hostname, program_name, loginame.
Another thing to check would be if the user is actually in
the sysamin server role inherited through Windows group
membership. If you left the BUILTIN\Administrators login in
SQL Server set to the default, was the user added to the
local administrators group on the server? That would be one
way...other ways would depend on what Windows groups are in
the syadmin SQL Server role. Then check to see if that user
is a member of any Windows group.
-Sue
On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>Using SS2000 SP4. I have a user who is able to start and stop jobs. The use
r
>only has db_owner permissions in a few databases. Should someone who is in
>the db_owner role be able to start and stop jobs or run dts packages?
>The user didn't used to be able to even see the jobs so I'm not sure what
>changed.|||BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
exist but they do not have any sysadmin type permissions.
I have another domain user setup and is the login for the SQLServer Agent
service and that user owns all jobs.
We checked the administrator groups for the domain and locally and the users
are not in there or part of a group that is in the admin group.
How would the user show up in sysprocesses? I see several entries for 'sa'
but they're all in the master database and background except for one that ha
s
'no database context' and is sleeping. Same for 'system' user.
There is an 'NT Authority\System' login that has sysadmin permisions. Is
there any way a user could use that login?
Thanks for so many choices.
--
Dan D.
"Sue Hoegemeier" wrote:
> Normally, a user needs to be a member of sysadmins SQL
> server role to start and stop any and all jobs. If not a
> sysadmin, the user can only start and stop jobs that they
> own.
> Who owns the jobs? If the user owns the jobs, they can start
> and stop the job. Also, did you verify that the user is
> really using their login? Maybe they have EM registered with
> a sysadmin login - that wouldn't be too hard to see in
> sysprocesses when the user is connected - just check with
> the hostname, program_name, loginame.
> Another thing to check would be if the user is actually in
> the sysamin server role inherited through Windows group
> membership. If you left the BUILTIN\Administrators login in
> SQL Server set to the default, was the user added to the
> local administrators group on the server? That would be one
> way...other ways would depend on what Windows groups are in
> the syadmin SQL Server role. Then check to see if that user
> is a member of any Windows group.
> -Sue
> On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Well...the user needs to be connected to see them in
sysprocesses. You'd want to know what PC they use - that
would show up in hostname.
But probably stepping back a bit is a good idea. All the
same things I posted apply but how do you know the user is
executing jobs? Do you know how the user is executing jobs -
such as does the user connect from Enterprise Manager and
just start and stop jobs at will?
-Sue
On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrato
r
>exist but they do not have any sysadmin type permissions.
>I have another domain user setup and is the login for the SQLServer Agent
>service and that user owns all jobs.
>We checked the administrator groups for the domain and locally and the user
s
>are not in there or part of a group that is in the admin group.
>How would the user show up in sysprocesses? I see several entries for 'sa'
>but they're all in the master database and background except for one that h
as
>'no database context' and is sleeping. Same for 'system' user.
>There is an 'NT Authority\System' login that has sysadmin permisions. Is
>there any way a user could use that login?
>Thanks for so many choices.|||The user said that he could start a job. And I know that someone stopped a
job the other day. Also, the user said that he could backup the database.
I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
also might change the 'sa' password on this server.
Thanks for your suggestions.
--
Dan D.
"Sue Hoegemeier" wrote:
> Well...the user needs to be connected to see them in
> sysprocesses. You'd want to know what PC they use - that
> would show up in hostname.
> But probably stepping back a bit is a good idea. All the
> same things I posted apply but how do you know the user is
> executing jobs? Do you know how the user is executing jobs -
> such as does the user connect from Enterprise Manager and
> just start and stop jobs at will?
> -Sue
> On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Yup...changing he password in case they got ahold of that is
a good idea. You may also want to run a trace when the user
is at work. Had to do those before. You tend to find other
more interesting activities they do. Often has some
entertainment value if nothing else.
-Sue
On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>The user said that he could start a job. And I know that someone stopped a
>job the other day. Also, the user said that he could backup the database.
>I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
>also might change the 'sa' password on this server.
>Thanks for your suggestions.|||A little extra entertainment is good. Thanks.
--
Dan D.
"Sue Hoegemeier" wrote:
> Yup...changing he password in case they got ahold of that is
> a good idea. You may also want to run a trace when the user
> is at work. Had to do those before. You tend to find other
> more interesting activities they do. Often has some
> entertainment value if nothing else.
> -Sue
> On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>sql
only has db_owner permissions in a few databases. Should someone who is in
the db_owner role be able to start and stop jobs or run dts packages?
The user didn't used to be able to even see the jobs so I'm not sure what
changed.
--
Dan D.Normally, a user needs to be a member of sysadmins SQL
server role to start and stop any and all jobs. If not a
sysadmin, the user can only start and stop jobs that they
own.
Who owns the jobs? If the user owns the jobs, they can start
and stop the job. Also, did you verify that the user is
really using their login? Maybe they have EM registered with
a sysadmin login - that wouldn't be too hard to see in
sysprocesses when the user is connected - just check with
the hostname, program_name, loginame.
Another thing to check would be if the user is actually in
the sysamin server role inherited through Windows group
membership. If you left the BUILTIN\Administrators login in
SQL Server set to the default, was the user added to the
local administrators group on the server? That would be one
way...other ways would depend on what Windows groups are in
the syadmin SQL Server role. Then check to see if that user
is a member of any Windows group.
-Sue
On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>Using SS2000 SP4. I have a user who is able to start and stop jobs. The use
r
>only has db_owner permissions in a few databases. Should someone who is in
>the db_owner role be able to start and stop jobs or run dts packages?
>The user didn't used to be able to even see the jobs so I'm not sure what
>changed.|||BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
exist but they do not have any sysadmin type permissions.
I have another domain user setup and is the login for the SQLServer Agent
service and that user owns all jobs.
We checked the administrator groups for the domain and locally and the users
are not in there or part of a group that is in the admin group.
How would the user show up in sysprocesses? I see several entries for 'sa'
but they're all in the master database and background except for one that ha
s
'no database context' and is sleeping. Same for 'system' user.
There is an 'NT Authority\System' login that has sysadmin permisions. Is
there any way a user could use that login?
Thanks for so many choices.
--
Dan D.
"Sue Hoegemeier" wrote:
> Normally, a user needs to be a member of sysadmins SQL
> server role to start and stop any and all jobs. If not a
> sysadmin, the user can only start and stop jobs that they
> own.
> Who owns the jobs? If the user owns the jobs, they can start
> and stop the job. Also, did you verify that the user is
> really using their login? Maybe they have EM registered with
> a sysadmin login - that wouldn't be too hard to see in
> sysprocesses when the user is connected - just check with
> the hostname, program_name, loginame.
> Another thing to check would be if the user is actually in
> the sysamin server role inherited through Windows group
> membership. If you left the BUILTIN\Administrators login in
> SQL Server set to the default, was the user added to the
> local administrators group on the server? That would be one
> way...other ways would depend on what Windows groups are in
> the syadmin SQL Server role. Then check to see if that user
> is a member of any Windows group.
> -Sue
> On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Well...the user needs to be connected to see them in
sysprocesses. You'd want to know what PC they use - that
would show up in hostname.
But probably stepping back a bit is a good idea. All the
same things I posted apply but how do you know the user is
executing jobs? Do you know how the user is executing jobs -
such as does the user connect from Enterprise Manager and
just start and stop jobs at will?
-Sue
On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrato
r
>exist but they do not have any sysadmin type permissions.
>I have another domain user setup and is the login for the SQLServer Agent
>service and that user owns all jobs.
>We checked the administrator groups for the domain and locally and the user
s
>are not in there or part of a group that is in the admin group.
>How would the user show up in sysprocesses? I see several entries for 'sa'
>but they're all in the master database and background except for one that h
as
>'no database context' and is sleeping. Same for 'system' user.
>There is an 'NT Authority\System' login that has sysadmin permisions. Is
>there any way a user could use that login?
>Thanks for so many choices.
|||The user said that he could start a job. And I know that someone stopped ajob the other day. Also, the user said that he could backup the database.
I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
also might change the 'sa' password on this server.
Thanks for your suggestions.
--
Dan D.
"Sue Hoegemeier" wrote:
> Well...the user needs to be connected to see them in
> sysprocesses. You'd want to know what PC they use - that
> would show up in hostname.
> But probably stepping back a bit is a good idea. All the
> same things I posted apply but how do you know the user is
> executing jobs? Do you know how the user is executing jobs -
> such as does the user connect from Enterprise Manager and
> just start and stop jobs at will?
> -Sue
> On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Yup...changing he password in case they got ahold of that is
a good idea. You may also want to run a trace when the user
is at work. Had to do those before. You tend to find other
more interesting activities they do. Often has some
entertainment value if nothing else.
-Sue
On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>The user said that he could start a job. And I know that someone stopped a
>job the other day. Also, the user said that he could backup the database.
>I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
>also might change the 'sa' password on this server.
>Thanks for your suggestions.|||A little extra entertainment is good. Thanks.
--
Dan D.
"Sue Hoegemeier" wrote:
> Yup...changing he password in case they got ahold of that is
> a good idea. You may also want to run a trace when the user
> is at work. Had to do those before. You tend to find other
> more interesting activities they do. Often has some
> entertainment value if nothing else.
> -Sue
> On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>sql
permissions to run start and stop jobs
Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
only has db_owner permissions in a few databases. Should someone who is in
the db_owner role be able to start and stop jobs or run dts packages?
The user didn't used to be able to even see the jobs so I'm not sure what
changed.
--
Dan D.Normally, a user needs to be a member of sysadmins SQL
server role to start and stop any and all jobs. If not a
sysadmin, the user can only start and stop jobs that they
own.
Who owns the jobs? If the user owns the jobs, they can start
and stop the job. Also, did you verify that the user is
really using their login? Maybe they have EM registered with
a sysadmin login - that wouldn't be too hard to see in
sysprocesses when the user is connected - just check with
the hostname, program_name, loginame.
Another thing to check would be if the user is actually in
the sysamin server role inherited through Windows group
membership. If you left the BUILTIN\Administrators login in
SQL Server set to the default, was the user added to the
local administrators group on the server? That would be one
way...other ways would depend on what Windows groups are in
the syadmin SQL Server role. Then check to see if that user
is a member of any Windows group.
-Sue
On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
>only has db_owner permissions in a few databases. Should someone who is in
>the db_owner role be able to start and stop jobs or run dts packages?
>The user didn't used to be able to even see the jobs so I'm not sure what
>changed.|||BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
exist but they do not have any sysadmin type permissions.
I have another domain user setup and is the login for the SQLServer Agent
service and that user owns all jobs.
We checked the administrator groups for the domain and locally and the users
are not in there or part of a group that is in the admin group.
How would the user show up in sysprocesses? I see several entries for 'sa'
but they're all in the master database and background except for one that has
'no database context' and is sleeping. Same for 'system' user.
There is an 'NT Authority\System' login that has sysadmin permisions. Is
there any way a user could use that login?
Thanks for so many choices.:)
--
Dan D.
"Sue Hoegemeier" wrote:
> Normally, a user needs to be a member of sysadmins SQL
> server role to start and stop any and all jobs. If not a
> sysadmin, the user can only start and stop jobs that they
> own.
> Who owns the jobs? If the user owns the jobs, they can start
> and stop the job. Also, did you verify that the user is
> really using their login? Maybe they have EM registered with
> a sysadmin login - that wouldn't be too hard to see in
> sysprocesses when the user is connected - just check with
> the hostname, program_name, loginame.
> Another thing to check would be if the user is actually in
> the sysamin server role inherited through Windows group
> membership. If you left the BUILTIN\Administrators login in
> SQL Server set to the default, was the user added to the
> local administrators group on the server? That would be one
> way...other ways would depend on what Windows groups are in
> the syadmin SQL Server role. Then check to see if that user
> is a member of any Windows group.
> -Sue
> On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
> >only has db_owner permissions in a few databases. Should someone who is in
> >the db_owner role be able to start and stop jobs or run dts packages?
> >
> >The user didn't used to be able to even see the jobs so I'm not sure what
> >changed.
>|||Well...the user needs to be connected to see them in
sysprocesses. You'd want to know what PC they use - that
would show up in hostname.
But probably stepping back a bit is a good idea. All the
same things I posted apply but how do you know the user is
executing jobs? Do you know how the user is executing jobs -
such as does the user connect from Enterprise Manager and
just start and stop jobs at will?
-Sue
On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
>exist but they do not have any sysadmin type permissions.
>I have another domain user setup and is the login for the SQLServer Agent
>service and that user owns all jobs.
>We checked the administrator groups for the domain and locally and the users
>are not in there or part of a group that is in the admin group.
>How would the user show up in sysprocesses? I see several entries for 'sa'
>but they're all in the master database and background except for one that has
>'no database context' and is sleeping. Same for 'system' user.
>There is an 'NT Authority\System' login that has sysadmin permisions. Is
>there any way a user could use that login?
>Thanks for so many choices.:)|||The user said that he could start a job. And I know that someone stopped a
job the other day. Also, the user said that he could backup the database.
I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
also might change the 'sa' password on this server.
Thanks for your suggestions.
--
Dan D.
"Sue Hoegemeier" wrote:
> Well...the user needs to be connected to see them in
> sysprocesses. You'd want to know what PC they use - that
> would show up in hostname.
> But probably stepping back a bit is a good idea. All the
> same things I posted apply but how do you know the user is
> executing jobs? Do you know how the user is executing jobs -
> such as does the user connect from Enterprise Manager and
> just start and stop jobs at will?
> -Sue
> On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
> >exist but they do not have any sysadmin type permissions.
> >
> >I have another domain user setup and is the login for the SQLServer Agent
> >service and that user owns all jobs.
> >
> >We checked the administrator groups for the domain and locally and the users
> >are not in there or part of a group that is in the admin group.
> >
> >How would the user show up in sysprocesses? I see several entries for 'sa'
> >but they're all in the master database and background except for one that has
> >'no database context' and is sleeping. Same for 'system' user.
> >
> >There is an 'NT Authority\System' login that has sysadmin permisions. Is
> >there any way a user could use that login?
> >
> >Thanks for so many choices.:)
>|||Yup...changing he password in case they got ahold of that is
a good idea. You may also want to run a trace when the user
is at work. Had to do those before. You tend to find other
more interesting activities they do. Often has some
entertainment value if nothing else.
-Sue
On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>The user said that he could start a job. And I know that someone stopped a
>job the other day. Also, the user said that he could backup the database.
>I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
>also might change the 'sa' password on this server.
>Thanks for your suggestions.|||A little extra entertainment is good. Thanks.
--
Dan D.
"Sue Hoegemeier" wrote:
> Yup...changing he password in case they got ahold of that is
> a good idea. You may also want to run a trace when the user
> is at work. Had to do those before. You tend to find other
> more interesting activities they do. Often has some
> entertainment value if nothing else.
> -Sue
> On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >The user said that he could start a job. And I know that someone stopped a
> >job the other day. Also, the user said that he could backup the database.
> >
> >I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
> >also might change the 'sa' password on this server.
> >
> >Thanks for your suggestions.
>
only has db_owner permissions in a few databases. Should someone who is in
the db_owner role be able to start and stop jobs or run dts packages?
The user didn't used to be able to even see the jobs so I'm not sure what
changed.
--
Dan D.Normally, a user needs to be a member of sysadmins SQL
server role to start and stop any and all jobs. If not a
sysadmin, the user can only start and stop jobs that they
own.
Who owns the jobs? If the user owns the jobs, they can start
and stop the job. Also, did you verify that the user is
really using their login? Maybe they have EM registered with
a sysadmin login - that wouldn't be too hard to see in
sysprocesses when the user is connected - just check with
the hostname, program_name, loginame.
Another thing to check would be if the user is actually in
the sysamin server role inherited through Windows group
membership. If you left the BUILTIN\Administrators login in
SQL Server set to the default, was the user added to the
local administrators group on the server? That would be one
way...other ways would depend on what Windows groups are in
the syadmin SQL Server role. Then check to see if that user
is a member of any Windows group.
-Sue
On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
>only has db_owner permissions in a few databases. Should someone who is in
>the db_owner role be able to start and stop jobs or run dts packages?
>The user didn't used to be able to even see the jobs so I'm not sure what
>changed.|||BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
exist but they do not have any sysadmin type permissions.
I have another domain user setup and is the login for the SQLServer Agent
service and that user owns all jobs.
We checked the administrator groups for the domain and locally and the users
are not in there or part of a group that is in the admin group.
How would the user show up in sysprocesses? I see several entries for 'sa'
but they're all in the master database and background except for one that has
'no database context' and is sleeping. Same for 'system' user.
There is an 'NT Authority\System' login that has sysadmin permisions. Is
there any way a user could use that login?
Thanks for so many choices.:)
--
Dan D.
"Sue Hoegemeier" wrote:
> Normally, a user needs to be a member of sysadmins SQL
> server role to start and stop any and all jobs. If not a
> sysadmin, the user can only start and stop jobs that they
> own.
> Who owns the jobs? If the user owns the jobs, they can start
> and stop the job. Also, did you verify that the user is
> really using their login? Maybe they have EM registered with
> a sysadmin login - that wouldn't be too hard to see in
> sysprocesses when the user is connected - just check with
> the hostname, program_name, loginame.
> Another thing to check would be if the user is actually in
> the sysamin server role inherited through Windows group
> membership. If you left the BUILTIN\Administrators login in
> SQL Server set to the default, was the user added to the
> local administrators group on the server? That would be one
> way...other ways would depend on what Windows groups are in
> the syadmin SQL Server role. Then check to see if that user
> is a member of any Windows group.
> -Sue
> On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
> >only has db_owner permissions in a few databases. Should someone who is in
> >the db_owner role be able to start and stop jobs or run dts packages?
> >
> >The user didn't used to be able to even see the jobs so I'm not sure what
> >changed.
>|||Well...the user needs to be connected to see them in
sysprocesses. You'd want to know what PC they use - that
would show up in hostname.
But probably stepping back a bit is a good idea. All the
same things I posted apply but how do you know the user is
executing jobs? Do you know how the user is executing jobs -
such as does the user connect from Enterprise Manager and
just start and stop jobs at will?
-Sue
On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
>exist but they do not have any sysadmin type permissions.
>I have another domain user setup and is the login for the SQLServer Agent
>service and that user owns all jobs.
>We checked the administrator groups for the domain and locally and the users
>are not in there or part of a group that is in the admin group.
>How would the user show up in sysprocesses? I see several entries for 'sa'
>but they're all in the master database and background except for one that has
>'no database context' and is sleeping. Same for 'system' user.
>There is an 'NT Authority\System' login that has sysadmin permisions. Is
>there any way a user could use that login?
>Thanks for so many choices.:)|||The user said that he could start a job. And I know that someone stopped a
job the other day. Also, the user said that he could backup the database.
I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
also might change the 'sa' password on this server.
Thanks for your suggestions.
--
Dan D.
"Sue Hoegemeier" wrote:
> Well...the user needs to be connected to see them in
> sysprocesses. You'd want to know what PC they use - that
> would show up in hostname.
> But probably stepping back a bit is a good idea. All the
> same things I posted apply but how do you know the user is
> executing jobs? Do you know how the user is executing jobs -
> such as does the user connect from Enterprise Manager and
> just start and stop jobs at will?
> -Sue
> On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
> >exist but they do not have any sysadmin type permissions.
> >
> >I have another domain user setup and is the login for the SQLServer Agent
> >service and that user owns all jobs.
> >
> >We checked the administrator groups for the domain and locally and the users
> >are not in there or part of a group that is in the admin group.
> >
> >How would the user show up in sysprocesses? I see several entries for 'sa'
> >but they're all in the master database and background except for one that has
> >'no database context' and is sleeping. Same for 'system' user.
> >
> >There is an 'NT Authority\System' login that has sysadmin permisions. Is
> >there any way a user could use that login?
> >
> >Thanks for so many choices.:)
>|||Yup...changing he password in case they got ahold of that is
a good idea. You may also want to run a trace when the user
is at work. Had to do those before. You tend to find other
more interesting activities they do. Often has some
entertainment value if nothing else.
-Sue
On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>The user said that he could start a job. And I know that someone stopped a
>job the other day. Also, the user said that he could backup the database.
>I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
>also might change the 'sa' password on this server.
>Thanks for your suggestions.|||A little extra entertainment is good. Thanks.
--
Dan D.
"Sue Hoegemeier" wrote:
> Yup...changing he password in case they got ahold of that is
> a good idea. You may also want to run a trace when the user
> is at work. Had to do those before. You tend to find other
> more interesting activities they do. Often has some
> entertainment value if nothing else.
> -Sue
> On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >The user said that he could start a job. And I know that someone stopped a
> >job the other day. Also, the user said that he could backup the database.
> >
> >I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
> >also might change the 'sa' password on this server.
> >
> >Thanks for your suggestions.
>
Permissions system_function_schema
Hi all,
I have user defined functions in master databases such as
system_function_schema.fn_chariswhitespace
As default this kind of functions have a public permissions for EXEC, but I
want to remove this
When I execute the sentece
REVOKE EXEC ON system_function_schema.fn_chariswhitespace TO PUBLIC
I got nex error
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name 'system_function_schema.fn_chariswhitespace'.
I have SQL Server 2000 64 bits.
Can anybody help me?
Thanks in advance,
Javier RosasHi,
You are using an undocumented/unsupported featured. The
system_function_schema doesn't exist in SQL 2005 and you will have
difficulties upgrading your code.
I recommend you abandon use of the system_function_schema
Regards,
Clifford Dibble
"Javier Rosas" wrote:
> Hi all,
> I have user defined functions in master databases such as
> system_function_schema.fn_chariswhitespace
> As default this kind of functions have a public permissions for EXEC, but
I
> want to remove this
> When I execute the sentece
> REVOKE EXEC ON system_function_schema.fn_chariswhitespace TO PUBLIC
> I got nex error
> Server: Msg 208, Level 16, State 11, Line 1
> Invalid object name 'system_function_schema.fn_chariswhitespace'.
> I have SQL Server 2000 64 bits.
> Can anybody help me?
> Thanks in advance,
> Javier Rosas
I have user defined functions in master databases such as
system_function_schema.fn_chariswhitespace
As default this kind of functions have a public permissions for EXEC, but I
want to remove this
When I execute the sentece
REVOKE EXEC ON system_function_schema.fn_chariswhitespace TO PUBLIC
I got nex error
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name 'system_function_schema.fn_chariswhitespace'.
I have SQL Server 2000 64 bits.
Can anybody help me?
Thanks in advance,
Javier RosasHi,
You are using an undocumented/unsupported featured. The
system_function_schema doesn't exist in SQL 2005 and you will have
difficulties upgrading your code.
I recommend you abandon use of the system_function_schema
Regards,
Clifford Dibble
"Javier Rosas" wrote:
> Hi all,
> I have user defined functions in master databases such as
> system_function_schema.fn_chariswhitespace
> As default this kind of functions have a public permissions for EXEC, but
I
> want to remove this
> When I execute the sentece
> REVOKE EXEC ON system_function_schema.fn_chariswhitespace TO PUBLIC
> I got nex error
> Server: Msg 208, Level 16, State 11, Line 1
> Invalid object name 'system_function_schema.fn_chariswhitespace'.
> I have SQL Server 2000 64 bits.
> Can anybody help me?
> Thanks in advance,
> Javier Rosas
permissions required for executing CDOSys stored procedures
Hi there
I'm doing some analysis on the database applications in my organisation
before migrating the databases to a new server. One of these makes use of
CDOSys objects for sending mail, instead of SQL Mail. There are a number of
stored procedures within the database that call the sp_OACreate &
sp_OASetProperty. Apparently only members of the sysadmin role can execute
these stored procedures, however, the sql login for this application is not
a
sysadmin! I thought perhaps there was a mistake in books online but i've
looked on google and the permission requirements are the same - must be
sysadmin. Any ideas how it still manages to function without these rights?
Thanks in advance!Neile,
Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
SomeOtherPrincipal.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Thanks for the response. I had a look at the permissions on the stored
procedures and could see none granted on the login in question... bizarre.
I
assume the way to check this is by going into the procedure properties scree
n
and clicking on the permissions tab? Is there another view somewhere else'
thx
"Russell Fields" wrote:
> Neile,
> Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
> SomeOtherPrincipal.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>
>|||Neile,
I usually use sp_helprotect to investigate these things. E.g.
sp_helprotect @.name='sp_OACreate'
I don't know what version of SQL Server you are using, but in 2005 the
properties of the sp_OACreate extended stored procedure has a permissions
page that shows this also, at least in SP2. With SQL Server 2000 Enterprise
Manager there is a permissions button on the properties form that serves a
similar purpose.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I had a look at the permissions on the stored
> procedures and could see none granted on the login in question...
> bizarre. I
> assume the way to check this is by going into the procedure properties
> screen
> and clicking on the permissions tab? Is there another view somewhere
> else'
> thx
> "Russell Fields" wrote:
>|||Hi Russell,
Thanks for this. It's a sql 2000 box and i had checked the permission
properties page as you suggested below but it was blank. So it appears that
the these stored procedures are being executed by a user who is not a
sysadmin and who has not been granted specific execute permissions on the
procedures.
Is this possible'
Thanks
"Russell Fields" wrote:
> Neile,
> I usually use sp_helprotect to investigate these things. E.g.
> sp_helprotect @.name='sp_OACreate'
> I don't know what version of SQL Server you are using, but in 2005 the
> properties of the sp_OACreate extended stored procedure has a permissions
> page that shows this also, at least in SP2. With SQL Server 2000 Enterpri
se
> Manager there is a permissions button on the properties form that serves a
> similar purpose.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...
>
>|||Neile,
I don't know what to say about a blank properties page except, "I don't
know." Of course, the sp_OA procedures can be granted to public, which
would give everybody rights, but would also result in public having a green
check box next to it.
What was the response to: sp_helprotect @.name='sp_OACreate'
Another possibility is that the user is also in the local administrators
group of the server. If so, he is by default member of the sysadmin server
role on the SQL Server database unless steps are taken to prevent that. For
example, as described in http://support.microsoft.com/kb/263712/.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> Thanks for this. It's a sql 2000 box and i had checked the permission
> properties page as you suggested below but it was blank. So it appears
> that
> the these stored procedures are being executed by a user who is not a
> sysadmin and who has not been granted specific execute permissions on the
> procedures.
> Is this possible'
> Thanks
> "Russell Fields" wrote:
>|||Oh, and if the user is db_owner in master he also can execute sp_OA
procedures.
The BOL does not say this is possible, but I just tested it and it does work
on SQL 2000 and 2005. (So is this an 'undocumented' feature or an error in
documentation? I have reported it as an error.)
RLF
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OdHHkzqrHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Neile,
> I don't know what to say about a blank properties page except, "I don't
> know." Of course, the sp_OA procedures can be granted to public, which
> would give everybody rights, but would also result in public having a
> green check box next to it.
> What was the response to: sp_helprotect @.name='sp_OACreate'
> Another possibility is that the user is also in the local administrators
> group of the server. If so, he is by default member of the sysadmin
> server role on the SQL Server database unless steps are taken to prevent
> that. For example, as described in
> http://support.microsoft.com/kb/263712/.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...
>|||Perhaps the cross-database chaining (a.k.a. db_chaining) database option is
turned on. In that case, execute permissions on indirectly referenced
objects in other databases are not needed as long as the ownership chain is
unbroken. Users need execute permissions on only the user stored procedure.
Note that the user database must be owned by 'sa' to maintain an unbroken
chain to master database objects. It is important that only sysadmin users
be allowed to create dbo-owned objects in this scenario in order to prevent
elevation of privileges.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Dan,
Thanks for that reminder. We broke our ownership chains many years ago,
well before SQL 2000 SP3, by giving each database a different owner. It had
faded from memory.
RLF
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
> Perhaps the cross-database chaining (a.k.a. db_chaining) database option
> is turned on. In that case, execute permissions on indirectly referenced
> objects in other databases are not needed as long as the ownership chain
> is unbroken. Users need execute permissions on only the user stored
> procedure.
> Note that the user database must be owned by 'sa' to maintain an unbroken
> chain to master database objects. It is important that only sysadmin
> users be allowed to create dbo-owned objects in this scenario in order to
> prevent elevation of privileges.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>|||Hi Russell
Thanks for all the tips - this is all useful stuff to know. I discovered
that the stored procedure in the database which in turn calls the CDOSys
stored procedures is executed via a SQL job - and thus in the context of the
job owner, which is sa. So that explains it!!
But thanks for your suggestions and thanks to Dan for the info about cross
db chaining - all good to know.
Cheers
Neile
"Russell Fields" wrote:
> Dan,
> Thanks for that reminder. We broke our ownership chains many years ago,
> well before SQL 2000 SP3, by giving each database a different owner. It h
ad
> faded from memory.
> RLF
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
>
>
I'm doing some analysis on the database applications in my organisation
before migrating the databases to a new server. One of these makes use of
CDOSys objects for sending mail, instead of SQL Mail. There are a number of
stored procedures within the database that call the sp_OACreate &
sp_OASetProperty. Apparently only members of the sysadmin role can execute
these stored procedures, however, the sql login for this application is not
a
sysadmin! I thought perhaps there was a mistake in books online but i've
looked on google and the permission requirements are the same - must be
sysadmin. Any ideas how it still manages to function without these rights?
Thanks in advance!Neile,
Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
SomeOtherPrincipal.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Thanks for the response. I had a look at the permissions on the stored
procedures and could see none granted on the login in question... bizarre.
I
assume the way to check this is by going into the procedure properties scree
n
and clicking on the permissions tab? Is there another view somewhere else'
thx
"Russell Fields" wrote:
> Neile,
> Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
> SomeOtherPrincipal.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>
>|||Neile,
I usually use sp_helprotect to investigate these things. E.g.
sp_helprotect @.name='sp_OACreate'
I don't know what version of SQL Server you are using, but in 2005 the
properties of the sp_OACreate extended stored procedure has a permissions
page that shows this also, at least in SP2. With SQL Server 2000 Enterprise
Manager there is a permissions button on the properties form that serves a
similar purpose.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I had a look at the permissions on the stored
> procedures and could see none granted on the login in question...
> bizarre. I
> assume the way to check this is by going into the procedure properties
> screen
> and clicking on the permissions tab? Is there another view somewhere
> else'
> thx
> "Russell Fields" wrote:
>|||Hi Russell,
Thanks for this. It's a sql 2000 box and i had checked the permission
properties page as you suggested below but it was blank. So it appears that
the these stored procedures are being executed by a user who is not a
sysadmin and who has not been granted specific execute permissions on the
procedures.
Is this possible'
Thanks
"Russell Fields" wrote:
> Neile,
> I usually use sp_helprotect to investigate these things. E.g.
> sp_helprotect @.name='sp_OACreate'
> I don't know what version of SQL Server you are using, but in 2005 the
> properties of the sp_OACreate extended stored procedure has a permissions
> page that shows this also, at least in SP2. With SQL Server 2000 Enterpri
se
> Manager there is a permissions button on the properties form that serves a
> similar purpose.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...
>
>|||Neile,
I don't know what to say about a blank properties page except, "I don't
know." Of course, the sp_OA procedures can be granted to public, which
would give everybody rights, but would also result in public having a green
check box next to it.
What was the response to: sp_helprotect @.name='sp_OACreate'
Another possibility is that the user is also in the local administrators
group of the server. If so, he is by default member of the sysadmin server
role on the SQL Server database unless steps are taken to prevent that. For
example, as described in http://support.microsoft.com/kb/263712/.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> Thanks for this. It's a sql 2000 box and i had checked the permission
> properties page as you suggested below but it was blank. So it appears
> that
> the these stored procedures are being executed by a user who is not a
> sysadmin and who has not been granted specific execute permissions on the
> procedures.
> Is this possible'
> Thanks
> "Russell Fields" wrote:
>|||Oh, and if the user is db_owner in master he also can execute sp_OA
procedures.
The BOL does not say this is possible, but I just tested it and it does work
on SQL 2000 and 2005. (So is this an 'undocumented' feature or an error in
documentation? I have reported it as an error.)
RLF
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OdHHkzqrHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Neile,
> I don't know what to say about a blank properties page except, "I don't
> know." Of course, the sp_OA procedures can be granted to public, which
> would give everybody rights, but would also result in public having a
> green check box next to it.
> What was the response to: sp_helprotect @.name='sp_OACreate'
> Another possibility is that the user is also in the local administrators
> group of the server. If so, he is by default member of the sysadmin
> server role on the SQL Server database unless steps are taken to prevent
> that. For example, as described in
> http://support.microsoft.com/kb/263712/.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...
>|||Perhaps the cross-database chaining (a.k.a. db_chaining) database option is
turned on. In that case, execute permissions on indirectly referenced
objects in other databases are not needed as long as the ownership chain is
unbroken. Users need execute permissions on only the user stored procedure.
Note that the user database must be owned by 'sa' to maintain an unbroken
chain to master database objects. It is important that only sysadmin users
be allowed to create dbo-owned objects in this scenario in order to prevent
elevation of privileges.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Dan,
Thanks for that reminder. We broke our ownership chains many years ago,
well before SQL 2000 SP3, by giving each database a different owner. It had
faded from memory.
RLF
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
> Perhaps the cross-database chaining (a.k.a. db_chaining) database option
> is turned on. In that case, execute permissions on indirectly referenced
> objects in other databases are not needed as long as the ownership chain
> is unbroken. Users need execute permissions on only the user stored
> procedure.
> Note that the user database must be owned by 'sa' to maintain an unbroken
> chain to master database objects. It is important that only sysadmin
> users be allowed to create dbo-owned objects in this scenario in order to
> prevent elevation of privileges.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>|||Hi Russell
Thanks for all the tips - this is all useful stuff to know. I discovered
that the stored procedure in the database which in turn calls the CDOSys
stored procedures is executed via a SQL job - and thus in the context of the
job owner, which is sa. So that explains it!!
But thanks for your suggestions and thanks to Dan for the info about cross
db chaining - all good to know.
Cheers
Neile
"Russell Fields" wrote:
> Dan,
> Thanks for that reminder. We broke our ownership chains many years ago,
> well before SQL 2000 SP3, by giving each database a different owner. It h
ad
> faded from memory.
> RLF
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
>
>
Wednesday, March 21, 2012
Permissions on stored procedures & tables
At one client site, the DB server has 2 databases, that of my application
and that for another application. The client's consultant has added a
stored procedure which I am to access.
Signed onto my application's DB, I run the query:
Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
I did get an error saying my user wasn't valid on the other DB so I added it
& granted it Execute permissions on MyStoredProc. Now I get error messages
saying:
SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
'OtherUser'
I thought having the right to execute the stored procedure, I shouldn't need
explicit rights to the tables from which it selects.
What do I need to do here? Short of granting myself rights to a bunch of
tables in the other DB?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyI'm not sure what the error with OtherOwner is or if you
ownership chains are intact. Even if they are, with SP3,
cross db ownership chains were introduced. They are off by
default for user databases. You can find more information in
the following article:
INF: Cross-Database Ownership Chaining Behavior Changes in
SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=810474
There is also information in the updated version of books
online under:
Cross DB Ownership Chaining
Using Ownership Chains
or online at:
http://msdn.microsoft.com/library/e...config_8d7m.asp
http://msdn.microsoft.com/library/e...curity_4iyb.asp
-Sue
On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
<d.wilson@.embtrak.com> wrote:
>At one client site, the DB server has 2 databases, that of my application
>and that for another application. The client's consultant has added a
>stored procedure which I am to access.
>Signed onto my application's DB, I run the query:
>Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
>I did get an error saying my user wasn't valid on the other DB so I added i
t
>& granted it Execute permissions on MyStoredProc. Now I get error messages
>saying:
>SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
>'OtherUser'
>I thought having the right to execute the stored procedure, I shouldn't nee
d
>explicit rights to the tables from which it selects.
>What do I need to do here? Short of granting myself rights to a bunch of
>tables in the other DB?
>Thanks.|||Thanks, Sue.
Those, especially the last link, explain the problem. This underscores the
recommendation to let DBO own all objects. Since that's not the case in this
DB, I have to give my user explicit permissions on each view & table.
dwilson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> I'm not sure what the error with OtherOwner is or if you
> ownership chains are intact. Even if they are, with SP3,
> cross db ownership chains were introduced. They are off by
> default for user databases. You can find more information in
> the following article:
> INF: Cross-Database Ownership Chaining Behavior Changes in
> SQL Server 2000 Service Pack 3
> http://support.microsoft.com/?id=810474
> There is also information in the updated version of books
> online under:
> Cross DB Ownership Chaining
> Using Ownership Chains
> or online at:
> http://msdn.microsoft.com/library/e...config_8d7m.asp
> http://msdn.microsoft.com/library/e...curity_4iyb.asp
> -Sue
> On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
> <d.wilson@.embtrak.com> wrote:
>
it[vbcol=seagreen]
messages[vbcol=seagreen]
owner[vbcol=seagreen]
need[vbcol=seagreen]
>|||I feel that the procedure you execute first have only static query and the
second query has dynamic query, and the user you are using is supplied with
execute permission alone at this senario if you are trying to execute the
procedre with dynamic sql it will not work and will thro a error as SELECT
permission denied on object 'table name', database 'database name', owner
'owner name'. So, get the Select permission will solve the problem or modify
the sp by avoiding dynamic query.
"Daniel Wilson" wrote:
> Thanks, Sue.
> Those, especially the last link, explain the problem. This underscores th
e
> recommendation to let DBO own all objects. Since that's not the case in th
is
> DB, I have to give my user explicit permissions on each view & table.
> dwilson
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> it
> messages
> owner
> need
>
>
and that for another application. The client's consultant has added a
stored procedure which I am to access.
Signed onto my application's DB, I run the query:
Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
I did get an error saying my user wasn't valid on the other DB so I added it
& granted it Execute permissions on MyStoredProc. Now I get error messages
saying:
SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
'OtherUser'
I thought having the right to execute the stored procedure, I shouldn't need
explicit rights to the tables from which it selects.
What do I need to do here? Short of granting myself rights to a bunch of
tables in the other DB?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyI'm not sure what the error with OtherOwner is or if you
ownership chains are intact. Even if they are, with SP3,
cross db ownership chains were introduced. They are off by
default for user databases. You can find more information in
the following article:
INF: Cross-Database Ownership Chaining Behavior Changes in
SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=810474
There is also information in the updated version of books
online under:
Cross DB Ownership Chaining
Using Ownership Chains
or online at:
http://msdn.microsoft.com/library/e...config_8d7m.asp
http://msdn.microsoft.com/library/e...curity_4iyb.asp
-Sue
On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
<d.wilson@.embtrak.com> wrote:
>At one client site, the DB server has 2 databases, that of my application
>and that for another application. The client's consultant has added a
>stored procedure which I am to access.
>Signed onto my application's DB, I run the query:
>Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
>I did get an error saying my user wasn't valid on the other DB so I added i
t
>& granted it Execute permissions on MyStoredProc. Now I get error messages
>saying:
>SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
>'OtherUser'
>I thought having the right to execute the stored procedure, I shouldn't nee
d
>explicit rights to the tables from which it selects.
>What do I need to do here? Short of granting myself rights to a bunch of
>tables in the other DB?
>Thanks.|||Thanks, Sue.
Those, especially the last link, explain the problem. This underscores the
recommendation to let DBO own all objects. Since that's not the case in this
DB, I have to give my user explicit permissions on each view & table.
dwilson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> I'm not sure what the error with OtherOwner is or if you
> ownership chains are intact. Even if they are, with SP3,
> cross db ownership chains were introduced. They are off by
> default for user databases. You can find more information in
> the following article:
> INF: Cross-Database Ownership Chaining Behavior Changes in
> SQL Server 2000 Service Pack 3
> http://support.microsoft.com/?id=810474
> There is also information in the updated version of books
> online under:
> Cross DB Ownership Chaining
> Using Ownership Chains
> or online at:
> http://msdn.microsoft.com/library/e...config_8d7m.asp
> http://msdn.microsoft.com/library/e...curity_4iyb.asp
> -Sue
> On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
> <d.wilson@.embtrak.com> wrote:
>
it[vbcol=seagreen]
messages[vbcol=seagreen]
owner[vbcol=seagreen]
need[vbcol=seagreen]
>|||I feel that the procedure you execute first have only static query and the
second query has dynamic query, and the user you are using is supplied with
execute permission alone at this senario if you are trying to execute the
procedre with dynamic sql it will not work and will thro a error as SELECT
permission denied on object 'table name', database 'database name', owner
'owner name'. So, get the Select permission will solve the problem or modify
the sp by avoiding dynamic query.
"Daniel Wilson" wrote:
> Thanks, Sue.
> Those, especially the last link, explain the problem. This underscores th
e
> recommendation to let DBO own all objects. Since that's not the case in th
is
> DB, I have to give my user explicit permissions on each view & table.
> dwilson
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> it
> messages
> owner
> need
>
>
permissions not scripting in 2000
We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.
Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.
|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>
>
|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...[vbcol=seagreen]
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.
Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.
|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>
>
|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...[vbcol=seagreen]
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
permissions not scripting in 2000
We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are havin
g
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch fo
r
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the othe
r
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object i
n
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...[vbcol=seagreen]
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>
server running Windows 2000 Server. Only a couple of the databases are havin
g
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch fo
r
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the othe
r
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object i
n
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...[vbcol=seagreen]
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>
permissions not scripting in 2000
We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> > We have several databases that were migrated to SQL2000 from 7.0 on the
> > same
> > server running Windows 2000 Server. Only a couple of the databases are
> > having
> > a problem that when we script out the stored procedures or any object for
> > that matter (with the script permissions box selected) the object or SP is
> > scripted out but the GRANT permissions portion of the script is omitted.
> > We
> > also cannot see the permissions for the object in the GUI of Enterprise
> > Manager. The other databases work fine on the same server. Is it a switch
> > for
> > this particular database or something? Any help on this matter would be
> > greatly appreciated.
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
--
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>> Did you check "Script object-level permissions" on the Options tab.
>> Also, you can generate scripts via Query Analyzer. It has the options for
>> scripting permissions, too.
>>
>> --
>> -oj
>>
>> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
>> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>> > We have several databases that were migrated to SQL2000 from 7.0 on the
>> > same
>> > server running Windows 2000 Server. Only a couple of the databases are
>> > having
>> > a problem that when we script out the stored procedures or any object
>> > for
>> > that matter (with the script permissions box selected) the object or SP
>> > is
>> > scripted out but the GRANT permissions portion of the script is
>> > omitted.
>> > We
>> > also cannot see the permissions for the object in the GUI of Enterprise
>> > Manager. The other databases work fine on the same server. Is it a
>> > switch
>> > for
>> > this particular database or something? Any help on this matter would be
>> > greatly appreciated.
>>sql
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> > We have several databases that were migrated to SQL2000 from 7.0 on the
> > same
> > server running Windows 2000 Server. Only a couple of the databases are
> > having
> > a problem that when we script out the stored procedures or any object for
> > that matter (with the script permissions box selected) the object or SP is
> > scripted out but the GRANT permissions portion of the script is omitted.
> > We
> > also cannot see the permissions for the object in the GUI of Enterprise
> > Manager. The other databases work fine on the same server. Is it a switch
> > for
> > this particular database or something? Any help on this matter would be
> > greatly appreciated.
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
--
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>> Did you check "Script object-level permissions" on the Options tab.
>> Also, you can generate scripts via Query Analyzer. It has the options for
>> scripting permissions, too.
>>
>> --
>> -oj
>>
>> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
>> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>> > We have several databases that were migrated to SQL2000 from 7.0 on the
>> > same
>> > server running Windows 2000 Server. Only a couple of the databases are
>> > having
>> > a problem that when we script out the stored procedures or any object
>> > for
>> > that matter (with the script permissions box selected) the object or SP
>> > is
>> > scripted out but the GRANT permissions portion of the script is
>> > omitted.
>> > We
>> > also cannot see the permissions for the object in the GUI of Enterprise
>> > Manager. The other databases work fine on the same server. Is it a
>> > switch
>> > for
>> > this particular database or something? Any help on this matter would be
>> > greatly appreciated.
>>sql
Permissions not Saving
I am having a strange problem with certain databases.
Some of the permissions I set for certain tables are not
saving. I right click on the table,move over "All tasks"
and select "Manage Permissions". I tick the permissions I
want and click ok. When I exit and go back in the
permissions I have set are gone.
Can anyone explain.Hi,
Not sure of the issue in Enterprise manager. Can you try giving the
permissions using GRANT statement from Query analyzer.
How to give prev.
GRANT SELECT on table_name to <user_name>
GRANT INSERT on table_name to <User_name>
GRANT UPDATE on table_name to <user_name>
GRANT DELETE on table_name to <User_name>
GRANT ALL on table_name to <user_name>
GRANT INSERT,SELECT on table_name to <User_name>
Procedures
GRANT EXECUTE on proc_name to <user_name>
Have a look into grant statement in books online for more previlage details
Thanks
Hari
MCDBA
"Nobster" <Norbert_Armstrong@.dub.Invesco.com> wrote in message
news:15a6001c44713$30c84b00$a301280a@.phx
.gbl...
> I am having a strange problem with certain databases.
> Some of the permissions I set for certain tables are not
> saving. I right click on the table,move over "All tasks"
> and select "Manage Permissions". I tick the permissions I
> want and click ok. When I exit and go back in the
> permissions I have set are gone.
> Can anyone explain.|||I have seen something similar before. Try testing the permissions in QA as
they may well have been correctly set. In my case when I used sp_helpprotect
or looked directly at sysprotects the permissions had been entered but EM
didn't display them correctly. Some time ago I did once 'debug' this
behaviour using profiler and posted up my findings in the replication group,
but unfortunately a search doesn't reveal them. Anyway if this corresponds
to your situation and you do profile it, please post up your findings.
Regards,
Paul Ibison
Some of the permissions I set for certain tables are not
saving. I right click on the table,move over "All tasks"
and select "Manage Permissions". I tick the permissions I
want and click ok. When I exit and go back in the
permissions I have set are gone.
Can anyone explain.Hi,
Not sure of the issue in Enterprise manager. Can you try giving the
permissions using GRANT statement from Query analyzer.
How to give prev.
GRANT SELECT on table_name to <user_name>
GRANT INSERT on table_name to <User_name>
GRANT UPDATE on table_name to <user_name>
GRANT DELETE on table_name to <User_name>
GRANT ALL on table_name to <user_name>
GRANT INSERT,SELECT on table_name to <User_name>
Procedures
GRANT EXECUTE on proc_name to <user_name>
Have a look into grant statement in books online for more previlage details
Thanks
Hari
MCDBA
"Nobster" <Norbert_Armstrong@.dub.Invesco.com> wrote in message
news:15a6001c44713$30c84b00$a301280a@.phx
.gbl...
> I am having a strange problem with certain databases.
> Some of the permissions I set for certain tables are not
> saving. I right click on the table,move over "All tasks"
> and select "Manage Permissions". I tick the permissions I
> want and click ok. When I exit and go back in the
> permissions I have set are gone.
> Can anyone explain.|||I have seen something similar before. Try testing the permissions in QA as
they may well have been correctly set. In my case when I used sp_helpprotect
or looked directly at sysprotects the permissions had been entered but EM
didn't display them correctly. Some time ago I did once 'debug' this
behaviour using profiler and posted up my findings in the replication group,
but unfortunately a search doesn't reveal them. Anyway if this corresponds
to your situation and you do profile it, please post up your findings.
Regards,
Paul Ibison
Tuesday, March 20, 2012
Permissions Management
Hello,
I am trying to set up permissions to secure databases in our Developement. I
have set up "View any Database" to Deny at the Database level.
when i login as admin i see all the database while the rest see Master and
Temp DB. What do i have to do at the database level so i can make other
databases visible to appropriate personnel and also give them control such as
Create Tables, Update SP etc.
Thanks
MannyManny
If the user is an owner of db try the following
Create a new SQL login "login1"
? Create a user named ?login1? in master database
? Grant CREATE DATABASE to login1
? While impersonating login1, create a database called ?dbteste?
? Revoke CREATE DATABASE permission from login1
? Revoke VIEW ANY DATABASE permission from PUBLIC
? Register this server as login1
? From the ?login1? session, expand database tree. Now, you should see
master, tempdb, dbteste
? Grant VIEW ANY DATABASE to PUBLIC
? From the ?login1? session, you should see all the databases
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny|||So basically the user who is creating the database will be only one who can
view the database in the tree.
What i am trying to do is following:
I have users User1, User2, User3 and Databases as DB1, DB2 and DB3.
I would like to revoke Public from seeing all the databases.
User 1 should have access to DB1 (as DBO) and DB2 as read/write
User 2 should only have access to DB2 (DBO)
User 3 should have access to DB2 (DBO) and DB3 as read only.
Hope i am clear in explaining things
THanks
Manny
"Uri Dimant" wrote:
> Manny
> If the user is an owner of db try the following
> Create a new SQL login "login1"
> â?¢ Create a user named â'login1â' in master database
> â?¢ Grant CREATE DATABASE to login1
> â?¢ While impersonating login1, create a database called â'dbtesteâ'
> â?¢ Revoke CREATE DATABASE permission from login1
> â?¢ Revoke VIEW ANY DATABASE permission from PUBLIC
> â?¢ Register this server as login1
> â?¢ From the â'login1â' session, expand database tree. Now, you should see
> master, tempdb, dbteste
> â?¢ Grant VIEW ANY DATABASE to PUBLIC
> â?¢ From the â'login1â' session, you should see all the databases
>
>
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> > Hello,
> >
> > I am trying to set up permissions to secure databases in our Developement.
> > I
> > have set up "View any Database" to Deny at the Database level.
> >
> > when i login as admin i see all the database while the rest see Master and
> > Temp DB. What do i have to do at the database level so i can make other
> > databases visible to appropriate personnel and also give them control such
> > as
> > Create Tables, Update SP etc.
> >
> > Thanks
> >
> > Manny
>
>|||Manny,
VIEW ANY DATABASE is a server level permission and cannot be granted nor
denied at the database level. To accomplish what you wish to do, you can
deny the permission to the public role (rather than to individual users) and
put users in the db_owner role of the database(s) they should have access.
This would then allow them to see only databases which they have ownership
of.
With regards to granting/revoking DDL permissions, I suggest you try out the
tool SQL CodeSecure. It allows you to set DDL permissions very easily, as
well as audit all changes modifications made to a database and even rollback
changes or recover deleted objects.
You can download the application from this location:
http://www.sql-labs.com/downloads/SQLCodeSecure.zip
HTH.
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny
I am trying to set up permissions to secure databases in our Developement. I
have set up "View any Database" to Deny at the Database level.
when i login as admin i see all the database while the rest see Master and
Temp DB. What do i have to do at the database level so i can make other
databases visible to appropriate personnel and also give them control such as
Create Tables, Update SP etc.
Thanks
MannyManny
If the user is an owner of db try the following
Create a new SQL login "login1"
? Create a user named ?login1? in master database
? Grant CREATE DATABASE to login1
? While impersonating login1, create a database called ?dbteste?
? Revoke CREATE DATABASE permission from login1
? Revoke VIEW ANY DATABASE permission from PUBLIC
? Register this server as login1
? From the ?login1? session, expand database tree. Now, you should see
master, tempdb, dbteste
? Grant VIEW ANY DATABASE to PUBLIC
? From the ?login1? session, you should see all the databases
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny|||So basically the user who is creating the database will be only one who can
view the database in the tree.
What i am trying to do is following:
I have users User1, User2, User3 and Databases as DB1, DB2 and DB3.
I would like to revoke Public from seeing all the databases.
User 1 should have access to DB1 (as DBO) and DB2 as read/write
User 2 should only have access to DB2 (DBO)
User 3 should have access to DB2 (DBO) and DB3 as read only.
Hope i am clear in explaining things
THanks
Manny
"Uri Dimant" wrote:
> Manny
> If the user is an owner of db try the following
> Create a new SQL login "login1"
> â?¢ Create a user named â'login1â' in master database
> â?¢ Grant CREATE DATABASE to login1
> â?¢ While impersonating login1, create a database called â'dbtesteâ'
> â?¢ Revoke CREATE DATABASE permission from login1
> â?¢ Revoke VIEW ANY DATABASE permission from PUBLIC
> â?¢ Register this server as login1
> â?¢ From the â'login1â' session, expand database tree. Now, you should see
> master, tempdb, dbteste
> â?¢ Grant VIEW ANY DATABASE to PUBLIC
> â?¢ From the â'login1â' session, you should see all the databases
>
>
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> > Hello,
> >
> > I am trying to set up permissions to secure databases in our Developement.
> > I
> > have set up "View any Database" to Deny at the Database level.
> >
> > when i login as admin i see all the database while the rest see Master and
> > Temp DB. What do i have to do at the database level so i can make other
> > databases visible to appropriate personnel and also give them control such
> > as
> > Create Tables, Update SP etc.
> >
> > Thanks
> >
> > Manny
>
>|||Manny,
VIEW ANY DATABASE is a server level permission and cannot be granted nor
denied at the database level. To accomplish what you wish to do, you can
deny the permission to the public role (rather than to individual users) and
put users in the db_owner role of the database(s) they should have access.
This would then allow them to see only databases which they have ownership
of.
With regards to granting/revoking DDL permissions, I suggest you try out the
tool SQL CodeSecure. It allows you to set DDL permissions very easily, as
well as audit all changes modifications made to a database and even rollback
changes or recover deleted objects.
You can download the application from this location:
http://www.sql-labs.com/downloads/SQLCodeSecure.zip
HTH.
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny
Monday, March 12, 2012
Permissions and privileges
Hi
I would like to have a list of all those who have access to my
databases and what privileges they have.
If any one has something in the same lines that could be shared it
will be very much appreciated
VinceMost of the web sites have scripts for this. SQL Server
Central has several - these are links to some of the
scripts:
http://www.sqlservercentral.com/scripts/contributions/263.asp
http://www.sqlservercentral.com/scripts/contributions/573.asp
http://www.sqlservercentral.com/scripts/contributions/268.asp
-Sue
On 4 Sep 2003 10:44:02 -0700, wumutek@.yahoo.com (Vincento
Harris) wrote:
>Hi
>I would like to have a list of all those who have access to my
>databases and what privileges they have.
>If any one has something in the same lines that could be shared it
>will be very much appreciated
>
>Vince|||Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<jkjhlv0bidmj1okqgelfjijbks41sgc0et@.4ax.com>...
> Most of the web sites have scripts for this. SQL Server
> Central has several - these are links to some of the
> scripts:
> http://www.sqlservercentral.com/scripts/contributions/263.asp
> http://www.sqlservercentral.com/scripts/contributions/573.asp
> http://www.sqlservercentral.com/scripts/contributions/268.asp
> -Sue
> On 4 Sep 2003 10:44:02 -0700, wumutek@.yahoo.com (Vincento
> Harris) wrote:
> >Hi
> >I would like to have a list of all those who have access to my
> >databases and what privileges they have.
> >If any one has something in the same lines that could be shared it
> >will be very much appreciated
> >
> >
> >Vince
Thanks Sue now I know the sp_helprotect procedure is out there.
The script did not seem to give the information I needed.
I will still be interested in a script that lists ALL users and the
permissions they have in the database.
Will greatly appreciate your expert directions
Vincentos|||Use the example in the first link:
http://www.sqlservercentral.com/scripts/contributions/263.asp
and don't pass in a user name. It will go through all the
users in the database.
-Sue
On 8 Sep 2003 11:29:56 -0700, wumutek@.yahoo.com (Vincento
Harris) wrote:
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<jkjhlv0bidmj1okqgelfjijbks41sgc0et@.4ax.com>...
>> Most of the web sites have scripts for this. SQL Server
>> Central has several - these are links to some of the
>> scripts:
>> http://www.sqlservercentral.com/scripts/contributions/263.asp
>> http://www.sqlservercentral.com/scripts/contributions/573.asp
>> http://www.sqlservercentral.com/scripts/contributions/268.asp
>> -Sue
>> On 4 Sep 2003 10:44:02 -0700, wumutek@.yahoo.com (Vincento
>> Harris) wrote:
>> >Hi
>> >I would like to have a list of all those who have access to my
>> >databases and what privileges they have.
>> >If any one has something in the same lines that could be shared it
>> >will be very much appreciated
>> >
>> >
>> >Vince
>Thanks Sue now I know the sp_helprotect procedure is out there.
>The script did not seem to give the information I needed.
>I will still be interested in a script that lists ALL users and the
>permissions they have in the database.
>Will greatly appreciate your expert directions
>
>Vincentos|||Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<nt5slv8t2643549b0datshnbl3tuq3b7ma@.4ax.com>...
> Use the example in the first link:
> http://www.sqlservercentral.com/scripts/contributions/263.asp
> and don't pass in a user name. It will go through all the
> users in the database.
> -Sue
> On 8 Sep 2003 11:29:56 -0700, wumutek@.yahoo.com (Vincento
> Harris) wrote:
> >Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<jkjhlv0bidmj1okqgelfjijbks41sgc0et@.4ax.com>...
> >> Most of the web sites have scripts for this. SQL Server
> >> Central has several - these are links to some of the
> >> scripts:
> >> http://www.sqlservercentral.com/scripts/contributions/263.asp
> >> http://www.sqlservercentral.com/scripts/contributions/573.asp
> >> http://www.sqlservercentral.com/scripts/contributions/268.asp
This seemed very useful thanks to Randy dyess
http://www.transactsql.com/Articles/NEPII.html
Vince
I would like to have a list of all those who have access to my
databases and what privileges they have.
If any one has something in the same lines that could be shared it
will be very much appreciated
VinceMost of the web sites have scripts for this. SQL Server
Central has several - these are links to some of the
scripts:
http://www.sqlservercentral.com/scripts/contributions/263.asp
http://www.sqlservercentral.com/scripts/contributions/573.asp
http://www.sqlservercentral.com/scripts/contributions/268.asp
-Sue
On 4 Sep 2003 10:44:02 -0700, wumutek@.yahoo.com (Vincento
Harris) wrote:
>Hi
>I would like to have a list of all those who have access to my
>databases and what privileges they have.
>If any one has something in the same lines that could be shared it
>will be very much appreciated
>
>Vince|||Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<jkjhlv0bidmj1okqgelfjijbks41sgc0et@.4ax.com>...
> Most of the web sites have scripts for this. SQL Server
> Central has several - these are links to some of the
> scripts:
> http://www.sqlservercentral.com/scripts/contributions/263.asp
> http://www.sqlservercentral.com/scripts/contributions/573.asp
> http://www.sqlservercentral.com/scripts/contributions/268.asp
> -Sue
> On 4 Sep 2003 10:44:02 -0700, wumutek@.yahoo.com (Vincento
> Harris) wrote:
> >Hi
> >I would like to have a list of all those who have access to my
> >databases and what privileges they have.
> >If any one has something in the same lines that could be shared it
> >will be very much appreciated
> >
> >
> >Vince
Thanks Sue now I know the sp_helprotect procedure is out there.
The script did not seem to give the information I needed.
I will still be interested in a script that lists ALL users and the
permissions they have in the database.
Will greatly appreciate your expert directions
Vincentos|||Use the example in the first link:
http://www.sqlservercentral.com/scripts/contributions/263.asp
and don't pass in a user name. It will go through all the
users in the database.
-Sue
On 8 Sep 2003 11:29:56 -0700, wumutek@.yahoo.com (Vincento
Harris) wrote:
>Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<jkjhlv0bidmj1okqgelfjijbks41sgc0et@.4ax.com>...
>> Most of the web sites have scripts for this. SQL Server
>> Central has several - these are links to some of the
>> scripts:
>> http://www.sqlservercentral.com/scripts/contributions/263.asp
>> http://www.sqlservercentral.com/scripts/contributions/573.asp
>> http://www.sqlservercentral.com/scripts/contributions/268.asp
>> -Sue
>> On 4 Sep 2003 10:44:02 -0700, wumutek@.yahoo.com (Vincento
>> Harris) wrote:
>> >Hi
>> >I would like to have a list of all those who have access to my
>> >databases and what privileges they have.
>> >If any one has something in the same lines that could be shared it
>> >will be very much appreciated
>> >
>> >
>> >Vince
>Thanks Sue now I know the sp_helprotect procedure is out there.
>The script did not seem to give the information I needed.
>I will still be interested in a script that lists ALL users and the
>permissions they have in the database.
>Will greatly appreciate your expert directions
>
>Vincentos|||Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<nt5slv8t2643549b0datshnbl3tuq3b7ma@.4ax.com>...
> Use the example in the first link:
> http://www.sqlservercentral.com/scripts/contributions/263.asp
> and don't pass in a user name. It will go through all the
> users in the database.
> -Sue
> On 8 Sep 2003 11:29:56 -0700, wumutek@.yahoo.com (Vincento
> Harris) wrote:
> >Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<jkjhlv0bidmj1okqgelfjijbks41sgc0et@.4ax.com>...
> >> Most of the web sites have scripts for this. SQL Server
> >> Central has several - these are links to some of the
> >> scripts:
> >> http://www.sqlservercentral.com/scripts/contributions/263.asp
> >> http://www.sqlservercentral.com/scripts/contributions/573.asp
> >> http://www.sqlservercentral.com/scripts/contributions/268.asp
This seemed very useful thanks to Randy dyess
http://www.transactsql.com/Articles/NEPII.html
Vince
Permissions and ODBC
There are 2 SQL databases involved and an MS Access program... also using
windows authentication...
The Ms Access program executes a stored procedure that resides in SQL
database#1, the stored procedure updates data in SQL dataabase#2..
Assume the user has rights to execute the stored procedure in database#1.
How can you best limit the rights of the user to database#2, but still allow
them to execute the stored procedure in database#1 that updates the data in
database#2 ?Permissions on indirectly referenced objects are not needed as long as the
objects have the same owner (i.e. owner maps to the same login). In the
case of dbo-owned objects in different databases, the databases need to have
the same owner so that the dbo user maps to the same login. You can change
database owners using sp_changedbowner, if needed. Also, cross-database
ownership chaining is a configurable option in SQL 2000 SP3 and needs to be
enabled in both databases. You can enable this using sp_dboption:
EXEC sp_dboption 'Database1', 'db chaining', true
EXEC sp_dboption 'Database2', 'db chaining', true
The main security consideration with cross-database chaining is that you
should enable the option only if you trust users with object CREATE
permissions in those databases. See the SQL 2003 SP3 Books Online for more
info.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rob" <rwc1960@.bellsouth.net> wrote in message
news:MYW0d.113845$0o5.105823@.bignews1.bellsouth.net...
> There are 2 SQL databases involved and an MS Access program... also using
> windows authentication...
> The Ms Access program executes a stored procedure that resides in SQL
> database#1, the stored procedure updates data in SQL dataabase#2..
> Assume the user has rights to execute the stored procedure in database#1.
> How can you best limit the rights of the user to database#2, but still
> allow
> them to execute the stored procedure in database#1 that updates the data
> in
> database#2 ?
>
>
>
windows authentication...
The Ms Access program executes a stored procedure that resides in SQL
database#1, the stored procedure updates data in SQL dataabase#2..
Assume the user has rights to execute the stored procedure in database#1.
How can you best limit the rights of the user to database#2, but still allow
them to execute the stored procedure in database#1 that updates the data in
database#2 ?Permissions on indirectly referenced objects are not needed as long as the
objects have the same owner (i.e. owner maps to the same login). In the
case of dbo-owned objects in different databases, the databases need to have
the same owner so that the dbo user maps to the same login. You can change
database owners using sp_changedbowner, if needed. Also, cross-database
ownership chaining is a configurable option in SQL 2000 SP3 and needs to be
enabled in both databases. You can enable this using sp_dboption:
EXEC sp_dboption 'Database1', 'db chaining', true
EXEC sp_dboption 'Database2', 'db chaining', true
The main security consideration with cross-database chaining is that you
should enable the option only if you trust users with object CREATE
permissions in those databases. See the SQL 2003 SP3 Books Online for more
info.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rob" <rwc1960@.bellsouth.net> wrote in message
news:MYW0d.113845$0o5.105823@.bignews1.bellsouth.net...
> There are 2 SQL databases involved and an MS Access program... also using
> windows authentication...
> The Ms Access program executes a stored procedure that resides in SQL
> database#1, the stored procedure updates data in SQL dataabase#2..
> Assume the user has rights to execute the stored procedure in database#1.
> How can you best limit the rights of the user to database#2, but still
> allow
> them to execute the stored procedure in database#1 that updates the data
> in
> database#2 ?
>
>
>
Permissions and ODBC
There are 2 SQL databases involved and an MS Access program... also using
windows authentication...
The Ms Access program executes a stored procedure that resides in SQL
database#1, the stored procedure updates data in SQL dataabase#2..
Assume the user has rights to execute the stored procedure in database#1,
how can you best limit the rights of the user to database#2, but still allow
them to execute the stored procedure in database#1 that updates the data in
database#2 ?
If you have SP3 installed (and you should have), make sure you
understand the rules for cross-database ownership chaining, as
explained in SQL Books Online. You can download the latest version
from http://www.microsoft.com/sql/techinf...000/books.asp.
In general, if you confine all data operations to stored procedures,
you only need to grant Execute permissions on the stored procedures
while revoking or denying select, update, insert and delete
permissions on the base tables/views to the public role so that the
permissions can't be inherited. Creating parameterized stored
procedures and staying away from dynamic SQL statements are two
techniques to reduce the attack surface of your application.
--Mary
On Sat, 11 Sep 2004 22:40:59 -0400, "rob" <rwc1960@.bellsouth.net>
wrote:
>There are 2 SQL databases involved and an MS Access program... also using
>windows authentication...
>The Ms Access program executes a stored procedure that resides in SQL
>database#1, the stored procedure updates data in SQL dataabase#2..
>Assume the user has rights to execute the stored procedure in database#1,
>how can you best limit the rights of the user to database#2, but still allow
>them to execute the stored procedure in database#1 that updates the data in
>database#2 ?
>
>
windows authentication...
The Ms Access program executes a stored procedure that resides in SQL
database#1, the stored procedure updates data in SQL dataabase#2..
Assume the user has rights to execute the stored procedure in database#1,
how can you best limit the rights of the user to database#2, but still allow
them to execute the stored procedure in database#1 that updates the data in
database#2 ?
If you have SP3 installed (and you should have), make sure you
understand the rules for cross-database ownership chaining, as
explained in SQL Books Online. You can download the latest version
from http://www.microsoft.com/sql/techinf...000/books.asp.
In general, if you confine all data operations to stored procedures,
you only need to grant Execute permissions on the stored procedures
while revoking or denying select, update, insert and delete
permissions on the base tables/views to the public role so that the
permissions can't be inherited. Creating parameterized stored
procedures and staying away from dynamic SQL statements are two
techniques to reduce the attack surface of your application.
--Mary
On Sat, 11 Sep 2004 22:40:59 -0400, "rob" <rwc1960@.bellsouth.net>
wrote:
>There are 2 SQL databases involved and an MS Access program... also using
>windows authentication...
>The Ms Access program executes a stored procedure that resides in SQL
>database#1, the stored procedure updates data in SQL dataabase#2..
>Assume the user has rights to execute the stored procedure in database#1,
>how can you best limit the rights of the user to database#2, but still allow
>them to execute the stored procedure in database#1 that updates the data in
>database#2 ?
>
>
Permissions and ODBC
There are 2 SQL databases involved and an MS Access program... also using
windows authentication...
The Ms Access program executes a stored procedure that resides in SQL
database#1, the stored procedure updates data in SQL dataabase#2..
Assume the user has rights to execute the stored procedure in database#1.
How can you best limit the rights of the user to database#2, but still allow
them to execute the stored procedure in database#1 that updates the data in
database#2 ?
Permissions on indirectly referenced objects are not needed as long as the
objects have the same owner (i.e. owner maps to the same login). In the
case of dbo-owned objects in different databases, the databases need to have
the same owner so that the dbo user maps to the same login. You can change
database owners using sp_changedbowner, if needed. Also, cross-database
ownership chaining is a configurable option in SQL 2000 SP3 and needs to be
enabled in both databases. You can enable this using sp_dboption:
EXEC sp_dboption 'Database1', 'db chaining', true
EXEC sp_dboption 'Database2', 'db chaining', true
The main security consideration with cross-database chaining is that you
should enable the option only if you trust users with object CREATE
permissions in those databases. See the SQL 2003 SP3 Books Online for more
info.
Hope this helps.
Dan Guzman
SQL Server MVP
"rob" <rwc1960@.bellsouth.net> wrote in message
news:MYW0d.113845$0o5.105823@.bignews1.bellsouth.ne t...
> There are 2 SQL databases involved and an MS Access program... also using
> windows authentication...
> The Ms Access program executes a stored procedure that resides in SQL
> database#1, the stored procedure updates data in SQL dataabase#2..
> Assume the user has rights to execute the stored procedure in database#1.
> How can you best limit the rights of the user to database#2, but still
> allow
> them to execute the stored procedure in database#1 that updates the data
> in
> database#2 ?
>
>
>
windows authentication...
The Ms Access program executes a stored procedure that resides in SQL
database#1, the stored procedure updates data in SQL dataabase#2..
Assume the user has rights to execute the stored procedure in database#1.
How can you best limit the rights of the user to database#2, but still allow
them to execute the stored procedure in database#1 that updates the data in
database#2 ?
Permissions on indirectly referenced objects are not needed as long as the
objects have the same owner (i.e. owner maps to the same login). In the
case of dbo-owned objects in different databases, the databases need to have
the same owner so that the dbo user maps to the same login. You can change
database owners using sp_changedbowner, if needed. Also, cross-database
ownership chaining is a configurable option in SQL 2000 SP3 and needs to be
enabled in both databases. You can enable this using sp_dboption:
EXEC sp_dboption 'Database1', 'db chaining', true
EXEC sp_dboption 'Database2', 'db chaining', true
The main security consideration with cross-database chaining is that you
should enable the option only if you trust users with object CREATE
permissions in those databases. See the SQL 2003 SP3 Books Online for more
info.
Hope this helps.
Dan Guzman
SQL Server MVP
"rob" <rwc1960@.bellsouth.net> wrote in message
news:MYW0d.113845$0o5.105823@.bignews1.bellsouth.ne t...
> There are 2 SQL databases involved and an MS Access program... also using
> windows authentication...
> The Ms Access program executes a stored procedure that resides in SQL
> database#1, the stored procedure updates data in SQL dataabase#2..
> Assume the user has rights to execute the stored procedure in database#1.
> How can you best limit the rights of the user to database#2, but still
> allow
> them to execute the stored procedure in database#1 that updates the data
> in
> database#2 ?
>
>
>
Friday, March 9, 2012
Permissions
How can i Deny access to my domain users to my sql databases and grant acces
s
only to some users. If a make Deny to all users in domain and if a create th
e
necessary users with Grant Access to some databases, the can access the
databases?
Best RegardsCreate a group and add the users you want tio deny access
Take a look at below stored procedure in the BOL.
sp_revokedbaccess [ @.name_in_db = ] 'name'
"icrp@.msn.com" <icrpmsncom@.discussions.microsoft.com> wrote in message
news:9F945ED7-1C19-44CC-A15E-C44D30CCEB65@.microsoft.com...
> How can i Deny access to my domain users to my sql databases and grant
> access
> only to some users. If a make Deny to all users in domain and if a create
> the
> necessary users with Grant Access to some databases, the can access the
> databases?
> Best Regards|||Hi,
what happen if you create seprate user and assign each of them a
permissions , it will be hectic if # users are large , rather if you create
a
ROLE(department wise) and then assign them a proper permissions and then map
a user / group ( it could be sql or windows user/group) . it will save your
time and purpose tooo.
HTH
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Uri Dimant" wrote:
> Create a group and add the users you want tio deny access
> Take a look at below stored procedure in the BOL.
> sp_revokedbaccess [ @.name_in_db = ] 'name'
>
> "icrp@.msn.com" <icrpmsncom@.discussions.microsoft.com> wrote in message
> news:9F945ED7-1C19-44CC-A15E-C44D30CCEB65@.microsoft.com...
>
>
s
only to some users. If a make Deny to all users in domain and if a create th
e
necessary users with Grant Access to some databases, the can access the
databases?
Best RegardsCreate a group and add the users you want tio deny access
Take a look at below stored procedure in the BOL.
sp_revokedbaccess [ @.name_in_db = ] 'name'
"icrp@.msn.com" <icrpmsncom@.discussions.microsoft.com> wrote in message
news:9F945ED7-1C19-44CC-A15E-C44D30CCEB65@.microsoft.com...
> How can i Deny access to my domain users to my sql databases and grant
> access
> only to some users. If a make Deny to all users in domain and if a create
> the
> necessary users with Grant Access to some databases, the can access the
> databases?
> Best Regards|||Hi,
what happen if you create seprate user and assign each of them a
permissions , it will be hectic if # users are large , rather if you create
a
ROLE(department wise) and then assign them a proper permissions and then map
a user / group ( it could be sql or windows user/group) . it will save your
time and purpose tooo.
HTH
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Uri Dimant" wrote:
> Create a group and add the users you want tio deny access
> Take a look at below stored procedure in the BOL.
> sp_revokedbaccess [ @.name_in_db = ] 'name'
>
> "icrp@.msn.com" <icrpmsncom@.discussions.microsoft.com> wrote in message
> news:9F945ED7-1C19-44CC-A15E-C44D30CCEB65@.microsoft.com...
>
>
Wednesday, March 7, 2012
PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE
I tried to create a sp on one of the databases on my lap top and got this: Pls help i need it bad
Msg 10314, Level 16, State 11, Procedure ap_Hello, Line 5
An error occurred in the Microsoft .NET Framework while trying to load
assembly id 65695. The server may be running out of resources, or the
assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or
UNSAFE. Run the query again, or check documentation to see how to solve
the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly
'vbtriggers, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or
one of its dependencies. An error relating to security occurred.
(Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String
codeBase, Evidence assemblySecurity, Assembly locationHint,
StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean
forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyNa me assemblyRef,
Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean
forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString,
Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean
forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
The statement has been terminated.
Please post the CLR code here. Do you use reflection or an Assembly load function within the CLR code ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
-|||Ok what clr code?
I downloaded this db from a book website (not recomending the book at this time)
it was a rar file i unziped and ran a script to attach. I think it has to be on a network, im not sure
EXEC sp_attach_db 'Asset5',
C:\......Asset5.mdf',
C:\......Asset5.ldf'
Pls help asap i bought the book to cram for an interview and I cant tell you where i would like to cram the book now|||Please post the script you are using to create the ap_Hello SP here.|||Create proc ap_Hello
AS
Declare @.myVar char(5)
SET @.MyVar='Hello'
SELECT @.MyVar
Simple as can be, this works on all other dbs on this laptop except for the one installed from the book, im not sure but seems the book says you have to be running it on a server not locally can that be the prob?
Also i used the scripts for a few tables to recreate them in a new db on the same laptop then selected from the book db into my new db and ran the a few sp from the book and they worked, it is this Asset5 db only that i have trouble with|||
So you encounter the problem when creating the SP? It looks like that there is DDL trigger, named vbtriggers, defined in the database but failed to be loaded correctly.
Please open Management Studio and click Database -> Your database -> Programmablity -> Database Triggers, and see if there is any defined. If any, script them out so that you can re-create them if necessary and then delete all the database triggers.
After this, please try creating the SP again. What will be the result?
|||EXCELLENT!!!!!!!!!! the vbTrigger is an Assembly, i had to drop a trig that depended on it so i scripted that also, when i tried to create the assembly agian in go a huge error list, but i guess i have about a month of studying until i get there so no worries.THANK YOU VERY MUCH!!!!!!!
Permission to start/stop logreader and distribution agent
Hi all!
SQL Server 7.0, NT 4.0
I have distribuition server, whose only role is to be a
distributor, there are no user databases on it. I want to
create login on that server, a login which can only check
the status of logreaders and distribution agents, and to
start or stop them, everyrhing in EM Console - Replication
Monitor. Some users will be able to register Distributor
server in their EM Console using this login.
Does anybody have an idea how to do this?
TIA!
ArminFrom BOL:
users that are not members of the sysadmin fixed server role can monitor
replication if they are defined with the replmonitor role in the
distribution database. A system administrator can add any user to the
replmonitor role, which allows that user to view replication activity with
the Replication Monitor node in SQL Server Enterprise Manager; however, the
user will not be able to administer replication.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thank you very much for your answer, Kevin!
But I have a problem - there is no replmonitor role in
distribution database, at least I can not see it. Maybe
this role exists in SQL2K, not in SQL 7.0?
Best regards,
Armin
role can monitor
in the
user to the
replication activity with
Manager; however, the
confers no rights.
SQL Server 7.0, NT 4.0
I have distribuition server, whose only role is to be a
distributor, there are no user databases on it. I want to
create login on that server, a login which can only check
the status of logreaders and distribution agents, and to
start or stop them, everyrhing in EM Console - Replication
Monitor. Some users will be able to register Distributor
server in their EM Console using this login.
Does anybody have an idea how to do this?
TIA!
ArminFrom BOL:
users that are not members of the sysadmin fixed server role can monitor
replication if they are defined with the replmonitor role in the
distribution database. A system administrator can add any user to the
replmonitor role, which allows that user to view replication activity with
the Replication Monitor node in SQL Server Enterprise Manager; however, the
user will not be able to administer replication.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thank you very much for your answer, Kevin!
But I have a problem - there is no replmonitor role in
distribution database, at least I can not see it. Maybe
this role exists in SQL2K, not in SQL 7.0?
Best regards,
Armin
quote:
>--Original Message--
>From BOL:
>users that are not members of the sysadmin fixed server
role can monitor
quote:
>replication if they are defined with the replmonitor role
in the
quote:
>distribution database. A system administrator can add any
user to the
quote:
>replmonitor role, which allows that user to view
replication activity with
quote:
>the Replication Monitor node in SQL Server Enterprise
Manager; however, the
quote:
>user will not be able to administer replication.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
quote:
>
>.
>
Subscribe to:
Posts (Atom)