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
>

DTSProperty.GetValue

Hi,
Seems a pretty straightforward problem however, I have now spent some time on it. I have a DTSProperty object and want to get its value. I am able to get all the other attributes like Name, Type just fine. The GetValue method on the DTSProperty class takes a object and returns a object. If I want to get a value of the current property what do I pass to GetValue?
I have something like

foreach (DtsProperty property in controlFlow.Properties)
{
string propName = property.Name;
//I want to do this
if(property.Get)
property.GetValue(?)//what do I pass in here

}

Thanks,
-Suri.

object x = property.GetValue(controlFlow);|||Thanks Darren. It works just fine. However I am not sure why the GetValue method was designed to take a TaskHost object as opposed to the IDTSCustomProperty90 which has a value property on it.|||Yes I know what you mean. I read the BOL section and thought that, but looking at code I have that works, well that is what worked.|||This is an optimization. Each object type (more accurately, interface) is chached internally for property objects. Were it not so, property objects would multiply geometrically. When you get a pointer to the properties collection, you're actually getting a pointer to a template collection for the interface. When you get the value, then the template asks the object you pass in for the value.
Make sense?
K

DtsConnection -- Consuming DTS DataReader In Code w/ Parameters

I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following

command.Parameters.Add(new DtsDataParameter("User::EmailPromotion", 1));

and

command.Parameters.Add(new DtsDataParameter("EmailPromotion", 1));

no luck either way
It seems for the variable to be configurable by DtsClient,
it has to be in DtsClient namespace. Create a variable
DtsClient::EmailPromotion, and use the short name
"EmailPromotion" when constructing the parameter object.

Hope this helps,
Michael.

P.S. Sorry, I could not find any documentation for this.
I'll open a bug to get it documented.|||That was it.

Thx|||this is the method that reporting services uses to build reports on top of SSIS. I cannot get this to show up in our VS.NET report designer. I have posted in that forum with no responses. Any help there would be greatly appreciated.

I have already uncommented the SSIS data extension from the rsreportserver.config and restarted the service.

It shows up in web report manager, but not in vs.net

Note using sept ctp and vs.net RC|||I believe you should also uncomment SSIS sections
in RsReportDesigner.config.|||Does this exist for 2005? It looks like things are little different.|||

The RSReportDesigner.config file is in
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||that fixed it. much appreciated|||For those of you who are looking for the steps to change the variable namespace, follow these steps:

1) In the SSIS package, open the variable window
2) In the buttons at the top of the variables window, click the one farthest on the right (Choose Variable Columns)
3) Click "Namespace" and press OK
4) For the variable you are populating in code, change the namespace to DtsClient (this is case sensitive)
5) Now, within any objects that utilize the variable, you'll have to change the variable reference (for example, change from User::VariableName to DtsClient::VariableName)
6) In the code of the app consuming the SSIS package, the ParameterName you'll set won't include the DtsClient:: portion of the variable name

DtsConnection -- Consuming DTS DataReader In Code w/ Parameters

I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following

command.Parameters.Add(new DtsDataParameter("User::EmailPromotion", 1));

and

command.Parameters.Add(new DtsDataParameter("EmailPromotion", 1));

no luck either wayIt seems for the variable to be configurable by DtsClient,
it has to be in DtsClient namespace. Create a variable
DtsClient::EmailPromotion, and use the short name
"EmailPromotion" when constructing the parameter object.

Hope this helps,
Michael.

P.S. Sorry, I could not find any documentation for this.
I'll open a bug to get it documented.|||That was it.

Thx|||this is the method that reporting services uses to build reports on top of SSIS. I cannot get this to show up in our VS.NET report designer. I have posted in that forum with no responses. Any help there would be greatly appreciated.

I have already uncommented the SSIS data extension from the rsreportserver.config and restarted the service.

It shows up in web report manager, but not in vs.net

Note using sept ctp and vs.net RC|||I believe you should also uncomment SSIS sections
in RsReportDesigner.config.|||Does this exist for 2005? It looks like things are little different.|||

The RSReportDesigner.config file is in
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||that fixed it. much appreciated|||For those of you who are looking for the steps to change the variable namespace, follow these steps:

1) In the SSIS package, open the variable window
2) In the buttons at the top of the variables window, click the one farthest on the right (Choose Variable Columns)
3) Click "Namespace" and press OK
4) For the variable you are populating in code, change the namespace to DtsClient (this is case sensitive)
5) Now, within any objects that utilize the variable, you'll have to change the variable reference (for example, change from User::VariableName to DtsClient::VariableName)
6) In the code of the app consuming the SSIS package, the ParameterName you'll set won't include the DtsClient:: portion of the variable name

dts/ sql server 7.0

hai i am new

i am trying to execute DTS (dtsrun) via query analyser, it showing error as "
Execute permission denied on object 'XXX',Database 'YYY',Owner 'dbo' "You're probably not the owner of the dts.|||

Quote:

Originally Posted by space1000

You're probably not the owner of the dts.

without having the owner rights how to handle the same|||

Quote:

Originally Posted by raveekumarg

without having the owner rights how to handle the same

The system administrator should give the user enough rights on the ntfs system.

DTS Transfer of 'view over view' fails

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.
There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>

DTS Transfer of 'view over view' fails

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>

DTS Transfer of 'view over view' fails

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>

Dts transfer of a 'view over a view'

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.Sorry for the inadvertent re-post.
Paul.
--
"Paul W" <qqq@.qqq.com> wrote in message
news:eQL9hW3EFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
>

Dts transfer of a 'view over a view'

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.
Sorry for the inadvertent re-post.
Paul.
"Paul W" <qqq@.qqq.com> wrote in message
news:eQL9hW3EFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
>

Dts transfer of a 'view over a view'

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.Sorry for the inadvertent re-post.
Paul.
--
"Paul W" <qqq@.qqq.com> wrote in message
news:eQL9hW3EFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>
>

DTS transactions

Hi!
I am using SQLServer DTS object to manage my database. There is
BeginTransaction method. My question is:
1. In which database's context SQLServer.BeginTransaction starts
transaction?
2. How can I know/change current context in which SQLServer object works?Igor Solodovnikov, The DTS Object model has two hierarchies: 1) The DTS
Application hierarchy, which contains information about components registere
d
with the system and packages stored in SQL Serverand Meta Data Services, 2)
The DTS package hierarchy which contains all the the functional DTS elements
- tasks, steps, connections and global variables. Which hierarchy are you
using? Also, SQL Server 2000 Books online has a wealth of information about
the DTS Object model and it's methods and properties. If you have any furthe
r
questions, my email is frank_chang91@.hotmail.com.
"Igor Solodovnikov" wrote:

> Hi!
> I am using SQLServer DTS object to manage my database. There is
> BeginTransaction method. My question is:
> 1. In which database's context SQLServer.BeginTransaction starts
> transaction?
> 2. How can I know/change current context in which SQLServer object works?
>