Perspectives Defined in Analysis Cube not working in Report Builder

I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.

Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.

Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.

By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.

Help!!!!

Certainly the perspective in the Report Model should reflect the definition in the cube.

Does this apply to all the perspectives in the cube, if you define multiple of them?

|||Sorry for my delayed response. The answer to your question is yes, my previous message applied to all of the perspectives in the cube. And I have had the same problem on multiple cubes.|||I have the same problem. You already know a solution?

Perspectives Defined in Analysis Cube not working in Report Builder

I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.

Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.

Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.

By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.

Help!!!!

Certainly the perspective in the Report Model should reflect the definition in the cube.

Does this apply to all the perspectives in the cube, if you define multiple of them?

|||Sorry for my delayed response. The answer to your question is yes, my previous message applied to all of the perspectives in the cube. And I have had the same problem on multiple cubes.

Perspectives Defined in Analysis Cube not working in Report Builder

I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.

Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.

Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.

By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.

Help!!!!

Certainly the perspective in the Report Model should reflect the definition in the cube.

Does this apply to all the perspectives in the cube, if you define multiple of them?

|||Sorry for my delayed response. The answer to your question is yes, my previous message applied to all of the perspectives in the cube. And I have had the same problem on multiple cubes.

Persisting Code (Repost)

I have created a SRS report with 6 columns. I would like every 3rd Row of
the report to have a silver background and white on the rest. When I run
the report the first time I get the desired result. Then, depending on the
number of rows the report returns, successive runs of the report will have
the silver row starting on 1, 2 or 3. My guess is that SRS is remembering
where the code left off and picks up from there on the successive runs.
I added the following code to my report:
Private Shared count As Integer = 0
Private Shared colors As String() = {"White","White","White","White","White","White","White","White","White","White","White","White","Silver","Silver","Silver","Silver","Silver","Silver"}
Public Function GetColor() As String
Dim c as string = colors(count Mod colors.Length)
count = count + 1
Return c
End Function
Then, on the background Color I have:
=Code.GetColor()
George F Grund IVWould removing the shared keyword achieve your desired results?
You could also try and reset the count by creating a function like
below and then calling from your page header.
public function ResetVariable() as string
count = 0
return ""
end function|||Well, I'll asumme that you are using a table with no groups. If so, what you
are trying to do is very simple and you don't that code. Select the detail
row, then in the background color porperty type something like this:
=IIF(RowCount("YourDataSet") mod 3 = 0, "#D3D3D3", "#FFFFFF")
D3D3D3 is the RBG code for Light Gray, and FFFFFF is for White
I hope this helpssql

permissions to deploy to one folder

I have created a staging folder in reporting services. The idea is to allow
developers to deploy to this folder only in order to test their reports and
to then have the dba's move the reports to the users' area.
Is it possible to give the developer group rights to deploy to this staging
folder only?Yes, just click on the properties tab for the desired folder and then the
secrurities tab. From here you can set the permission how you like.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:679A12F5-45D5-465F-9BE9-7F0166A69BD6@.microsoft.com...
>I have created a staging folder in reporting services. The idea is to allow
> developers to deploy to this folder only in order to test their reports
> and
> to then have the dba's move the reports to the users' area.
> Is it possible to give the developer group rights to deploy to this
> staging
> folder only?

Permissions to a standard database role

Q: A standard database role named abc is created and assigned permissions to
db_reader and db_writer roles.
Then the database users are made members to the role abc.
(The DatabaseAccess under users displays viaGroupMembership.)
But why doesnot users have access to the database?
(SQLServer2000)Hi,
Please let me now what error message, if any, you receive when you attempt
to access the database. Also, if you have auditing enabled, what error
message, if any, do you receive in the SQL Error Log or the Application
Event Log?
Based on my test, the configuration you describe should work OK.
Thanks.
Gary
This posting is provided "AS IS" with no warranties, and confers no rights.

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:

>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell wha
t
>access the public role gives a user?
>Thanks
>Don
>

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
Don
Yes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:

>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>sql

Permissions Problem using Dynamic SQL

Hi all!

