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.
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
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?
In SQL 2000, a guest user can access any system tables in a database, but it looks not the same in SQL 2005. What even worse is the SQL security handles it in a confused way. When I sign on as sa and run:
SELECT * FROM sys.sysreferences
Of course I get what I want. While, if I sign on as a user only has guest privilege, I get nothing. Based on my understanding, if one does not have permission to SELECT, he should get rejucted error message. Otherwise, he should get return data. Now, SQL 2005 does not give any.
More interesting thing is when select * from sys.sysobjects, SQL 2005 return data to both sa user and user with guest privilege. Did MS applied different security on different sys.sys.. table? When a select return nothing, how can we know if there is no data at all, or the user does not have sufficient privilege.
I created a trigger that will throw a message whenever a new record is inserted in the table. Now I want to remove this trigger. I am not able to remove.
CREATE TRIGGER prod_culture_trig ON Production.Culture AFTER INSERT AS SELECT 'New culture entry added';
I get the following error message:
Msg 2714, Level 16, State 2, Procedure prod_oulture_trig, Line 4 There is already an object named 'prod_oulture_trig' in the database.
This error confirms that we have the trigger already existing. Now I run the code
DROP TRIGGER prod_oulture_trig;
I get the following error message - Msg 3701, Level 11, State 5, Line 1 Cannot drop the trigger 'prod_culture_trig', because it does not exist or you do not have permission.
What permission do I require? This is a test database on my computer with me as the administrator.
I created a report with a school parameter for all our schools in SQL report. The school parameter gets it's value from a query. The report is generated based on the selection made by the user. If user choose school "A", report is generated for school "A" and if user choose school "B" report is generated for school "B".
I am using a centralized user id and password for all users. A report URL (report link) is sent to user to access the report. Currently, from the school parameter (school drop-down list) users from any school may/can choose report for other schools other than theirs schoos. The content of the report is sensitive and we do not want anyone to see anyone else report.
I want to create a user based security, so based on the user id for school "A", he/she will only be able to see school "A" report but nothing else.
I was trying to accomplish this by creating linked report but it does not look realistic to create a folder for each school on the report server (which means about 180 or more folder for all our schools ). Does anyone else have better suggestions what can/should I do?
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
Hi, I have the following two tables in MS SQL 2000 1.Products: ProdID intProName char(10) 2.Orders: OrderID intProdID intOrderDate DateTimeQuantity int I want to join these two tables to form the following result format: _________Prod1__Prod2__Prod3__Prod421/01/07__1______0_______1_____0___22/01/07__5______1_______2_____3___23/01/07__8______0_______1_____2___24/01/07__3______3_______4_____3___25/01/07__2______0_______1_____4___26/01/07__1______2_______6_____2___ So the first row would have the product name, Left hand column has the order date and then the sum of all the orders within Any pointers on how to achive this would be great.
Hello , I am having a problem related to Alter table syntax in MS SQL Server . What I want to do is in a single alter table statement I want to add as well as modify columns in a table.
For E.g. alter table testtable add fld1 numeric(10), alter column fld2 numeric(15) But I am getting an error.are these kinds of statements possible in MSSQL server ?I was able to do so in Oracle.I just wanted some confirmation or examples of whether this is possible in MS SQL Server also. Fast Help would be extremely appreciated. Thanks, Anand
which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.
Hi, I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE.
I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore.
How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table.
if we have two file group in a particular sql server 2000 database (c and d drive), and in that database suppose one particular table (location c drive) is growing very fast, i want to move it to D: drive file group. so how we can do it.
I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.
Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.
I have a couple questions. I'm very new to SQL and I have this problem: I need to be able to drop the contents of Existingtable_B into Newtable_A - I found this command (below) that will make a 'copy' but I don't want to keep the contents of Existingtable_B. Is it possible to drop them into Newtable_A instead of copy? Also, I want to do this for 5 tables on Sundays at midnight.. how could I schedule that? Finally, what happens if there is not enough space or some other critical error happens during this procedure? I don't want to lose the data.
Hi,When I drop a table in Sqlserver 2000 database, The following error occurs:Server: Msg 1204, Level 19, State 1, Line 1The SQL Server cannot obtain a LOCK resource at this time. Rerun yourstatement when there are fewer active users or ask the system administratorto check the SQL Server lock and memory configuration.What's wrong? Any help is greatly appreciated, thanks.
Hi everybody,I need some help in SQL Server. I am looking for a command that will "Dropall user table" in auser database.Can anyone help me?Thank you very muchSabrina
I am unable to get the table in the following VB code to actually DROP. A straight SQL version with literals (no variables) runs "successfully" as a query but the table also fails to DROP. Can anybody explain what I'm doing wrong?
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
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 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?
