SQL Server roles & ASP.NET 2.0 Forms Authentication Roles

SQL Server Security

Index ‹ SQL Server

- Previous
  - 1
    - Maintenance Plan Job Failing, but MP History Reporting Success? I have a very similar problem, also using SQL Server 2005 SP2 and Windows Server 2003 SP2, with a mainteance plan that does a full database backup on the weekends. It has failed the last two weekends on the first step, which is the Check Database Integrity task, with the following message: Message Executed as user: DBServer\sqlsvc. ...e End Progress Progress: 2007-07-01 01:00:11.91 Source: Check Database Integrity Executing query "USE [GPNEU] ".: 100% complete End Progress Progress: 2007-07-01 01:00:12.86 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-07-01 01:00:12.86 Source: Check Database Integrity Executing query "USE [GPNW] ".: 100% complete End Progress Progress: 2007-07-01 01:00:13.83 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-07-01 01:00:13.84 Source: Check Database Integrity Executing query "USE [GPVSI] ".: 100% complete End Progress Progress: 2007-07-01 01:00:15.33 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-07-01 01:00:1... The package execution fa... The step failed. I have also checked logs files, event viewer, job history and can find nothing but the generic message: The package execution failed. I cannot even find what step the package is failing on. When I run just the first step (the Check Database Integrity step) by itself, it executes successfully. Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 2
    - odd behaviour when I load a file into a table Hi Enric, Does the package fail I mean does it fail to insert the other rows or it just doesn't insert them but succeeds Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 3
    - High CPU utilization in SQL Server 2005 SP2 (CTP) Some more information: This can be easily reproduced using the ostress utility by simulating 100 simultaneous concurrent connections...the SQL file that is being used is a single procedure call. The wait event is " RESOURCE_SEMAPHORE_QUERY_COMPILE" - we have removed the "SET" options from within the stored procedure. We have also made sure that all queries are parameterized queries and that there are no constants involved. The procedure uses 3 table variables and depending upon the input parameters, different set of queries are used to populate those table variables. End result that is returned to the client application is via a join of 2 out of these 3 table variables. So, from the wait event it definitely looks like we are hitting a query recompilation issue (part of it seems to have been fixed in SP2 but we are not seeing those issues). We have also gone through the whitepaper: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#ETD and have followed the instructions and made sure we are doing the right things. Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 4
    - A third dynamic assembly loading problem ;) There are only a sub-set (13 or something like that, all of them system assemblies) of assemblies that are allowed to leaded from the GAC inside of SQL Server. No user assemblies are allowed to be loaded from the GAC. So what you need to do is to catalog the assemblies you want to load dynamically in the database, and it should work. Niels Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 5
    - T SQL LIKE Hi, Apparently there is something wrong with the site which gives problems when writing expresion with 2 s's. This is the original post: How to give a list of expresions in the LIKE clause SELECT * FROM tblOne WHERE num LIKE ['12345986' '86355634' '67375436'] Greetz, Geert Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 6
    - Microsoft search service kills SQL 2000 server I've moved this into the general engine thread since the question is in no way related to Broker. Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 7
    - Opening sql files opens new instance of SQL Management Studio What version of SQL Server Management Studio do you have 9.00.1399 = RTM 9.00.2047 = SP1 9.00.3042 = SP2 Paul A. Mestemaker II Program Manager Microsoft SQL Server Manageability http://blogs.msdn.com/sqlrem/ Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 8
    - check if there is an existing SQL server installation Hi, Try this.Hope this works for you. select @@Version (or) exec xp_msver both will get you about the details of installed product. Regards, Prashanthi Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 9
    - Precedence Constraints and Sequence Containers Double-click on one of the precendence constraints going into the single sequence container. Near the bottom you will see a drop down that allows you to set it to an OR condition (The constraints will turn into dotted lines) This means that that will execute if ANY of its precedence cnstraints are satisfied. If you leave it set to AND, then ALL of the constraints have to be satisfied, which will never happen in your case since the other 11 sequence containers will never execute. Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 10
    - Top N Sum and Sum for Others In your footer where you compute the sum of all detail rows try to do something like this: Sum(IFF([Value in top 5], Value, 0)) Not sure what would be the correct syntax of the above boolean expression [Value in top 5] but I would first start to research/experiment with this. Hope it helps, Dom. Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 11
    - Identity column value increments by 2 rather than 1 on insert I tested what you describe and the identities are implemented by one, as expected. Can you post the code of your procedure Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 12
    - Getting Access Denied To FileName Error When Using the Execute Sql Task (With File Connection) into a Foreach Loop Container. How are you executing the package . the error is clear, the account running the package has not access to the drive. Also make sure ou are passing the path and the file name to the Execute SQl task. Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 13
    - Need help with a sp Hi, The question is pretty vague. Can you please explain more what the context is of your problem. What do you want if you have records like this: TABLE FRUITS Id Value1 Value2 1 Apple Banana 1 Apple Melon 1 Apple null 1 grape null 2 Plum Melon 2 Plum null 3 berry null Which rows do you want to be returned for ID=1 Greetz, Geert Geert Verhoeven Consultant @ Ausy Belgium My Personal Blog Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 14
    - How can I generate INSERT statements from Management Studio No, you can’t SSMS does not support that yet. You can create the statements using the script from here: http://vyaskn.tripod.com/code.htm#inserts Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 15
    - how to design database for chinese and japanese characters Moving to SQL Server Setup and Upgrade Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 16
    - Get the error in dos cmd line I really need to know if there is some variable that gives me the sql error because when I execute the cmd, if there's an error the error is printed int the cmd window. The errorlevel var just give me the number "1" but I would like to get something like "An error in database ocurred due to database already exists" or something like that. Please I'll apreciate any ideias you may have Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
