How to determine missing permissions ?

I've been tediously working on setting up scripts to limit access through
our different departments accordingly. I've been working at creating the
tightest set of perms first but can't seem to get it working. I've posted
the script obviously but figured I'd ask if there were some way to determine
what permissions are required when I get an access denied error...
In the script it is assumed that there are already 3 logins created and
added to the current database. I know there is alot to go on the script as
far as error checking, previous existence etc....but in a controlled
environment (my localhost at this time) this should work. The problem is
after running this I couldn't even open and view db properties...I had to
drop and recreate the user from login. Then I still couldn't select data
from any table.
THANKS!!!! For any input. I know I'm missing it somewhere here bigtime.
----
--
-- LIVE_LimitedWebAccess --
-- creates permissions applicable to a live environment for web
----
--
----
--
-- create the role that will be used to limit access
----
--
CREATE ROLE [LimitedWebAccess] AUTHORIZATION [dbo]
----
--
-- add logins to the new role
----
--
EXEC sp_addrolemember N'LimitedWebAccess', N'web'
EXEC sp_addrolemember N'LimitedWebAccess', N'webdev'
EXEC sp_addrolemember N'LimitedWebAccess', N'wtf'
----
--
-- loop thru all tables setting permissions appropriately for the new role
----
--
declare @.strName nvarchar(100)
declare @.strSQL nvarchar(1024)
declare curTables cursor for
select [name] from sys.tables where type='U'
open curTables
fetch next from curTables into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curTables into @.strName
end
close curTables
deallocate curTables
----
--
-- loop thru all views setting permissions appropriately for the new role
----
--
declare curViews cursor for
select [name] from sys.views
open curViews
fetch next from curViews into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curViews into @.strName
end
close curViews
deallocate curViews
----
--
-- loop thru all stored procs setting permissions appropriately for the new
role
----
--
declare curProcs cursor for
select [name] from sys.procedures where is_ms_shipped=0
open curProcs
fetch next from curProcs into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curProcs into @.strName
end
close curProcs
deallocate curProcs
----
--
-- loop thru all user functions setting permissions appropriately for the
new role
----
--
declare curFuncs cursor for
select [name] from sys.objects where type='FN'
open curFuncs
fetch next from curFuncs into @.strName
while @.@.fetch_status=0
begin
set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [LimitedW
ebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
[LimitedWebAccess]'
exec sp_executesql @.strSQL
fetch next from curFuncs into @.strName
end
close curFuncs
deallocate curFuncs> the script obviously but figured I'd ask if there were some way to
> determine what permissions are required when I get an access denied
> error...
I have a couple questions,
Does the login have sys_admin privileges?
does the user have db_owner fixed database role privileges?
Have you issued GRANT ON SELECT.... for more details please refer to the
BOL
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:e$gFbWPiGHA.3296@.TK2MSFTNGP05.phx.gbl...
> I've been tediously working on setting up scripts to limit access through
> our different departments accordingly. I've been working at creating the
> tightest set of perms first but can't seem to get it working. I've posted
> the script obviously but figured I'd ask if there were some way to
> determine what permissions are required when I get an access denied
> error...
> In the script it is assumed that there are already 3 logins created and
> added to the current database. I know there is alot to go on the script
> as far as error checking, previous existence etc....but in a controlled
> environment (my localhost at this time) this should work. The problem is
> after running this I couldn't even open and view db properties...I had to
> drop and recreate the user from login. Then I still couldn't select data
> from any table.
> THANKS!!!! For any input. I know I'm missing it somewhere here bigtime.
> ----
--
> -- LIVE_LimitedWebAccess --
> -- creates permissions applicable to a live environment for web
> ----
--
>
> ----
--
> -- create the role that will be used to limit access
> ----
--
> CREATE ROLE [LimitedWebAccess] AUTHORIZATION [dbo]
> ----
--
> -- add logins to the new role
> ----
--
> EXEC sp_addrolemember N'LimitedWebAccess', N'web'
> EXEC sp_addrolemember N'LimitedWebAccess', N'webdev'
> EXEC sp_addrolemember N'LimitedWebAccess', N'wtf'
>
> ----
--
> -- loop thru all tables setting permissions appropriately for the new role
> ----
--
> declare @.strName nvarchar(100)
> declare @.strSQL nvarchar(1024)
> declare curTables cursor for
> select [name] from sys.tables where type='U'
> open curTables
> fetch next from curTables into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curTables into @.strName
> end
> close curTables
> deallocate curTables
>
> ----
--
> -- loop thru all views setting permissions appropriately for the new role
> ----
--
> declare curViews cursor for
> select [name] from sys.views
> open curViews
> fetch next from curViews into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT DELETE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT INSERT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY REFERENCES ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT SELECT ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='GRANT UPDATE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curViews into @.strName
> end
> close curViews
> deallocate curViews
>
> ----
--
> -- loop thru all stored procs setting permissions appropriately for the
> new role
> ----
--
> declare curProcs cursor for
> select [name] from sys.procedures where is_ms_shipped=0
> open curProcs
> fetch next from curProcs into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curProcs into @.strName
> end
> close curProcs
> deallocate curProcs
>
> ----
--
> -- loop thru all user functions setting permissions appropriately for the
> new role
> ----
--
> declare curFuncs cursor for
> select [name] from sys.objects where type='FN'
> open curFuncs
> fetch next from curFuncs into @.strName
> while @.@.fetch_status=0
> begin
> set @.strSQL='GRANT EXECUTE ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY ALTER ON [dbo].[' + @.strName + '] TO [Limite
dWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY CONTROL ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY TAKE OWNERSHIP ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> set @.strSQL='DENY VIEW DEFINITION ON [dbo].[' + @.strName + '] TO
> [LimitedWebAccess]'
> exec sp_executesql @.strSQL
> fetch next from curFuncs into @.strName
> end
> close curFuncs
> deallocate curFuncs
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFFpF6SiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> I have a couple questions,
> Does the login have sys_admin privileges?
> does the user have db_owner fixed database role privileges?
Do you mean for the user running the script? If so I'm running the script
as 'sa'. If you mean the user 'web' then absolutely not. We want to
restrict them to selecting/updating data. As shown in the script below they
are explicitly granted select on all tables/views...

> Have you issued GRANT ON SELECT.... for more details please refer to the
> BOL
I have. That is how I got this far...but I'm having trouble now finding
what is missing. I don't want the user 'web' to have sys_admin privileges.
But with the script below the user 'web' now gets 'Invalid object name
'tblname'' when trying to select data from a table.

>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:e$gFbWPiGHA.3296@.TK2MSFTNGP05.phx.gbl...
>|||Tim
> But with the script below the user 'web' now gets 'Invalid object name
> 'tblname'' when trying to select data from a table.
Pobably because the "web" user/login is not the owner of the "tblname", Can
you check it?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:eoG8BuXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eFFpF6SiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> Do you mean for the user running the script? If so I'm running the script
> as 'sa'. If you mean the user 'web' then absolutely not. We want to
> restrict them to selecting/updating data. As shown in the script below
> they are explicitly granted select on all tables/views...
>
>
> I have. That is how I got this far...but I'm having trouble now finding
> what is missing. I don't want the user 'web' to have sys_admin
> privileges. But with the script below the user 'web' now gets 'Invalid
> object name 'tblname'' when trying to select data from a table.
>
>
>

How to determine default values of a field during runtime

Hi, i am using the function below (visual basic 6) to determine several
properties of
each field in a recordset (SQL server) and all is working well. My
problem is that I
dont know how to find the default value of a field from the table. Does
anyone have
any code suggestions that would get me the default value
For Each F In rstProgressData.Fields
If F.Type <> adChapter Then
If F.Name <> "upsize_ts" Then
rstDest.AddNew
rstDest!Progress = F.Name
Select Case F.Type
Case adChar, adVarWChar, adVarChar
rstDest!ProgressFieldType = "String"
rstDest!ProgressFieldSize = F.DefinedSize
Case adBoolean
rstDest!ProgressFieldType = "Boolean"
Case adSmallInt, adUnsignedTinyInt, adInteger
rstDest!ProgressFieldType = "Integer"
Case adDecimal, adNumeric
rstDest!ProgressFieldType = "Decimal"
Case adDBTimeStamp
rstDest!ProgressFieldType = "DateTime"
Case 203
rstDest!ProgressFieldType = "Memo"
End Select
If (F.Attributes And adFldIsNullable) = adFldIsNullable Then
rstDest!ProgressFieldNullable = True
End If
rstDest.Update
End If
End If
Next FThats hard to see where your recordset is based on if you don=B4t send
the query with you, but otherwise the information can be queried
through the INFORMATION_SCHEMA Views:
Select Column_default from INFORMATION_SCHEMA.COLUMNS
Where table_name =3D '<SomeTable>'
HTH, Jens Suessmeyer.|||The code has to be generic so that it can deal with any sql statement.
As above for any query I can get the field size, type, name & if its
nullable, I just need to find out what syntax to use to get the default
value e.g F.Type gives me type. F has been defined as an ADODB.Field.sql

How to detect whether SMO is installed?

Hi, folks --
I am working on an install bootstrapper that (among other things)
will install Server Management Objects (SQLServer2005_XMO.msi from SQL
Server 2005 Feature Pack) iff it's not already installed.
So, how to detect programmatically whether or not it's already
installed?
What files/version/registry keys/usual suspects should I be looking
for?
Thanks in advance for your help.
-- Davidson
Hello n7dai@.comcast.net,
I normally look for C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.d ll.
I believe current-most is 9.0.3042.0.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||What about:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=577731&SiteID=1
Jens K. Suessmeyer.
http://www.sqlserver2005.de
<n7dai@.comcast.net> wrote in message
news:1175273193.753957.182290@.e65g2000hsc.googlegr oups.com...
> Hi, folks --
> I am working on an install bootstrapper that (among other things)
> will install Server Management Objects (SQLServer2005_XMO.msi from SQL
> Server 2005 Feature Pack) iff it's not already installed.
> So, how to detect programmatically whether or not it's already
> installed?
> What files/version/registry keys/usual suspects should I be looking
> for?
> Thanks in advance for your help.
> -- Davidson
>

How to de-normalize one to many relationships?

I am a newbie to SSIS. I have been working through SQL Server 2005 Integration Services and am quite pleased with the book and with SSIS. That said, I am having trouble determining how to handle a challenge.

The business challenge is that we need to push new inventory items that we sell from our back end accounting system onto our web site. The challenge is that each item can show up in 1 or 100 categories. The silly web software wants us to write the item and the categories that item is associated with in one transaction / command.

Stated another way, when we push an item into the web store, we need to push the item and an array of categories in the same transaction.

This is my technical challenge, I cannot figure out how to select 100 records out of an item table in MS-SQL and then create an array of categories for each of those 100 items. (items belong to >1 categories)

I thought I could use two OLE DB sources where the main source was the item table and the 2nd source was the category table.

My best guess at this point is to use an OLE DB source for the item table. Then use the script component and hard code the read from the category list within the script component.

As a note, scalability is not really an issue, there would be no more than 10-20 items being pushed at any given time.

Any help would be GREATLY appriciated.

It sounds to me like you need two dataflows: the first to insert the new categories, and the second to lookup the new categories and insert the items. This can be done in a single transaction by setting RetainSameConnection=True on the connection manager for your destination connection, and placing Execute SQL Tasks before the first dataflow and after the second to execute BEGIN and COMMIT TRANSACTION statements respectively.
|||JayH - Thank you for the reply. My original post was not as clear as it should be.

First, our destination is actually a web service which we are accessing via a proxy / assembly. We call the assembly from a reference inside the script component. The goofy web service wants us to write the item record along with the categories to which the item belongs. The categories is basically an array that has to be declared.

Second, the categories are already defined on the web site. Each category has its own category ID. When we write the item record, we reference an array of category id so the web site software understands the relationship between the item and the category.

From a pseudo code perspective:
1) Select items from the item table.
2) Process each item one at a time.
3) For each item, find the associated category id's
4) Count the number of category id's for each item
5) Declare an array of category id's for each item using the count from #4 above
6) Populate the array with the category id's
7) Populate the "item record" with the category id array.
8) Call the web service via the assembly

