Publc role has been grannted 'SELECT' privelege to syslogins and sysusers tables in the Master and GTSS database
The syslogins table contains all the logins that were created on the server. The sysusers table contains the users that are mapped to the database. Unauthorised access to these tables would reveal critical authentication info of other users
Restrictive permissions should be configured on critical database tables such as sysusers and syslogin.
I have an application that uses Integrated Windows authentication. My Web.config looks like below <add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" /> When users try to access my application, they get the below error: Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo' The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse. Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO. Thanks,
I have somme users who need to truncate some tables owned by DBO. I know that only table owner or DBO can execute TRUNCATE TABLE but I don't want grant DBO permission to those users. Do you have any suggestion ? Thanks a lot.
I know that only the owner of a table can truncate it. I want a user to run a procedure that truncates a table. Both the procedure and the table are owned by dbo, however the system says that the user can't truncate the tables because she doesn't have permission. The user has dataread and datawrite global permissions, and I do not want to give her dbo perimssions. Is there a solution to this?
I gave a user explicit permission to create table, but still gettingthis error:Property Default Schema is not available for database[DBNAME]. Thisproperty may not exist for this object or may not be recoverable due toinsufficient access right. Microsoft.SQLServer.Express.SQLEditorsDo I need to create a new schema?--sharif
I have the following requirementI am creating a login and database user 'test' on a database with dborole .I want to remove create table , alter table permisions to this user.I am able to revoke create table permission but alter table goesthrough.I gave a command deny insert,delete,update on ssycolumns to test.Still I am not able to prevent user altering schema . Alter tablesuccessfully goes throgh.I do not want to use datreader and datwriter role.since I want user 'test' to create storred procedure with dbo ownerIs there a way to achieve this ?ThanksM A Srinivas
I am trying to create a set of "Reporting" views and grant Select permission on those views instead of the tables. I created the view in the same database but under a different schema from the tables. When the user tries to select from the views they get an error saying they do not have select privilege on the tables. Do I need to grant them select on the underlying tables?
question 1 : I create a login account and a user account ,and then grant create table permission to the user. But the user can't create table yet,it return a error message: The specified schema name "dbo" either does not exist or you do not have permission to use it
But i can find the dbo scheam by querying sys.schemas.
the sql script i execute:
(sa login)
Code Snippet use tempdb go create login l1 with password='a' ,check_policy=off go create user u1 for login l1 go grant create table to u1
Code Snippet (l1 login) use tempdb create table table1 ( col int )
--fail --error message:The specified schema name "dbo" either does not exist or you do not have permission to use it
question 2 : The granularity of "grant permission to useraccount" command is database,while i want to get a table level of granularity.. how?
Hi,i run an asp.net application which uses sql server express.i defined a login 'aspnet' (IIS 5.0) and for the specific database, an user 'aspnet' with following roles:db_datareader and db_datawriter.Now, any user who uses that application must also be able to create programmatically tables in that database. My question is: which role do i have to give to user 'aspnet'? I use Studio Management express.ThanksTartuffe
I am having problems running a stored procedure that was created by the sa account. The stored procedure creates a temp table ( #temp_basf) and performs a bulk insert into it. The user account has execute privileges against the stored procedure. The error I am getting is:
The current user is not the database or object owner of table '#temp_basf'. Cannot perform SET operation.
Here is the code from stored procedure:
create procedure processFarmChemDataFile as begin create table #temp_basf (RTUId varchar(15), LName varchar(20), Address1 varchar(50), City varchar(20), StateCode varchar(9), ZipCode varchar(15), CName varchar(30), Voice varchar(30), Name varchar(25), TankCap varchar(15), TankHgt varchar(9), notUsed varchar(20), ProdDesc varchar(20), InvTime smalldatetime, GrVolume varchar(15)) end begin bulk insert #temp_basf from 'c: estdata.txt' with (FIELDTERMINATOR = ',',ROWTERMINATOR = '') end begin update #temp_basf set RTUId = replace(RTUId,'"',''), LName = replace(LName,'"',''), Address1 = replace(Address1,'"',''), City = replace(City,'"',''), StateCode = replace(StateCode,'"',''), ZipCode = replace(ZipCode,'"',''), CName = replace(CName,'"',''), Voice = replace(Voice,'"',''), Name = 'Tank '+replace(Name,'"',''), TankCap = replace(TankCap,'"',''), TankHgt = replace(TankHgt,'"',''), notUsed = replace(notUsed,'"',''), ProdDesc = replace(ProdDesc,'"',''), InvTime = replace(InvTime,'"',''), GrVolume = replace(GrVolume,'"','') end begin select CustomerId,LocationId,TankId,Temp,Alarms,SpecGrav, CoeffExp,ProdCode,a.ProdDesc,UserProdId,UserTankId, b.InvTime,b.RTUId,b.GrVolume,ChartId from Tank a inner join #temp_basf b on a.Name = upper(b.Name) end
Why would we get the error 229 Select Permission on object denied when the user has select permissions granted? All tables in the database are accessible by the user but this one. All tables for this user have the same permissions: Select, Insert & update.
I noticed that the indexes were not set up correctly but even after fixing them it didn't help. what other reasons would cause this error.
Can any body tell me how can I restrict a user who has Sa previlages, from droping a table. He should be able to do everything except droping the table.
Does anyone know how i can grant select permission on more than one table in the same database using a single grant commmand....its painful to use grant statement seperately on each table
I have a stored procedure that I am writing that is giving me trouble dropping a temp table in SQL Server 2005. I have to use the SQL in Query 1, marked below, to get the temp table. I can find the table but I get the error listed in this message's subject. If I try to run DROP TABLE #ZipLatLong I will get an error telling me that the table already exists but then I can run CREATE TABLE #ZipLatLong ( ZIP INT, Latitude DECIMAL(18,6), Longitude DECIMAL(18,6)) and that will work...
In short, SQL Server 2005 is not letting me drop temporary objects. I am getting return messages telling me that an object already exists in the database by the name I am trying to delete and then it will let me create a table with the same name...
Figure 1 USE TEMPDB GO SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb' AND TABLE_SCHEMA = USER AND TABLE_NAME LIKE '#ZipLatLong%' GO DROP TABLE #ZipLatLong GO
I am trying to grant solely the SELECT privilige to a particular user and to a particular database.I am using Sql server management studio express to do this and SQL Server 2005.
I have created a new login and set the server roles to public, then on the user mapping page, I have checked the box next to the table I wish the user to access and have selected the database membership role - public. Next, On the Table Properties, I have added the user on the Permissions Page and Selected "Select" under the grant column
When I click on effective permissions, the select command does not show and when I try to login with that user, the table does not display.
I have 32-BIT MS SQL 2005 running on Windows 2003 R2 platform. The code is 100% bug-free and works fine on staging and production. Production has an issue AT RANDOM TIMES. Most of the time, it works fine...connections are pooled and reused. Out of nowhere (very randomly), it will start opening new connections for each request and keep doing that until the DB server crashes (could not open connection exception). If I restart IIS, it works fine again...all connections are being reused (no more than 6 connections). Just for fun, I restarted IIS again....it starts opening new connections for each request!! I restarted again, it now reuses existing connections. What's going on??? This has occurred 2 times on our production box. .NET ALSO has a SERIOUS bug where if you nest master pages or user controls, sometimes it will throw a compilation error on a LIVE site (microsoft admits that it's a bug in the engine and currently...there is NO FIX for it..there are patches...none of them work).
I get this error when I try to access one of my tables.
Msg 605, Level 21, State 1 Attempt to fetch logical page 4377 in database 'maillist' belongs to object '1340531809', not to object 'client'.
I know that when my database is restarted it will be marked suspect because of this error. Does anybody know: What causes this error? Why I keep on getting it? How do you fix it?
I am having a big issue now. I made what I thought would be a simple change to our reporting services application, which has been running smoothly for about 2 years. The change has caused my "forms authentication" to start throwing an error that I rember from a while back while testing. Our rporting server is down, sad to say. Has anyone had this problem.
The error : Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message:...
Changes made 1. Stoped RS web app in IIS 2. Changed the ASP.NET Config Settings(Report Server App)
a. Authentication Tab - cookie time out (changed to 60) b. State Manegement Tab - Session Time Out (changed to 60) 3. Changed the ASP.NET Config Settings(Our aps.net app that displays the reports)
a. Authentication Tab - cookie time out (changed to 60) b. State Manegement Tab - Session Time Out (changed to 60) 4. Changed the SessionState Timeout property in the web config file in our web app that displays the reports. 5. Restarted the Report Server app. 6. Recycled the app pools
Could not log in! I am catching the above error in my web app's ReportExecution.LogonUser(). I rebooted the server and continue to get sqldump logs and this exception. This error occured when I was testing the redirect a while back but that issue was resolved. The only changes I have made are above.....
source flat file containg row having length 803, frm that i parse the fields of diff lengths ,record type field is one of them,
after flat file i took derived column to parse the record types(i.e type1,type2, .......) later i took conditional split and split those record types in to diff derived columns,
problem starts now ?
i got 500 records before conditional split and after condi..split.. they r 499,
Does anyone ever face this sort of error? We were launching a SSIS from our .Net console:
0x80070002 while loading package file "C:Documents and Settingsadminsql2k5Local SettingsApplication DataMicrosoftSQL ServerSmoInnerPackage.dtsx". The system cannot find the file specified.
InnerPackage.dtsx is a sort of template for SSIS??
SQL Server 2005 anomoly? In SQL Server Management Studio I granted specific permissions to user "A" to do Select, Insert, Update, Delete on Table "B" - When I logged on as User "A" and attempted the Insert imto table "B" I got the following error: "Insert Permission Denied on Table B, Database C, Schema dbo" Is this a problem with the dbo schema?
Then I went back and created a stored proccedure "D" with the exact same Insert statement inside the procedure. I granted User "A" execute permission on the stored procedure "D". I then logged on as User A and executed Stored Procedure "D". No Problem - stored procedure executed fine with the Insert. I attempted the Insert statement again - straight SQL - as User "A" and got the same error as above ("Insert Permission Denied.....") Strange behavior - cannot do a SQL. Insert even though user has permissions but can execute a store procedure with the same Insert statement. What gives?
How to grant DROP table permission within a database to an SQL login. I could see in Databae properties-> permission tab, there is an option to grant CREATE TABLE, EXECUTE permission etc, but not DROP table permission. How to grant it?
Dear all I am a pretty new in the development world fresh from uni. I am doing development on a system that has a security database. Access to the data in that database is pretty important. So in case the main server where the database is stored for soem reason fails or gets compromised i need to have a second copy with the most recent data in that database and keep the application up and running. The data i have is stored in a SQL 2005 database. What are the recomended aproaches for acheiving this needed reliability? Would running the SQL Agent every 2 minutes do the trick? And replicate the database to another server and then have asecondary deployment on that server running as a backup? Or are there any other means? Any advice is apreciated. Sincerely
Our SQL server keeps crashing with the following error. When it crashed it completely shut down the server. Could you please give me advice on how to stop this from happening again? I would like to thank in advance for your help.
A MS DTC component has encountered an internal error. The process is being terminated. Error Specifics: A non-MS DTC XA Library threw an exception in function olog ntdll!KiFastSystemCallRet + 0x0 + 0xd58c3c0
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Here is the information of our server
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition Version 5.2.3790 Service Pack 1 Build 3790 Other OS Description Not Available OS Manufacturer Microsoft Corporation System Name SQL2387 System Manufacturer Dell Computer Corporation System Model PowerEdge 2850 System Type X86-based PC Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz BIOS Version/Date Dell Computer Corporation A04, 9/22/2005 SMBIOS Version 2.3 Windows Directory C:WINDOWS System Directory C:WINDOWSsystem32 Boot Device DeviceHarddiskDmVolumesSQL2387Dg0Volume1 Locale United States Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)" User Name Not Available Time Zone Eastern Standard Time Total Physical Memory 4,095.08 MB Available Physical Memory 1.75 GB Total Virtual Memory 1.83 GB Available Virtual Memory 3.81 GB Page File Space 2.00 GB Page File C:pagefile.sys
I want to grant access on the below view for an end user so that he connect to our SQL server and retrieve data. The view looks like the below
CREATE VIEW DB1.[dbo].[View1] AS -- For brevity, I made it as simple statement. SELECT * From DB2.dbo.table2 GO
For the above view, it looks like I have to grant select and connect permission for the DB1. [dbo].[View1] as well as DB2.dbo.table2.
1. Is my understanding correct?
2. I want the user to access only DB1. [dbo].[View1] and not the underlying tables. Is there a way to grant access only on the view and execute the statement on a different security context so that the user can€™t access DB2.dbo.table2 directly?
3. When the user uses SQL Server Management Studio to connect to SQL server, he is able to connect and select DB2.dbo.table2 directly. Is there any way to restrict user from viewing and executing select statement on DB2 database from SQL Server Management Studio
We developed an application in the ASP.Net with C#. We are going to do a task in the transaction. It is working perfectly. But if two users comes simultaneously, then It gives an exception that Transaction is already commited.
First transaction Commited Successfully. When Second transaction goes to Commit() Then it generate this error. We are unable to find this problem Whether this is problom from SQL Server or DOTNET.
We need to apply the transactional log for business hours (9-5) everyday to a oracle instance which will be seeded with SQL Data for once. And then every day the transactional log would be applied.
We need to do this as eventually this server is moving to oracle.
At this point if somebody could help me to read the transactional log that would be great.