- Next
  - 1
    - Data-Driven Subscription to Excel with labeled worksheets Question A few months ago there was a thread on this: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1713177&SiteID=1 which notes that out-of-the-box, SSRS does not support named tabs when multiple pages are output to Excel. That said, Lisa Nicholls recently provided a link at the end of the thread to her blog that discusses how to achieve your goal using XML output and XSLT. You might give this a try to see if it works for you. http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx Scott Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 2
    - SSAS Best Practice It depends on the situation, but what type of layer were you thinking of Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 3
    - distribution cleanup cannot clean up snapshot folder open up your distribution clean up task job, and in the job step properties do the following: setuser 'sqlrepadmin' GO EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72 GO Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 4
    - define/set parameter values in Management Studio? Unfortunately, I don't believe their is an easy and straightforward way to do this. About the only option I've been able to find is wrapping the MDX query in an XMLA query, which allows you to have parameters and define their values. The problem with this approach is that the result of the XMLA query is an XML response which contains a lot of metadata as well as the data (but it is not in any type of format that would allow you to easily look at just the query results). Here's a link to a topic in BOL that shows an example of this: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/a4754d16-d9c4-49f6-9be0-392180b912e4.htm If your query is a relatively simple one that returns a relatively simple result, this approach might work... HTH, Dave Fackler Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 5
    - Best practices - currency elements Could this be a problem with the specification of the database column For example: Code Snippet declare @testo table(myTotal decimal , dec_9_2 decimal(9,2) ) insert into @testo select 5, 3 --select myTotal from @testo select myTotal from @testo for xml path('') /* XML_F52E2B61-18A1-11d1-B105-00805F49916B ----------------------------------------- <myTotal>5</myTotal> */ select dec_9_2 from @testo for xml path('') /* XML_F52E2B61-18A1-11d1-B105-00805F49916B ----------------------------------------- <dec_9_2>3.00</dec_9_2> */ In the first query the source column is simply defined as a DECIMAL column and is displayed without any fractional "decimal" portion. When this column is converted to XML it displays only the whole number portion because really, the data consists of whole number only. In the second query the source column is defined as DECIMAL (9, 2) column. This provides for 7 whole number digits and 2 decimal digits. When this column is converted to XML it displays the desired decimal places. Can you provide the DDL for your source column Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 6
    - SQL Command Datareader Do you need to use the DataReader Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 7
    - suser_sid returns value for nonexistent login sp_adduser depends on being able to access the (deprecated) catalog views in master DB in order to work. Some restrictions to access catalog views are new in SQL Server 2005, and old (deprecated) code does not work when there are restrictions on them. Most likely the login exists, but the principal executing sp_adduser doesn’t have permission to access the metadata for the login. I would recommend trying the following code to verify the existence of the login: CREATE USER [COMMERCIAL DIRECT] go The CREATE USER DDL doesn’t rely directly on the catalog views, and given that suser_sid returns a non-null value, it should work. If the statement above works, then most likely the principal you are using to create the users doesn’t have permission to access the catalog view. You can also verify if the login exists using a privileged (i.e. sysadmin) account. SELECT * FROM sys.server_principals WHERE sid = suser_sid ( N 'COMMERCIAL DIRECT' ) I hope this information helps. -Raul Garcia SDE/T SQL Server Engine Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 8
    - Count the occurance of a character in a string select len(@s) - len(replace(@s, 'a','')) Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 9
    - What technique to use to update production table from staging table 1: Probably not. 2: You could use sp_rename. You'd have to drop the other table first. 3: The checksum function might help with this, but the built-in SQL Server function tends to have a high collision rate (same checksum value for different input values). You also need a primary key to compare on. So you'd do a join between the tables on the primary key, run all the columns from each table into its own checksum function, and include a WHERE clause looking for checksum values that don't match. Not sure how good performance will be. It helps if you can modify the table definition to include the checksum value as a column, so you don't have to do the calc each time. Hope this helps. Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 10
    - truncation error message Hi Have you tried adding "Derived Column transformation". Worked for me. In "Derived Column" select Replace '<your column name>' In Expression - SUBSTRING(<your column name>, 1, 50 ) This should truncate your column to the appropriate length NB Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 11
    - Error in executing stored proceduNot a valid identifier Hey I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go Create PROCEDURE [dbo] . [GSU_Site_ReterieveActiveSitesOnSearch] @whereClause nvarchar(2000) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. declare @sqlstr as varchar ( max ) set @sqlstr = 'SELECT Site.siteid as siteid,' set @sqlstr = @sqlstr + 'Site.Sitename as sitename, ' set @sqlstr = @sqlstr + 'Customer.customerid,' set @sqlstr = @sqlstr + 'Customer.customername as CustomerName,' set @sqlstr = @sqlstr + 'Site.City as City,' set @sqlstr = @sqlstr + 'site.Address as Address,' set @sqlstr = @sqlstr + 'Site.state , ' set @sqlstr = @sqlstr + 'Country.countryid as countryid,' set @sqlstr = @sqlstr + 'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,' set @sqlstr = @sqlstr + 'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ' set @sqlstr = @sqlstr + 'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid ' set @sqlstr = @sqlstr + 'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON ' set @sqlstr = @sqlstr + 'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID ' set @sqlstr = @sqlstr + @whereClause -- --set @sqlstr=@sqlstr+' WHERE GSUStatus.GSUStatusID=' +@GSUStatusID --if @BusinessUnitID <> 0 --set @sqlstr=@sqlstr+'and site.BusinessUnitID ='+@BusinessUnitID --if @CountryID <> 0 --set @sqlstr=@sqlstr+'and site.countryid='+@CountryID --if @CustomerID <> 0 --set @sqlstr=@sqlstr+'and site.customerid='+@CustomerID --if @SystemTypeID <> 0 --set @sqlstr=@sqlstr+'and site.SystemTypeID='+@SystemTypeID --if @SiteName <> '' --set @sqlstr=@sqlstr+'and site.Sitename like ' + @SiteName --if @Address <> '' --set @sqlstr=@sqlstr+'site.Address like '+ @Address --if @City <> '' --set @sqlstr=@sqlstr+'site.City like '+ @City --if @State <> '' --set @sqlstr=@sqlstr+'and site.state like '+ @State print @sqlstr exec @sqlstr END I executed the procedure by pasing parameters Exec [GSU_Site_ReterieveActiveSitesOnSearch] " where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' " and getting the following error - exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException} Please let me know the problem in this. Thanks Kusuma Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 12
    - OWC scans all partitions (SSAS 2005) Hi Nilgun, First of all, have you set the Slice property on your partitions Although in general AS is able to detect what data is in each partition, read the following article for details on when it will and won't work: http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx In your case it sounds like it will be quite straightforward to set the slice, and this will also detect whether your partitions hold the data you think they hold (which could be another reason why you're seeing AS scan all partitions). If you have set the slice, however, you could be running into an issue I've seen before and which I think is fixed in the latest cumulative hotfix build for SP2: http://support.microsoft.com/kb/936305 Look for the brief description under issue 50000890. HTH, Chris Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 13
    - SQL Server Mobile Mark, Your're right. The Database Diagram tool in the Server and Object explorers do not work with SQLCe databases. While you can create relationships using SQL statements, you might simply want to create a new Data Source in VS and add several related TableAdapters. The relationship tool in the TableAdapter Configuration Wizard does work. I describe how this is done in my EBook. See www.hitchhikerguides.net . hth Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 14
    - Conditional Bulk insert Try INSERT INTO Categories SELECT CategoryId, @LastUpdate FROM OPENROWSET ( BULK @filePath , FORMATFILE = @formatFile, FIRSTROW = 2 ) AS a where a.CategoryID not in (select distinct CategoryId from Categories ) Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 15
    - XML Configuration Issue Hi Matt, Do you have multiple script tasks in your package. Can you provide more info on the composition of the package and where this variable is being used. Thanks Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles
  - 16
    - Management Studio and Enterprise Manager on Same Machine? Yes, it's OK, I've done it. But it's not recommended for a production machine. Uninstalling either may cause either to not function properly. Tag: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles

Mark Delderfield

Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.

Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.

If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.

Many thanks,

Mark

Top

Re: SQL Server roles & ASP.NET 2.0 Forms Authentication Roles

Buck Woody - MSFT

I think this might help:

http://msdn2.microsoft.com/en-us/library/ms998317.aspx

Top