grant to all objects

Is there an easy way to assign permissions to all objects for a user/group?
For instance, in the following BOL example, SELECT permissions are granted t
o
the public role:
GRANT SELECT
ON authors
TO public
GO
In the above example, the table object 'authors' is explicitly listed; is
there a way to use the same statement to grant the same select permission to
the public role for all objects? Or would I have to explicitly indicate each
object? In the latter case, I could use a cursor to dynamically create the
grant statements for each of my objects, however, I was hoping to find a
simpler way to have this done.
Thanks for all your responses in advance.Firstly, I think the BOL example is dumb - I wouldn't go assigning ANY
permissions to the public role in ANY database.
That said, you'd have to assign individual object permissions
individually, preferably to a role that you create but to a specific
user would work too (just much uglier and more admin overhead when users
come & go and when restoring DB backups to other servers). You can do
this in a small cursor loop which is very easy to do. Here's one I
whipped up in about 5 minutes when I read your post that will assign all
possible permissions for all user tables, views, procs & UDFs in the
current database to a specified user or role (untested):
declare @.cmd nvarchar(1000)
declare @.objname sysname
declare @.owner sysname
declare @.objtype char(2)
declare objs cursor for
select [name], user_name(uid) as owner, type from dbo.sysobjects
where type in ('U', 'P', 'V', 'FN')
order by type, [name]
for read only
open objs
fetch next from objs into @.objname, @.owner, @.objtype
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'grant ' +
case (@.objtype)
when ('U') then ('select, insert, update, delete,
references')
when ('V') then ('select, insert, update, delete,
references')
when ('P') then ('execute')
when ('FN') then ('execute')
end + ' on [' + @.owner + '].[' + @.objname + '] to <my
user/role>'
exec (@.cmd)
end
fetch next from objs into @.objname, @.owner, @.objtype
end
close objs
deallocate objs
How easy is that?
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Rob wrote:

>Is there an easy way to assign permissions to all objects for a user/group?
>For instance, in the following BOL example, SELECT permissions are granted
to
>the public role:
>GRANT SELECT
>ON authors
>TO public
>GO
>In the above example, the table object 'authors' is explicitly listed; is
>there a way to use the same statement to grant the same select permission t
o
>the public role for all objects? Or would I have to explicitly indicate eac
h
>object? In the latter case, I could use a cursor to dynamically create the
>grant statements for each of my objects, however, I was hoping to find a
>simpler way to have this done.
>Thanks for all your responses in advance.
>sql

Good SSIS Example tutorial - User Variables

Hi There

I have read BOL extensively and gone to SSIS tutorial/example web links all day.
Problem is the examples are way to simple.

And the tutorials on BOL especially the DW warehouse example have no explanantion or step by step flow, and the others are to simple.

I would REALLY appreciate it if some had a link or resource to a good complicated step by step SSIS tutorial/example.
Or more specifically not a basic one, i am most interested in using variables to control flow, and the use of variables in expressions to set properties of a task.

For example if i set a variable equal to a single result set in a sql task, how do i use that variable later to control package flow, or in an expression to disable a task.

In BOL and internet resources it basically just states you can use variable to do many things but no nice examples of how to do so.

PLEASE help.

ThanxPhew, this thread could go on for years

You say you want complicated examples. For what its worth, I would say that a complicated example is just an amalgamation of lots of simple examples. SSIS is such an easy tool to use that the only real complexity is using alot of tasks in a package. The individual tasks themselves are fairly simple and that's why its possible to build large, seemingly complicated, proof-of-concept solutions in a very short space of time.

If you want to know how to use variables to control your package flow go here: http://www.sqlis.com/default.aspx?306

If you want to use variables in expressions to set properties of a task (termed property expressions) then there's a nice example here of setting the SQLStatementSource property of an ExecuteSQL Task: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
or here of setting various properties of the SendMail task: http://www.sqlis.com/default.aspx?59
or here of setting the ConnectionString property of a FlatFile connection manager: http://blogs.conchango.com/jamiethomson/

I haven't seen an example online of how to disable a task at runtime using an expression but believe me, it is very very easy, and there is enough material at the above links to show you how to do it. If you still have trouble then please reply here and I can put a simple demo together later.

You may also want to look here which explains how expressions can also be used in a variable: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx

Out of interest, under what circumstances are you needing to disable a task? I think that affecting your control-flow so that a task is never executed (as explained at the first link above) would be the better thing to do.

In summary, everything we're talking about here is involving the use of expressions, i.e. workflow contraint expressions and property expressions. Its impossible to overstate just how powerful expressions can be in an SSIS solution - gotta be my favourite feature of SSIS I reckon.

-Jamie|||Hi Jamie

Thank You so much for the input, i will check out all the links you provided.
I just saw that it was an option to set the diabled property through an expression.
For example i perform a sql task to see if there is any data in the data warehouse staging tables, if there is not ie: my variable is 0, i want to disable the delete data from staging tables sql tasks , but i agree changing the control flow is better.

Thank you again i will reply again once i have checked out the links.

Thank|||

SeanDL wrote:

I just saw that it was an option to set the diabled property through an expression.

Yeah, its definately an option. just not best practice!

-Jamie|||Sean, I second Jamie's advocation of using property expressions here. Using them for enabling and disabling tasks is, I think, a bit of bad practice, especially for workflow control.
At one time we considered special casing the enable/disable property on tasks so that property expressions couldn't be used to modify it. We backed away from that because there _are_ valid reasons to do this, but not for workflow.|||Hi Jamie

The links are proving useful, thanx.
If you have a minute to spare i have some queries about the send mail task example.
I am not sure if you are fimiliar with it, but i get errors after configuring the send mail task, as no recipient is being supplied, but that is because ToLine is dynamically created by an expression, but SSIS will not run debug it is an error!? I get around this by assigning the variable a value not sure if this is right.

Secondly when i run the example i get the following errors:

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ContactName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC001C012 at Sending mails from a record set: ForEach Variable Mapping number 2 to variable "User::ContactName" cannot be applied.

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ClosureDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC001C012 at Sending mails from a record set: ForEach Variable Mapping number 3 to variable "User::ClosureDate" cannot be applied.

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

I dont get it , the variables are type string, the columns in the table are char, the only values being assigned to the variable are in the loop but i am not changing any types ?

Thanx

|||Hi Jamie

Dont worry i figured ou tthe problem, for some reason the result set output columns are not in the same order as specified in the select statement? Strange.But it works now, thanx for all your help.|||

KirkHaselden wrote:

We backed away from that because there _are_ valid reasons to do this, but not for workflow.

And they are...?

-Jamie|||

Valid reason? Hmmmm, I'm not sure I have a proper list, it's more of a design principle. Ie., we take the approach that we don't know everything. Pretty safe assumption, wouldn't you say?
We don't like to arbitrarily limit what folks can do just because we think it's bad practice. Someone may have a perfectly good reason for doing it that we haven't thought of.

One I can think of is configuring "Disable" based on environment constraints, like configuring entire containers and enabling others. One could do this with a dummy task and precedence constraints, but the configuration method is more elegant in some cases like DBMaint.

|||Fair enough. I'd agree about it being bad practice to limit stuff. Sorry for making you think unnecessarily but when you said you had reasons I was interested to know what they were. :)|||Sorry for making you think unnecessarily
Yes, what a pain. :)
K|||

Hi Sean,

I am running into a similar problem. I have 1 input and 5 output variables to a stored procedure being called in a Execute SQL task, all defined as string and varchar. I am attempting to trap the return value (designated a long). I have everything defined as parameters.

How do I figure out what order my variables are being passed and populated and if the order is not correct, how do I ensure the order?

Thanks

Arun

|||

Arun,

The parameters are mapped in the order that they appear in the Paremeter Mappings tab.

-Jamie

Good SSIS Example tutorial - User Variables

Hi There

I have read BOL extensively and gone to SSIS tutorial/example web links all day.
Problem is the examples are way to simple.

And the tutorials on BOL especially the DW warehouse example have no explanantion or step by step flow, and the others are to simple.

I would REALLY appreciate it if some had a link or resource to a good complicated step by step SSIS tutorial/example.
Or more specifically not a basic one, i am most interested in using variables to control flow, and the use of variables in expressions to set properties of a task.

For example if i set a variable equal to a single result set in a sql task, how do i use that variable later to control package flow, or in an expression to disable a task.

In BOL and internet resources it basically just states you can use variable to do many things but no nice examples of how to do so.

PLEASE help.

ThanxPhew, this thread could go on for years

You say you want complicated examples. For what its worth, I would say that a complicated example is just an amalgamation of lots of simple examples. SSIS is such an easy tool to use that the only real complexity is using alot of tasks in a package. The individual tasks themselves are fairly simple and that's why its possible to build large, seemingly complicated, proof-of-concept solutions in a very short space of time.

If you want to know how to use variables to control your package flow go here: http://www.sqlis.com/default.aspx?306

If you want to use variables in expressions to set properties of a task (termed property expressions) then there's a nice example here of setting the SQLStatementSource property of an ExecuteSQL Task: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
or here of setting various properties of the SendMail task: http://www.sqlis.com/default.aspx?59
or here of setting the ConnectionString property of a FlatFile connection manager: http://blogs.conchango.com/jamiethomson/

I haven't seen an example online of how to disable a task at runtime using an expression but believe me, it is very very easy, and there is enough material at the above links to show you how to do it. If you still have trouble then please reply here and I can put a simple demo together later.

You may also want to look here which explains how expressions can also be used in a variable: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx

Out of interest, under what circumstances are you needing to disable a task? I think that affecting your control-flow so that a task is never executed (as explained at the first link above) would be the better thing to do.

In summary, everything we're talking about here is involving the use of expressions, i.e. workflow contraint expressions and property expressions. Its impossible to overstate just how powerful expressions can be in an SSIS solution - gotta be my favourite feature of SSIS I reckon.

-Jamie|||Hi Jamie

Thank You so much for the input, i will check out all the links you provided.
I just saw that it was an option to set the diabled property through an expression.
For example i perform a sql task to see if there is any data in the data warehouse staging tables, if there is not ie: my variable is 0, i want to disable the delete data from staging tables sql tasks , but i agree changing the control flow is better.

Thank you again i will reply again once i have checked out the links.

Thank|||

SeanDL wrote:

I just saw that it was an option to set the diabled property through an expression.

Yeah, its definately an option. just not best practice!

-Jamie|||Sean, I second Jamie's advocation of using property expressions here. Using them for enabling and disabling tasks is, I think, a bit of bad practice, especially for workflow control.
At one time we considered special casing the enable/disable property on tasks so that property expressions couldn't be used to modify it. We backed away from that because there _are_ valid reasons to do this, but not for workflow.|||Hi Jamie

The links are proving useful, thanx.
If you have a minute to spare i have some queries about the send mail task example.
I am not sure if you are fimiliar with it, but i get errors after configuring the send mail task, as no recipient is being supplied, but that is because ToLine is dynamically created by an expression, but SSIS will not run debug it is an error!? I get around this by assigning the variable a value not sure if this is right.

Secondly when i run the example i get the following errors:

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ContactName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC001C012 at Sending mails from a record set: ForEach Variable Mapping number 2 to variable "User::ContactName" cannot be applied.

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ClosureDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC001C012 at Sending mails from a record set: ForEach Variable Mapping number 3 to variable "User::ClosureDate" cannot be applied.

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

I dont get it , the variables are type string, the columns in the table are char, the only values being assigned to the variable are in the loop but i am not changing any types ?

Thanx

|||Hi Jamie

Dont worry i figured ou tthe problem, for some reason the result set output columns are not in the same order as specified in the select statement? Strange.But it works now, thanx for all your help.|||

KirkHaselden wrote:

We backed away from that because there _are_ valid reasons to do this, but not for workflow.

And they are...?

-Jamie|||

Valid reason? Hmmmm, I'm not sure I have a proper list, it's more of a design principle. Ie., we take the approach that we don't know everything. Pretty safe assumption, wouldn't you say?
We don't like to arbitrarily limit what folks can do just because we think it's bad practice. Someone may have a perfectly good reason for doing it that we haven't thought of.

One I can think of is configuring "Disable" based on environment constraints, like configuring entire containers and enabling others. One could do this with a dummy task and precedence constraints, but the configuration method is more elegant in some cases like DBMaint.

|||Fair enough. I'd agree about it being bad practice to limit stuff. Sorry for making you think unnecessarily but when you said you had reasons I was interested to know what they were. :)|||Sorry for making you think unnecessarily
Yes, what a pain. :)
K|||

