SQL2K sp4
Is there a way to get a list of all assigned permissions in a DB? I'd like
to see it in a format like this:
Database X
Object Name Roles Users
Object X role X user1
user2
user 3
role Y user 1
user 3
user 25
etc
I came up with this, but it will not differentiate between Grants and
Denies, nor will it be formatted the way I need:
select so.Name ObjectName, su.Name UserName
from sysObjects so
inner join sysPermissions sp on so.id = sp.id
inner join sysUsers su on sp.grantee = su.uid
where so.name not like 'dt_%' and so.name not like 'sys%'
order by 1,2
TIA, ChrisRThis should give you the info you need, but you may need to rearrange the
output format.
(NOTE: The WHERE clause at the end just prevents listing the built-in
database roles, such as, db_owner)
select Users.UserName, Users.UserType, Users.SQLRoleName,
ObjPerm.Permission, ObjPerm.ObjName
from
(
select U.name UserName,
case when U.isntname = 1 and U.isntgroup <> 1 then 'NTuser'
when U.isntgroup = 1 then 'NTgroup'
when U.issqluser = 1 then 'SQLuser'
when U.issqlrole = 1 then 'SQLrole' end UserType, '' [SQLRoleName]
from dbo.sysusers U
union
select U.name, 'MemberOf' UserType, G.name [SQLRoleName]
from dbo.sysmembers M inner join dbo.sysusers U
on M.memberuid = U.uid
inner join dbo.sysusers G
on M.groupuid = G.uid
) Users
full join
(
select O.name ObjName, U.name UserName, (TY.name + ' ' + AC.name) Permission
from dbo.sysprotects PR inner join master.dbo.spt_values AC on PR.action =
AC.number
inner join master.dbo.spt_values TY on PR.protecttype = TY.number
inner join dbo.sysobjects O on PR.id = O.id
inner join dbo.sysusers U on PR.uid = U.uid
where AC.type = 'T' and TY.type = 'T'
) ObjPerm
on (Users.UserName = ObjPerm.UserName or Users.SQLRoleName = ObjPerm.UserNam
e)
where Users.UserName not like('db_%')
--
DJanson
"ChrisR" wrote:
> SQL2K sp4
> Is there a way to get a list of all assigned permissions in a DB? I'd like
> to see it in a format like this:
> Database X
> Object Name Roles Users
> Object X role X user1
> user2
> user 3
> role Y user 1
> user 3
> user 25
> etc
>
> I came up with this, but it will not differentiate between Grants and
> Denies, nor will it be formatted the way I need:
> select so.Name ObjectName, su.Name UserName
> from sysObjects so
> inner join sysPermissions sp on so.id = sp.id
> inner join sysUsers su on sp.grantee = su.uid
> where so.name not like 'dt_%' and so.name not like 'sys%'
> order by 1,2
> TIA, ChrisR
>
Showing posts with label permissions. Show all posts
Showing posts with label permissions. Show all posts
Monday, March 19, 2012
Dump of SQL permissions?
SQL2K sp4
Is there a way to get a list of all assigned permissions in a DB? I'd like
to see it in a format like this:
Database X
Object Name Roles Users
Object X role X user1
user2
user 3
role Y user 1
user 3
user 25
etc
I came up with this, but it will not differentiate between Grants and
Denies, nor will it be formatted the way I need:
select so.Name ObjectName, su.Name UserName
from sysObjects so
inner join sysPermissions sp on so.id = sp.id
inner join sysUsers su on sp.grantee = su.uid
where so.name not like 'dt_%' and so.name not like 'sys%'
order by 1,2
TIA, ChrisRThis should give you the info you need, but you may need to rearrange the
output format.
(NOTE: The WHERE clause at the end just prevents listing the built-in
database roles, such as, db_owner)
select Users.UserName, Users.UserType, Users.SQLRoleName,
ObjPerm.Permission, ObjPerm.ObjName
from
(
select U.name UserName,
case when U.isntname = 1 and U.isntgroup <> 1 then 'NTuser'
when U.isntgroup = 1 then 'NTgroup'
when U.issqluser = 1 then 'SQLuser'
when U.issqlrole = 1 then 'SQLrole' end UserType, '' [SQLRoleName]
from dbo.sysusers U
union
select U.name, 'MemberOf' UserType, G.name [SQLRoleName]
from dbo.sysmembers M inner join dbo.sysusers U
on M.memberuid = U.uid
inner join dbo.sysusers G
on M.groupuid = G.uid
) Users
full join
(
select O.name ObjName, U.name UserName, (TY.name + ' ' + AC.name) Permission
from dbo.sysprotects PR inner join master.dbo.spt_values AC on PR.action =AC.number
inner join master.dbo.spt_values TY on PR.protecttype = TY.number
inner join dbo.sysobjects O on PR.id = O.id
inner join dbo.sysusers U on PR.uid = U.uid
where AC.type = 'T' and TY.type = 'T'
) ObjPerm
on (Users.UserName = ObjPerm.UserName or Users.SQLRoleName = ObjPerm.UserName)
where Users.UserName not like('db_%')
--
DJanson
"ChrisR" wrote:
> SQL2K sp4
> Is there a way to get a list of all assigned permissions in a DB? I'd like
> to see it in a format like this:
> Database X
> Object Name Roles Users
> Object X role X user1
> user2
> user 3
> role Y user 1
> user 3
> user 25
> etc
>
> I came up with this, but it will not differentiate between Grants and
> Denies, nor will it be formatted the way I need:
> select so.Name ObjectName, su.Name UserName
> from sysObjects so
> inner join sysPermissions sp on so.id = sp.id
> inner join sysUsers su on sp.grantee = su.uid
> where so.name not like 'dt_%' and so.name not like 'sys%'
> order by 1,2
> TIA, ChrisR
>
Is there a way to get a list of all assigned permissions in a DB? I'd like
to see it in a format like this:
Database X
Object Name Roles Users
Object X role X user1
user2
user 3
role Y user 1
user 3
user 25
etc
I came up with this, but it will not differentiate between Grants and
Denies, nor will it be formatted the way I need:
select so.Name ObjectName, su.Name UserName
from sysObjects so
inner join sysPermissions sp on so.id = sp.id
inner join sysUsers su on sp.grantee = su.uid
where so.name not like 'dt_%' and so.name not like 'sys%'
order by 1,2
TIA, ChrisRThis should give you the info you need, but you may need to rearrange the
output format.
(NOTE: The WHERE clause at the end just prevents listing the built-in
database roles, such as, db_owner)
select Users.UserName, Users.UserType, Users.SQLRoleName,
ObjPerm.Permission, ObjPerm.ObjName
from
(
select U.name UserName,
case when U.isntname = 1 and U.isntgroup <> 1 then 'NTuser'
when U.isntgroup = 1 then 'NTgroup'
when U.issqluser = 1 then 'SQLuser'
when U.issqlrole = 1 then 'SQLrole' end UserType, '' [SQLRoleName]
from dbo.sysusers U
union
select U.name, 'MemberOf' UserType, G.name [SQLRoleName]
from dbo.sysmembers M inner join dbo.sysusers U
on M.memberuid = U.uid
inner join dbo.sysusers G
on M.groupuid = G.uid
) Users
full join
(
select O.name ObjName, U.name UserName, (TY.name + ' ' + AC.name) Permission
from dbo.sysprotects PR inner join master.dbo.spt_values AC on PR.action =AC.number
inner join master.dbo.spt_values TY on PR.protecttype = TY.number
inner join dbo.sysobjects O on PR.id = O.id
inner join dbo.sysusers U on PR.uid = U.uid
where AC.type = 'T' and TY.type = 'T'
) ObjPerm
on (Users.UserName = ObjPerm.UserName or Users.SQLRoleName = ObjPerm.UserName)
where Users.UserName not like('db_%')
--
DJanson
"ChrisR" wrote:
> SQL2K sp4
> Is there a way to get a list of all assigned permissions in a DB? I'd like
> to see it in a format like this:
> Database X
> Object Name Roles Users
> Object X role X user1
> user2
> user 3
> role Y user 1
> user 3
> user 25
> etc
>
> I came up with this, but it will not differentiate between Grants and
> Denies, nor will it be formatted the way I need:
> select so.Name ObjectName, su.Name UserName
> from sysObjects so
> inner join sysPermissions sp on so.id = sp.id
> inner join sysUsers su on sp.grantee = su.uid
> where so.name not like 'dt_%' and so.name not like 'sys%'
> order by 1,2
> TIA, ChrisR
>
Sunday, March 11, 2012
dumb question
Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
2000. I have used Enterprise manager to create/manage users/permissions and
replication before. I am however not familiar with MSDE. I am using MSDE
because I'm playing with asp.net. Since there is no user interface I am
lost. I created my tables and stored procedures via the Web Matrix asp.net
development tool.
1) I need to configure the MSDE 2000 installation user access. Its currently
setup for integrated windows authentication, which I want to keep. But how
do I add/manage the users and their permissions. For example with Enterprise
Manager, even with Integrated authentication, you have to add the
users/groups from your windows domain before SQL server can use them. Do I
need to do this in MSDE? how?
2) how can I view current user accounts on the system? and their
permissions?
3) while I'm at it here... how can I retrieve the currently logged in user
from my asp.net web application?
any info is appreciated. Thanks.
If you have a copy of Enterprise Manager on a different machine you should be
able to connect to the msde install and see it in the familar enterprise
manager.
"djc" wrote:
> Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
> 2000. I have used Enterprise manager to create/manage users/permissions and
> replication before. I am however not familiar with MSDE. I am using MSDE
> because I'm playing with asp.net. Since there is no user interface I am
> lost. I created my tables and stored procedures via the Web Matrix asp.net
> development tool.
> 1) I need to configure the MSDE 2000 installation user access. Its currently
> setup for integrated windows authentication, which I want to keep. But how
> do I add/manage the users and their permissions. For example with Enterprise
> Manager, even with Integrated authentication, you have to add the
> users/groups from your windows domain before SQL server can use them. Do I
> need to do this in MSDE? how?
> 2) how can I view current user accounts on the system? and their
> permissions?
> 3) while I'm at it here... how can I retrieve the currently logged in user
> from my asp.net web application?
> any info is appreciated. Thanks.
>
>
|||hi,
txghia58 wrote:
> If you have a copy of Enterprise Manager on a different machine you
> should be able to connect to the msde install and see it in the
> familar enterprise manager.
bu only in development/test scenario... you are not licensed to use SQL
Server Client Tools in production...
you have to resort on home made tools and/or 3rd party tools to manage MSDE
in production...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
2000. I have used Enterprise manager to create/manage users/permissions and
replication before. I am however not familiar with MSDE. I am using MSDE
because I'm playing with asp.net. Since there is no user interface I am
lost. I created my tables and stored procedures via the Web Matrix asp.net
development tool.
1) I need to configure the MSDE 2000 installation user access. Its currently
setup for integrated windows authentication, which I want to keep. But how
do I add/manage the users and their permissions. For example with Enterprise
Manager, even with Integrated authentication, you have to add the
users/groups from your windows domain before SQL server can use them. Do I
need to do this in MSDE? how?
2) how can I view current user accounts on the system? and their
permissions?
3) while I'm at it here... how can I retrieve the currently logged in user
from my asp.net web application?
any info is appreciated. Thanks.
If you have a copy of Enterprise Manager on a different machine you should be
able to connect to the msde install and see it in the familar enterprise
manager.
"djc" wrote:
> Hello, I'm an MSDE n00b but I do have a basic understanding of SQL Server
> 2000. I have used Enterprise manager to create/manage users/permissions and
> replication before. I am however not familiar with MSDE. I am using MSDE
> because I'm playing with asp.net. Since there is no user interface I am
> lost. I created my tables and stored procedures via the Web Matrix asp.net
> development tool.
> 1) I need to configure the MSDE 2000 installation user access. Its currently
> setup for integrated windows authentication, which I want to keep. But how
> do I add/manage the users and their permissions. For example with Enterprise
> Manager, even with Integrated authentication, you have to add the
> users/groups from your windows domain before SQL server can use them. Do I
> need to do this in MSDE? how?
> 2) how can I view current user accounts on the system? and their
> permissions?
> 3) while I'm at it here... how can I retrieve the currently logged in user
> from my asp.net web application?
> any info is appreciated. Thanks.
>
>
|||hi,
txghia58 wrote:
> If you have a copy of Enterprise Manager on a different machine you
> should be able to connect to the msde install and see it in the
> familar enterprise manager.
bu only in development/test scenario... you are not licensed to use SQL
Server Client Tools in production...
you have to resort on home made tools and/or 3rd party tools to manage MSDE
in production...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Dumb permissions question
Hi
I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
horror I notice that that the SQL data directory has permissions set
for:
Domain\Administrator (full accesss)
Domain\Administrators (full accesss)
Authenticated users (read,list, read). I guess this isn't a good idea
at all!
What are the optimum permissions here (I can't seem to get a straight
answer). Do I set it for the SQL service to have full control only (at
the moment MSSQLSERVER service is logging on using
Domain\Administrator). Do I change this account? What about the other
SQL Server related services? Please also note I am also using Backup
Exec 9 to backup the databases.
Any suggestions you may have will be greatly appreciated!
Many thanks
Alex<postings@.alexshirley.com> wrote in message
news:1133285607.162678.51170@.g14g2000cwa.googlegroups.com...
> Hi
> I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
> horror I notice that that the SQL data directory has permissions set
> for:
> Domain\Administrator (full accesss)
> Domain\Administrators (full accesss)
> Authenticated users (read,list, read). I guess this isn't a good idea
> at all!
> What are the optimum permissions here (I can't seem to get a straight
> answer). Do I set it for the SQL service to have full control only (at
> the moment MSSQLSERVER service is logging on using
> Domain\Administrator). Do I change this account? What about the other
> SQL Server related services? Please also note I am also using Backup
> Exec 9 to backup the databases.
> Any suggestions you may have will be greatly appreciated!
> Many thanks
> Alex
On my SQL Server 2000 database running on Windows 2003 standard server, the
permissions on the SQL data directory are just for Domain\Administrators,
full access. The MSSQLServer service is set to use the local system account.
If your authenticated users have access then you should be able to revoke
that without problem.
I don't know where Backup Exec fits into the picture - I don't use it - but
it shouldn't be backing up the files directly so it shouldn't need any
permissions on the folder.
Hope this is useful.
--
Brian Cryer
www.cryer.co.uk/brian|||Great, thanks for your help Brian...!
Alex
-->
Brian Cryer wrote:
> <postings@.alexshirley.com> wrote in message
> news:1133285607.162678.51170@.g14g2000cwa.googlegroups.com...
> On my SQL Server 2000 database running on Windows 2003 standard server, th
e
> permissions on the SQL data directory are just for Domain\Administrators,
> full access. The MSSQLServer service is set to use the local system accoun
t.
> If your authenticated users have access then you should be able to revoke
> that without problem.
> I don't know where Backup Exec fits into the picture - I don't use it - bu
t
> it shouldn't be backing up the files directly so it shouldn't need any
> permissions on the folder.
> Hope this is useful.
> --
> Brian Cryer
> www.cryer.co.uk/brian
I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
horror I notice that that the SQL data directory has permissions set
for:
Domain\Administrator (full accesss)
Domain\Administrators (full accesss)
Authenticated users (read,list, read). I guess this isn't a good idea
at all!
What are the optimum permissions here (I can't seem to get a straight
answer). Do I set it for the SQL service to have full control only (at
the moment MSSQLSERVER service is logging on using
Domain\Administrator). Do I change this account? What about the other
SQL Server related services? Please also note I am also using Backup
Exec 9 to backup the databases.
Any suggestions you may have will be greatly appreciated!
Many thanks
Alex<postings@.alexshirley.com> wrote in message
news:1133285607.162678.51170@.g14g2000cwa.googlegroups.com...
> Hi
> I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
> horror I notice that that the SQL data directory has permissions set
> for:
> Domain\Administrator (full accesss)
> Domain\Administrators (full accesss)
> Authenticated users (read,list, read). I guess this isn't a good idea
> at all!
> What are the optimum permissions here (I can't seem to get a straight
> answer). Do I set it for the SQL service to have full control only (at
> the moment MSSQLSERVER service is logging on using
> Domain\Administrator). Do I change this account? What about the other
> SQL Server related services? Please also note I am also using Backup
> Exec 9 to backup the databases.
> Any suggestions you may have will be greatly appreciated!
> Many thanks
> Alex
On my SQL Server 2000 database running on Windows 2003 standard server, the
permissions on the SQL data directory are just for Domain\Administrators,
full access. The MSSQLServer service is set to use the local system account.
If your authenticated users have access then you should be able to revoke
that without problem.
I don't know where Backup Exec fits into the picture - I don't use it - but
it shouldn't be backing up the files directly so it shouldn't need any
permissions on the folder.
Hope this is useful.
--
Brian Cryer
www.cryer.co.uk/brian|||Great, thanks for your help Brian...!
Alex
-->
Brian Cryer wrote:
> <postings@.alexshirley.com> wrote in message
> news:1133285607.162678.51170@.g14g2000cwa.googlegroups.com...
> On my SQL Server 2000 database running on Windows 2003 standard server, th
e
> permissions on the SQL data directory are just for Domain\Administrators,
> full access. The MSSQLServer service is set to use the local system accoun
t.
> If your authenticated users have access then you should be able to revoke
> that without problem.
> I don't know where Backup Exec fits into the picture - I don't use it - bu
t
> it shouldn't be backing up the files directly so it shouldn't need any
> permissions on the folder.
> Hope this is useful.
> --
> Brian Cryer
> www.cryer.co.uk/brian
Dumb permissions question
Hi
I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
horror I notice that that the SQL data directory has permissions set
for:
Domain\Administrator (full accesss)
Domain\Administrators (full accesss)
Authenticated users (read,list, read). I guess this isn't a good idea
at all!
What are the optimum permissions here (I can't seem to get a straight
answer). Do I set it for the SQL service to have full control only (at
the moment MSSQLSERVER service is logging on using
Domain\Administrator). Do I change this account? What about the other
SQL Server related services? Please also note I am also using Backup
Exec 9 to backup the databases.
Any suggestions you may have will be greatly appreciated!
Many thanks
Alex
<postings@.alexshirley.com> wrote in message
news:1133285607.162678.51170@.g14g2000cwa.googlegro ups.com...
> Hi
> I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
> horror I notice that that the SQL data directory has permissions set
> for:
> Domain\Administrator (full accesss)
> Domain\Administrators (full accesss)
> Authenticated users (read,list, read). I guess this isn't a good idea
> at all!
> What are the optimum permissions here (I can't seem to get a straight
> answer). Do I set it for the SQL service to have full control only (at
> the moment MSSQLSERVER service is logging on using
> Domain\Administrator). Do I change this account? What about the other
> SQL Server related services? Please also note I am also using Backup
> Exec 9 to backup the databases.
> Any suggestions you may have will be greatly appreciated!
> Many thanks
> Alex
On my SQL Server 2000 database running on Windows 2003 standard server, the
permissions on the SQL data directory are just for Domain\Administrators,
full access. The MSSQLServer service is set to use the local system account.
If your authenticated users have access then you should be able to revoke
that without problem.
I don't know where Backup Exec fits into the picture - I don't use it - but
it shouldn't be backing up the files directly so it shouldn't need any
permissions on the folder.
Hope this is useful.
Brian Cryer
www.cryer.co.uk/brian
|||Great, thanks for your help Brian...!
Alex
-->
Brian Cryer wrote:
> <postings@.alexshirley.com> wrote in message
> news:1133285607.162678.51170@.g14g2000cwa.googlegro ups.com...
> On my SQL Server 2000 database running on Windows 2003 standard server, the
> permissions on the SQL data directory are just for Domain\Administrators,
> full access. The MSSQLServer service is set to use the local system account.
> If your authenticated users have access then you should be able to revoke
> that without problem.
> I don't know where Backup Exec fits into the picture - I don't use it - but
> it shouldn't be backing up the files directly so it shouldn't need any
> permissions on the folder.
> Hope this is useful.
> --
> Brian Cryer
> www.cryer.co.uk/brian
I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
horror I notice that that the SQL data directory has permissions set
for:
Domain\Administrator (full accesss)
Domain\Administrators (full accesss)
Authenticated users (read,list, read). I guess this isn't a good idea
at all!
What are the optimum permissions here (I can't seem to get a straight
answer). Do I set it for the SQL service to have full control only (at
the moment MSSQLSERVER service is logging on using
Domain\Administrator). Do I change this account? What about the other
SQL Server related services? Please also note I am also using Backup
Exec 9 to backup the databases.
Any suggestions you may have will be greatly appreciated!
Many thanks
Alex
<postings@.alexshirley.com> wrote in message
news:1133285607.162678.51170@.g14g2000cwa.googlegro ups.com...
> Hi
> I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
> horror I notice that that the SQL data directory has permissions set
> for:
> Domain\Administrator (full accesss)
> Domain\Administrators (full accesss)
> Authenticated users (read,list, read). I guess this isn't a good idea
> at all!
> What are the optimum permissions here (I can't seem to get a straight
> answer). Do I set it for the SQL service to have full control only (at
> the moment MSSQLSERVER service is logging on using
> Domain\Administrator). Do I change this account? What about the other
> SQL Server related services? Please also note I am also using Backup
> Exec 9 to backup the databases.
> Any suggestions you may have will be greatly appreciated!
> Many thanks
> Alex
On my SQL Server 2000 database running on Windows 2003 standard server, the
permissions on the SQL data directory are just for Domain\Administrators,
full access. The MSSQLServer service is set to use the local system account.
If your authenticated users have access then you should be able to revoke
that without problem.
I don't know where Backup Exec fits into the picture - I don't use it - but
it shouldn't be backing up the files directly so it shouldn't need any
permissions on the folder.
Hope this is useful.
Brian Cryer
www.cryer.co.uk/brian
|||Great, thanks for your help Brian...!
Alex
-->
Brian Cryer wrote:
> <postings@.alexshirley.com> wrote in message
> news:1133285607.162678.51170@.g14g2000cwa.googlegro ups.com...
> On my SQL Server 2000 database running on Windows 2003 standard server, the
> permissions on the SQL data directory are just for Domain\Administrators,
> full access. The MSSQLServer service is set to use the local system account.
> If your authenticated users have access then you should be able to revoke
> that without problem.
> I don't know where Backup Exec fits into the picture - I don't use it - but
> it shouldn't be backing up the files directly so it shouldn't need any
> permissions on the folder.
> Hope this is useful.
> --
> Brian Cryer
> www.cryer.co.uk/brian
Dumb permissions question
Hi
I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
horror I notice that that the SQL data directory has permissions set
for:
Domain\Administrator (full accesss)
Domain\Administrators (full accesss)
Authenticated users (read,list, read). I guess this isn't a good idea
at all!
What are the optimum permissions here (I can't seem to get a straight
answer). Do I set it for the SQL service to have full control only (at
the moment MSSQLSERVER service is logging on using
Domain\Administrator). Do I change this account? What about the other
SQL Server related services? Please also note I am also using Backup
Exec 9 to backup the databases.
Any suggestions you may have will be greatly appreciated!
Many thanks
Alex<postings@.alexshirley.com> wrote in message
news:1133285607.162678.51170@.g14g2000cwa.googlegroups.com...
> Hi
> I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
> horror I notice that that the SQL data directory has permissions set
> for:
> Domain\Administrator (full accesss)
> Domain\Administrators (full accesss)
> Authenticated users (read,list, read). I guess this isn't a good idea
> at all!
> What are the optimum permissions here (I can't seem to get a straight
> answer). Do I set it for the SQL service to have full control only (at
> the moment MSSQLSERVER service is logging on using
> Domain\Administrator). Do I change this account? What about the other
> SQL Server related services? Please also note I am also using Backup
> Exec 9 to backup the databases.
> Any suggestions you may have will be greatly appreciated!
> Many thanks
> Alex
On my SQL Server 2000 database running on Windows 2003 standard server, the
permissions on the SQL data directory are just for Domain\Administrators,
full access. The MSSQLServer service is set to use the local system account.
If your authenticated users have access then you should be able to revoke
that without problem.
I don't know where Backup Exec fits into the picture - I don't use it - but
it shouldn't be backing up the files directly so it shouldn't need any
permissions on the folder.
Hope this is useful.
--
Brian Cryer
www.cryer.co.uk/brian|||Great, thanks for your help Brian...!
Alex
-->
Brian Cryer wrote:
> <postings@.alexshirley.com> wrote in message
> news:1133285607.162678.51170@.g14g2000cwa.googlegroups.com...
> > Hi
> >
> > I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
> > horror I notice that that the SQL data directory has permissions set
> > for:
> > Domain\Administrator (full accesss)
> > Domain\Administrators (full accesss)
> > Authenticated users (read,list, read). I guess this isn't a good idea
> > at all!
> >
> > What are the optimum permissions here (I can't seem to get a straight
> > answer). Do I set it for the SQL service to have full control only (at
> > the moment MSSQLSERVER service is logging on using
> > Domain\Administrator). Do I change this account? What about the other
> > SQL Server related services? Please also note I am also using Backup
> > Exec 9 to backup the databases.
> >
> > Any suggestions you may have will be greatly appreciated!
> >
> > Many thanks
> >
> > Alex
> On my SQL Server 2000 database running on Windows 2003 standard server, the
> permissions on the SQL data directory are just for Domain\Administrators,
> full access. The MSSQLServer service is set to use the local system account.
> If your authenticated users have access then you should be able to revoke
> that without problem.
> I don't know where Backup Exec fits into the picture - I don't use it - but
> it shouldn't be backing up the files directly so it shouldn't need any
> permissions on the folder.
> Hope this is useful.
> --
> Brian Cryer
> www.cryer.co.uk/brian
I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
horror I notice that that the SQL data directory has permissions set
for:
Domain\Administrator (full accesss)
Domain\Administrators (full accesss)
Authenticated users (read,list, read). I guess this isn't a good idea
at all!
What are the optimum permissions here (I can't seem to get a straight
answer). Do I set it for the SQL service to have full control only (at
the moment MSSQLSERVER service is logging on using
Domain\Administrator). Do I change this account? What about the other
SQL Server related services? Please also note I am also using Backup
Exec 9 to backup the databases.
Any suggestions you may have will be greatly appreciated!
Many thanks
Alex<postings@.alexshirley.com> wrote in message
news:1133285607.162678.51170@.g14g2000cwa.googlegroups.com...
> Hi
> I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
> horror I notice that that the SQL data directory has permissions set
> for:
> Domain\Administrator (full accesss)
> Domain\Administrators (full accesss)
> Authenticated users (read,list, read). I guess this isn't a good idea
> at all!
> What are the optimum permissions here (I can't seem to get a straight
> answer). Do I set it for the SQL service to have full control only (at
> the moment MSSQLSERVER service is logging on using
> Domain\Administrator). Do I change this account? What about the other
> SQL Server related services? Please also note I am also using Backup
> Exec 9 to backup the databases.
> Any suggestions you may have will be greatly appreciated!
> Many thanks
> Alex
On my SQL Server 2000 database running on Windows 2003 standard server, the
permissions on the SQL data directory are just for Domain\Administrators,
full access. The MSSQLServer service is set to use the local system account.
If your authenticated users have access then you should be able to revoke
that without problem.
I don't know where Backup Exec fits into the picture - I don't use it - but
it shouldn't be backing up the files directly so it shouldn't need any
permissions on the folder.
Hope this is useful.
--
Brian Cryer
www.cryer.co.uk/brian|||Great, thanks for your help Brian...!
Alex
-->
Brian Cryer wrote:
> <postings@.alexshirley.com> wrote in message
> news:1133285607.162678.51170@.g14g2000cwa.googlegroups.com...
> > Hi
> >
> > I'm running SQL Server 2000 on a Windows 2003 SBS server. Much to my
> > horror I notice that that the SQL data directory has permissions set
> > for:
> > Domain\Administrator (full accesss)
> > Domain\Administrators (full accesss)
> > Authenticated users (read,list, read). I guess this isn't a good idea
> > at all!
> >
> > What are the optimum permissions here (I can't seem to get a straight
> > answer). Do I set it for the SQL service to have full control only (at
> > the moment MSSQLSERVER service is logging on using
> > Domain\Administrator). Do I change this account? What about the other
> > SQL Server related services? Please also note I am also using Backup
> > Exec 9 to backup the databases.
> >
> > Any suggestions you may have will be greatly appreciated!
> >
> > Many thanks
> >
> > Alex
> On my SQL Server 2000 database running on Windows 2003 standard server, the
> permissions on the SQL data directory are just for Domain\Administrators,
> full access. The MSSQLServer service is set to use the local system account.
> If your authenticated users have access then you should be able to revoke
> that without problem.
> I don't know where Backup Exec fits into the picture - I don't use it - but
> it shouldn't be backing up the files directly so it shouldn't need any
> permissions on the folder.
> Hope this is useful.
> --
> Brian Cryer
> www.cryer.co.uk/brian
Sunday, February 26, 2012
DTSrun permissions
Hi
We are attempting to run a dtsrun statement remotely, via a stored procedure. We are getting the errors below, although I have ran this from a c: prompt on my PC.
C:\>dtsrun /SNT_LAKES02 /Usa /P-- /Nbmmenudev /Ap_menucode:8=101
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
We have also tried this with the /E option with the same results.
Can anyone offer any help - thanks in advanceexec master..xp_cmdshell 'dtsrun /SNT_LAKES02 /Usa /P'
well you most likely will need to have permissions to exec master..xp_cmdshell. This most likely is the root of you problems, that is what was my issue when doing this for the first time.
We are attempting to run a dtsrun statement remotely, via a stored procedure. We are getting the errors below, although I have ran this from a c: prompt on my PC.
C:\>dtsrun /SNT_LAKES02 /Usa /P-- /Nbmmenudev /Ap_menucode:8=101
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
We have also tried this with the /E option with the same results.
Can anyone offer any help - thanks in advanceexec master..xp_cmdshell 'dtsrun /SNT_LAKES02 /Usa /P'
well you most likely will need to have permissions to exec master..xp_cmdshell. This most likely is the root of you problems, that is what was my issue when doing this for the first time.
Subscribe to:
Posts (Atom)