I've got a problem where I have created a stored procedure (using MS SQL Server 2000) that does a temporary table creation:
CREATE #tmpData ( [some_fields] )
and then it uses dynamic SQL to populate the data
SELECT @.ExecStr =
'INSERT INTO #tmpData
SELECT * FROM tData
WHERE [some_condition]
ORDER BY ' + @.SortColumn /* input parm to the stored proc */
EXEC (@.ExecStr)

I get a permissions error 229 when I try to run this because my user only has execute permissions for the stored procedure within the database. The only thing that I've found so far that will fix this is if I change the user's permissions to db_owner, which I don't want to do.

I've tried to explicitly grant permission within the stored proc, but since the object (the temp table) does not actually reside in the database, that gives me an error as well (4610: You can only grant or revoke permissions on objects in the current database.).

Is there anything else I can do? I really don't want to have to give the user that much freedom within the database, and removing the dynamic SQL really isn't a viable option either.

Thanks in advance for your help!
CatDynamic SQL executes within its own scope, not that of the stored procedure. So it does not inherit the stored procedures rights, and only operates under the connection's rights.
You'll need to grant READ permission to the user on table tData.|||Try this ... put your insert before your exec ...


SET NOCOUNT ON
USE master

create table #temp (dbname sysname)

declare @.sql nvarchar(255)
select @.sql = 'select name from sysdatabases'

insert into #temp
exec sp_executesql @.sql

select * from #temp

drop table #temp|||Thanks, Tom!

That was a good idea. Unfortunately it still gives me the same error.

Thanks again! If you have any other ideas, let me know.
Cat|||Your user is going to have to have select permissions on tData.|||Excellent suggestion.
You'll need to grant READ permission to the user on table tData.|||Thanks everyone!

I did get this running with just the SELECT permissions on the data tables involved. Although this is still not ideal from a security perspective, it is better than having to give them db_owner. I appreciate the feedback!

Thanks again,
Cat|||If security is a big issue, you could create a view based upon the table showing only the required columns and filtered rows, and then reference the view in your dynamic SQL. Then you can grant permissions on the view rather than on the table.|||Excellent suggestion.

Good advice is always worth repeating :cool:|||You could kludge your way around part of the problem using something like:DECLARE @.i INT

SET @.i = 1

SELECT o.name
FROM dbo.sysobjects AS o
ORDER BY
CASE @.i
WHEN 1 THEN o.name
WHEN 2 THEN Str(o.id, 20)
ELSE Convert(CHAR(30), crdate, 121)
END-PatP|||If security is a big issue,

I have to go change my pants now

"If"...good lord

Permissions Problem using Dynamic SQL

Hi all!

I've got a problem where I have created a stored procedure (using MS SQL Server 2000) that does a temporary table creation:

Code Snippet

CREATE #tmpData ( [some_fields] )

and then it uses dynamic SQL to populate the data

Code Snippet

SELECT @.ExecStr =
'INSERT INTO #tmpData
SELECT * FROM tData
WHERE [some_condition]
ORDER BY ' + @.SortColumn /* input parm to the SP */
EXEC (@.ExecStr)

I get a permissions error 229 when I try to run this because my user only has execute permissions for the stored procedure within the database. The only thing that I've found so far that will fix this is if I change the user's permissions to db_owner, which I don't want to do.

I've tried to explicitly grant permission within the stored proc, but since the object (the temp table) does not actually reside in the database, that gives me an error as well (4610: You can only grant or revoke permissions on objects in the current database.).

Is there anything else I can do? I really don't want to have to give the user that much freedom within the database, and removing the dynamic SQL really isn't a viable option either.

Thanks in advance for your help!

Cat

If you are using SQL 2000, you have no choice. To use dynamic SQL requires a high level of permissions.

IF you are using SQL 2005, explore the 'EXECUTE AS' property.

Refer to Books Online, Topic: 'EXECUTE AS'

Permissions problem (I think)