Hi Sean,

I am running into a similar problem. I have 1 input and 5 output variables to a stored procedure being called in a Execute SQL task, all defined as string and varchar. I am attempting to trap the return value (designated a long). I have everything defined as parameters.

How do I figure out what order my variables are being passed and populated and if the order is not correct, how do I ensure the order?

Thanks

Arun

|||

Arun,

The parameters are mapped in the order that they appear in the Paremeter Mappings tab.

-Jamie

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
Thanks
M> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.co m...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
ThanksM> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.com...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
Thanks
M> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.co m...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
ThanksM> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.com...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
ThanksM> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.com...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

Giving roles access to perspectives

In BOL, the page on creating perspectives says

"Dimension and cell security for a perspective are derived from the source cube. However, roles can be given or denied access to a perspective. A role can be denied access to a cube and given access to a perspective of the cube."

However, I can't find out how to grant access to a perspective. There doesn't seem to be anything about it on BOL, and the perspectives don't appear anywhere in the role editor.

Can anybody tell me how to do this? Sorry if I'm being stupid and missing something obvious!

Unfortunately in this version of Analysis Services it is not possible to assign security permission to perspective.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Is this a feature that is likely to be implemented in the future? Is there any other way to grant access to specific attributes of a dimension?|||

Of cource you can't expect any public commitment to any particular feature :). It has been requested by many customers.

You can create a role object and use BI Dev Studio to grant/deny permissions to dimension members of specific attribute.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.