Yes, the web service I am forced to use is a bit goofy but I cannot change it. :-(

Thank you for any thoughts...
|||Okay, I have a better picture now. I'm still not sure about where the item/category relationship is coming from, but if you can get the rows in an order like the following, then you should be able to create the array pretty easily in script.

Item 1 Category 1
Item 1 Category 2
Item 1 Category 3
Item 2 Category 3
Item 2 Category 2

If the data enters the script sorted by Item, then the script can evaluate each row and build an array of Categories for each item. When the Item changes from the previous row, you know you have all the Categories and you can make your web service call. The last row condition would need special handling and for that you should override the FinishOutputs method in your script so you can call the web service for the last Item.

Let me know if that sounds closer to what you're looking for.
|||JayH,

Yes, you have done a better job of explaining my issue that I have. I believe I comprehend the concept.

I am working on your concept now.

Be back later today. With me luck!

I am so remedial in SSIS...

How to deliver a report dynamically based on the dataset with standard edition?

Hi All,

I am working on a report which displays profit and loss discrepancy between two systems. So if there is discrepancy, in other words, the dataset returns value, then I want reporting serive to deliever the report to intended users. I am currently using standard edition with no data-driven subscription support.

I do not know what is the workaround for this implementation. Tks for advise in advance.

Alex

Sounds like you need a custom application to check the condition. You can use the RS FireEvent API to trigger the subscribed delivery.|||

Tks for your reply. Can we also render report inside SSIS, then send out to users? Not sure if that is possible.

Alex

|||I don't see a reason why this shouldn't work. You can write a script task to call down to the SSRS web service and request the report by invoking the Render method.|||

Great, thanks for your answer.

Also, I have another issue with ReportingService2005.FireEvent API, whenever it gets called, the system threw an exception said "insufficient permission for performing this operation". No idea how to fix it. Thanks in advance.

Sample Code:

rs.FireEvent("TimedSubscription", SubID);

Alex

|||

I don't know why it won't work if you are executing the call as a local admin on your local machine where SSRS is installed. If not, does it work if you add the the Windows identity the call is going under to the local Administrators group? If so, you need grant that user System Admin rights in the Report Manager.

|||

Hi,

I found the answer that by default "Generate event" permission is not granted to anyone. You have to go to report manager, security section to check this item. Tks.

Alex

How to delete tmp file which created by Crystal Report automatically

Hi,
Each time I run the vb application, the crystal report will create tmp file in the C:\ and VB*.tmp in the current working dirctory. How can I delete it automatically? Now, I need to delete it manually, otherwise, the huge tmp file will remind in both directories.
ThanksKill FileName