O.k. Here's the background on my situation. We've got an Access program
running against a SQL 2000 DB. The program was created using Access 2003.
The user that runs this program had Access 2000 on their PC, which was
upgraded to Access 2003. One of the first steps the program does is copy
data from 2 external files into 2 seperate tables(1 table for each file).
This is done with the TransferText command. When the user runs the program,
a new table with the same name as the original is created with the user id
shown as the owner. The data is then copied into this new table instead of
the one already in the DB. Thus, when the program tries to read the origina
l
table it fails b/c it is empty. This happens ONLY for this user. If anyone
else logs in on the users PC, the program works fine. I've tried changing
every permissions option available, including giving the user FULL rights an
d
even making them an enterprise admin. Nothing seems to work. All security
is the same as it was before we upgraded the user to Access 2003, and it was
all working before. Any ideas'If the following is the issue and only happens for that
user:
"When the user runs the program, a new table with the same
name as the original is created with the user id shown as
the owner"
then it sounds like the user is in different database roles
than the other user. You didn't say what authentication
method is used or how the access program is connecting, if
it's a project or an mdb, etc. You may want to try running
profiler, capture the Login, user info and compare it when
others run the same routine. Then look at the differences in
database role, server role membership.
-Sue
On Thu, 25 Aug 2005 14:26:09 -0700, "CD"
<CD@.discussions.microsoft.com> wrote:

>O.k. Here's the background on my situation. We've got an Access program
>running against a SQL 2000 DB. The program was created using Access 2003.
>The user that runs this program had Access 2000 on their PC, which was
>upgraded to Access 2003. One of the first steps the program does is copy
>data from 2 external files into 2 seperate tables(1 table for each file).
>This is done with the TransferText command. When the user runs the program
,
>a new table with the same name as the original is created with the user id
>shown as the owner. The data is then copied into this new table instead of
>the one already in the DB. Thus, when the program tries to read the origin
al
>table it fails b/c it is empty. This happens ONLY for this user. If anyon
e
>else logs in on the users PC, the program works fine. I've tried changing
>every permissions option available, including giving the user FULL rights a
nd
>even making them an enterprise admin. Nothing seems to work. All security
>is the same as it was before we upgraded the user to Access 2003, and it wa
s
>all working before. Any ideas'

Permissions problem

Hi
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
Any ideas why I cannot connect in enterprise manager.?
ThanksI can however import these tables using a DTS job.!!!
"Chubbly Geezer" wrote:

> Hi
> I have some code that I am testing to read data from a FoxPro file.
> I have copied the FoxPro file locally to test.
> I have created a linked server that points to this file. Enterprise manag
er
> correctly shows the tables available.
> Works fine.!
> However, when I create another linked server pointing towards the network
> files, enterprise manager shows no available tables and my code returns an
> error stating that the table does not exist.
> I have checked the security permissions on the network drive/directories a
nd
> I appear to have full access. I can indeed move, delete etc these files.
> Any ideas why I cannot connect in enterprise manager.?
> Thanks|||Hi
Does the account that SQL Server and Agent run under have permission on the
remote share and files?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Chubbly Geezer" wrote:

> Hi
> I have some code that I am testing to read data from a FoxPro file.
> I have copied the FoxPro file locally to test.
> I have created a linked server that points to this file. Enterprise manag
er
> correctly shows the tables available.
> Works fine.!
> However, when I create another linked server pointing towards the network
> files, enterprise manager shows no available tables and my code returns an
> error stating that the table does not exist.
> I have checked the security permissions on the network drive/directories a
nd
> I appear to have full access. I can indeed move, delete etc these files.
> Any ideas why I cannot connect in enterprise manager.?
> Thanks|||Both services were running under local system. Have changed to my login but
still no joy.
Also in linked server security options I have tried changing them to login
as myself but no luck.
The following 2 lines of code ALSO return table data:
EXEC sp_tables_ex @.table_server='OPERA_SERVER'
EXEC sp_columns_ex @.table_server='OPERA_SERVER', @.table_name='ssale'
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Does the account that SQL Server and Agent run under have permission on th
e
> remote share and files?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Chubbly Geezer" wrote:
>|||Correction.
The following 2 lines of code DO NOT WORK..
EXEC sp_tables_ex @.table_server='OPERA_SERVER'
EXEC sp_columns_ex @.table_server='OPERA_SERVER', @.table_name='ssale'
"Chubbly Geezer" wrote:
> Both services were running under local system. Have changed to my login b
ut
> still no joy.
> Also in linked server security options I have tried changing them to login
> as myself but no luck.
> The following 2 lines of code ALSO return table data:
> EXEC sp_tables_ex @.table_server='OPERA_SERVER'
> EXEC sp_columns_ex @.table_server='OPERA_SERVER', @.table_name='ssale'
>
> "Mike Epprecht (SQL MVP)" wrote:
>sql

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4iyb.asp
Davidsql

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
MG
"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/de...urity_4iyb.asp
David

Permissions on Views

I've created two new views along with a new login that I'd like to only have
select permission on these new views. I've created the views and granted
select permission to the new user to these views. The first view, which
invloves only one table, works fine. The second is giving me problems and I
don't quite understand why.
The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which are
owned by another user. I've verified that the view references the correct
owners and I've granted select permission to the user to this view, but I
keep getting select permission denied on the 4 objects being referenced in
the view, unless I grant select permissions to the objects that I want to
restrict.
What am I missing here?
Any help is appreciated
Thanks
Mike
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:79C5BB72-2C87-4D86-87FC-71B4F388AF2F@.microsoft.com...
> I've created two new views along with a new login that I'd like to only
> have
> select permission on these new views. I've created the views and granted
> select permission to the new user to these views. The first view, which
> invloves only one table, works fine. The second is giving me problems and
> I
> don't quite understand why.
> The 2nd view joins 2 tables, which are owned by dbo, with 2 views, which
> are
> owned by another user. I've verified that the view references the correct
> owners and I've granted select permission to the user to this view, but I
> keep getting select permission denied on the 4 objects being referenced in
> the view, unless I grant select permissions to the objects that I want to
> restrict.
> What am I missing here?
>
The ownership chain is broken.
See
Using Ownership Chains
http://msdn.microsoft.com/library/d...>
ity_4iyb.asp
David

Permissions on a SP

I have created and tested two triggers directly on the local server
and both work as they should. I initially had a problem with
xp_smtp_sendmail that was returning a 500.1
Internal server permissions error, I corrected this with applying the
correct permissions.
Both triggers work when activated from a direct logon but fail without
any error message when activated from the internet. Do I need to create
the necessary permissions on more stored procedures, or can I recreate a
SP from the master DB and copy it, if someone could point me in the right
direction it would be greatly appreciated.
JohnDon't send email from within a trigger. There is really no need to hold open
a transaction while you do this and you don't want to block other
transactions if the XP is slow in returning control to the trigger. Also, yo
u
want to avoid giving users the ability to run XPs in Master, even indirectly
,
if you can. The lack of execute permissions is what is causing this error
message.
Since Email is an inherently asynchronous medium it is easier and better to
schedule this with a SQL Agent based job running at suitable intervals.
Record whatever changes are required in a table and then use a job to pick
those up and send emails.
David Portas
SQL Server MVP
--|||Thanks David for the sound advice.
John
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:10552FB0-890C-4036-80CD-CE40EE612444@.microsoft.com...
> Don't send email from within a trigger. There is really no need to hold
> open
> a transaction while you do this and you don't want to block other
> transactions if the XP is slow in returning control to the trigger. Also,
> you
> want to avoid giving users the ability to run XPs in Master, even
> indirectly,
> if you can. The lack of execute permissions is what is causing this error
> message.
> Since Email is an inherently asynchronous medium it is easier and better
> to
> schedule this with a SQL Agent based job running at suitable intervals.
> Record whatever changes are required in a table and then use a job to pick
> those up and send emails.
> --
> David Portas
> SQL Server MVP
> --

Permissions Not Visible - SQL 2005 Management Studio

I'm trying to apply some security to my database, so i've created a role, added my user to the role and then set the security rights for the role. Everything work great, the security right behave exactly as defined...
However, when I go back the the Permissions pages, the values that i've already set don't appear in the lists (the all still work). In Fact they don't show up anywhere either in the permissions for the table / sp concerned or the right for the user or role.
Is it just me, or is it a problem with SQL 2005 Management Studio? or can anybody suggest a way of finding the securty rights of any given database object?
Microsoft SQL Server 2005 Beta 2
Microsoft SQL Server Management Studio 9.00.1116.00
Regards
Gary T

Security in SQL Server 2005 is Schema based, try these articles to get started. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3481751

http://www.windowsitpro.com/Article/ArticleID/42031/42031.html?Ad=1

|||

This has got me completely confused !!!!
In my simple world...
I would create a database role and the give this role the required rights to my database objects i.e the tables (select, insert update etc), stored procedures (execute etc). I would then create user accounts and add them to the required database roles...
CREATE ROLE [MyDB_AccessRole]
...Table def's...
GRANT SELECT ON [dbo].[MyTable] TO [MyDB_AccessRole] ..etc
...SP defs ...
GRANT EXECUTE ON [dbo].[MySP] TO [MyDB_AccessRole] ..etc
CREATE USER [DBUser] FOR LOGIN [DBUser]
EXEC sp_addrolemember N'MyDB_AccessRole, N'DBUser'

So far so good... this still all works in SQL 2005 (althought it nevers shows the rights anywhere?)
Then we have schemas...
CREATE SCHEMA [MyDB_AccessSchema] AUTHORIZATION [MyDB_AccessRole] ?

Is is true that the schema is just like the role? (i.e. A collection of database objects and permissions) if so, why do we need both.
Has anybody any simple examples (for dummies like me) that would explain the different between a role and a schema.
Having tried to create a role, I stiil don;t seem to be able to see the rights the user, role or schema has.
regards
Gary T

Permissions not effective for Windows Authentication login

Hello All,

I'm hoping someone can help me with this puzzle.

Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....

My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -

SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.

So I decided to try the simplest possible scenario to make it work:

I've created a login DOMAIN\newEmployee with Windows authentication.

DOMAIN\newEmployee has been granted access to databasename.

By default, DOMAIN\newEmployee is a member of Public.

Public has been granted all available permissions on all objects.

ie... grant all on userTables to public

........grant all on sysobjects to public

........grant all on otherSystemTables to public

etc.

Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAIN\newEmployee, querying -

select * from dbo.sysobjects

via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).

To compare, I then created a login newEmployee2 with SQL Server authentication.

newEmployee2 has been granted access to databasename.

select * from dbo.sysobjects

runs successfully from Query Analyser (as to any queries on user-created tables).

What else is required to grant access to tables from a Windows authenticated login?

( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )

Any help would be appreciated.

Thanks.

Kim.

Moved to Security.|||

Let me see if I understand the scenario, please correct me if I am missing something:

· DOMAIN\newEmployee & DOMAIN\newEmploee2 are Windows domain users

· DOMAIN\newEmployee is a member of existingRole

· public has been granted the permissions you mentioned.

· DOMAIN\newEmploee2 can select from dbo.sysobjects

· DOMAIN\newEmploee cannot select from dbo.sysobjects and gets back a permission denied error

From your description, it seems like the most likely cause is that newEmployee has an explicit denied permission on dbo.sysobjects either directly or via a role membership. Check the permissions for all the roles and groups that newEmployee is a member of.

BTW. Some of the objects and permissions that you mentioned here are deprecated, they will still work on SQL Server 2005, but they are supported only for backwards compatibility.

Let us know if this information was useful or/and if you have further question, please also let us know what version of SQL Server you are using in order to better assist you.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

D'oh !! I've checked db_denydatareader instead of db_datawriter.

I'll now crawl into a hole and die of embarrassment.

Thanks for the help.

Kim.

Permissions for Stored Procedures generated by VS 2005

I have a DataSet (Data Component in Beta 1) and I want to add Fill andGet methods by using a Stored Procedure that was created by VS 2005 (aspnet_Membership_GetAllUsers). I probably need to use Enterprise Manager to do so but I am not sure what permissions I need to set and how to set them.

Stored Proc permissions are DBO(database owner). Hope this helps.