I am supporting a system that needs to allow users to have access to TempDB.
I set these users up using the GUI, but whenever the server is restarted, these users permissions are wiped out and the db_owner permission is lost and I have to manually go in and apply the permissons for the database to work again, it happens on most reboots but not all.
Is there anyway to keep these users permissions when the server is rebooted?
Your help is most appriciated.
P.S Could I create a stored procedure that when ever the Server is rebooted the procedure would recreate these permissions?
If I need to do this how would I go about doing this?
I have a user account that needs CREATE TABLE permission in tempdb. I know how to do this using the GUI (Enterprise Manager).
The problem is that this reverts whenever the DB server is restarted. I need to figure out how to keep it from reverting, or it needs to be set in some sort of startup script.
We have several servers running SQL Server 2005. Each of these were upgraded from SQL Server 2000. Each server has about 50 databases on it. Each database has a table with the same name, containing login information to the database that the table resides in.
We have a VB 6.0 application that allows our Help Desk to do a SELECT on the table that contains login information. All it does is a SELECT; no UPDATEs, DELETEs, INSERTs, or DDL operations are performed by this application. The application logs into the databases using a Security Group login (let's call it MyGroup). Each member of the Help Desk team is a member of MyGroup.
About twice a month, the MyGroup security group loses SELECT permissions on all of the databases on a server. The server affected usually is different each time, but sometimes the same server will be affected two times in a row.
So far, we just run a script to update the SELECT permissions on the table in each of the databases, and this takes care of the problem (for now). But the problem seems to be recurring regularly.
What would make the server lose all permissions for a particular security group? The other logins continue to work fine. Only this one security group seems to be affected.
We have SQL 2000 Enterprise edition. There are several outside joins to MS Access tables. I need to find out what would be the best way for me to locate a Windows authenicated users permissions on a table with the SQL database. I cannot seem to find any way to trace this information. Thank you in advance
I have written a simple SQL Server 2005 package to pull some data from Oracle (using ODBC) and pumping it into SQL Server. When I run it from the server in debug mode in VS it works fine. When I schedule the job it errors out with "ora-01005: null password given; logon denied." The password is there. Has anyone experienced this? Is there a security setting somewhere preventing me from saving passwords? Is there a work around? Thanks.
Hi, I keep losing my permissions in tempdb. I have a DTS package that when it runs it first creates some temp tables in tempdb for processing the data before loading in to the database. It originally worked but now keeps failing every so often. I have now made the guest account in tempdb db_owner so anyone can create and delete tables. Is this the best way? Also this keeps being removed so I'm trying to get all the facts before I hunt the person down who keeps removing it.
Please forgive my ignorance, I am by no means a SQL Expert, but have encountered a strange issue.
I have 6 SQL Servers, Primarily SQL 2005 (one older SQL 2000) all loaded on Windows Server 2003 SP1.
We use the servers for a proprietary database that we created which is the backend to a software package we sell.
The issue I have is: We have added a Security account to the servers, and in one case we have granted rights for this account to the TempDB system database. However, whenever we restart this server SQL drops this user account, thus severing connectivity for the app that is relying on that account.
I have set the account as DB-Owner etc, but nothing sems to keep it on re-start.
I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code:
srv = New Server("MYPCSQLEXPRESS")
db = srv.Databases("washmaster")
Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File)
Dim recoverymod As RecoveryModel
recoverymod = db.DatabaseOptions.RecoveryModel
rs.NoRecovery = False
rs.Devices.Add(bdi)
rs.Database = "washmaster"
rs.ReplaceDatabase = True
srv.KillAllProcesses("washmaster")
rs.SqlRestore(srv)
This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception:
....Cannot open backup device..[filename]...Operating system error 5(Access is denied.)
The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied.
How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way?
I am having a problem restricting write access to tables in my database.In my database I have a table called, for the sake of argument, 'TableX'.In my SQL Server Logins, I have set up a login for 'Domain Users' using NTauthentication, and a login called 'FullTableX', using SQL Serverauthentication.I have added two users to my database relating to the above logins.I have added a role to my database called 'ReadTableX' with 'Domain Users'as a member of this role. 'ReadTableX' has SELECT permission only on arestricted set of tables.The only other role that 'Domain Users' is a member of is 'public', and'public' has no permissions on any of my tables.The user 'FullTableX' is a member of 'public', 'db_datareader' and'db_datawriter'With the above settings, I would expect user 'FullTableX' to have fullaccess (Select, Insert, Update and Delete) on all my database tables (so farso good), but any user connecting to the database with NT authentication(via ODBC System DSN) should only have read access to the limited set oftables. However, what is happening is that NT authenticated users also havefull access to all of the tables.What am I missing here??
I believe our problem is related to tempDB on the specific server but I would like to know if anybody has come across a similar issue.
We have an SQL statement similar to the following.
BEGIN TRANSACTION
CREATE #tableA
INSERT #tableA SELECT (expression A)
INSERT #tableA SELECT (expression B) WHERE (condition)
COMMIT TRANSACTION
First, let me briefly expand on the second INSERT as this may help when reading the points below.
INSERT #tableA SELECT ... FROM ... INNER JOIN tableB WHERE NOT EXISTS (SELECT ... FROM #tableA WHERE #tableA.columnA = tableB.columnB)
This script works fine on all of our servers except one, which is why I believe tempDB may be involved. After an analysis of the problem, we have the following results,
- If we remove the Transaction, the script succeeds. - If we leave the Transaction and remove either the first or second INSERT, the script succeeds.
- If we leave the Transaction and both Insert statements and remove the WHERE (condition) from the second Insert, the script succeeds.
- If we reduce the row counts from all source tables concerned by 90%, the script still does not succeed. - The script had succeeded the week before on the server in question.
Finally, if we replace #tableA with tableA, the script succeeds.
Can someone please explain what the tempdb database is responsible for in SQL Server 2000. The database and log file has grown extremely large and I cannot backup this table for some reason. Thanks.
Is there anyway to find the permissions assigned to user. What level of access the user has in each db and the tables & is he able to see jobs. Each object and permissions
The account i setup to access the db in Sql 2005 Proper on the Production serve is Represented by the name in the above example as “aspuser�. I created this user in security, logins. And I gave permissions to this on the Db level “create procedure delete, select, update insert.�
I get a error when i run the page in the browser that says “login failed for aspuser.�
I know virtual directory is configured properly. I can run aspx page in the directory with out a db connection, without and error.
I created a new login and then created a new user [COM] in DB with default schema pointing to [COM]
I created then schema [COM] WITH AUTHORIZATION [COM]
I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.
I'm trying to deploy a project that I deployed yesterday just fine, but today I get the following error:
------ Deploy started: Project: Point Reports, Configuration: Debug ------
Deploying to http://reporting.companyname.com/reportserver
Deploying data source '/Data Sources/Srv24.FieldResponse2_1'.
The permissions granted to user 'DOMAINharley.p.bartman' are insufficient for performing this operation.
Deploy complete -- 1 errors, 0 warnings
This seems like a basic permission issue, except I'm not logged in as the user listed! I've never logged into my computer as the user. I did log in to the reporting services website yesterday as that user, but since have rebooted my machine and logged into bothe my computer and the reporting services website as me. Yesterday this report deployed fine. Today, this error message. I've even tried creating a new project and just creating a simple datasource and deploying just that, but still this message! Where is Visual Studio storing and reusing this user name during my deploy process???
I'm making a copy of some tanles between 2 servers.
Server 1 requires a sql login
Server 2 is using Windows Auth.
I have a user on server 1 named "odbc" able to log in.
however my copy task fails, when I drill the error, it's lists the first user in server 1 alphabetically as the failed login???? but in my dts I am specifying the "odbc" user and password.
I think I have a permissions problem on server 1. So my Question, what minimum permissions does user "odbc" need to copy a table?
On server 1 I can copy from northwind to server 2 just fine..but any other db on server 1 causes the weird failure with the wrong username.
After SQL Server 2005 Database Engine is installed by domain administrator, how to give permissions to a regular domain user so that user can control SQL Server Database service?
Hi allI have a stored procedure that has the lineEXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E'If I run the SP from an access front end as a trusted user or from ascheduled job it runs fine and exectues the dts.If I run the stored procedure using VB6 as a standard connection the dtsjobwont run. I get back Execute permissions denied on xp_cmd.. on databasemasterdb_connect_string = "Provider=SQLOLEDB.1;Persist Security Info=False;UserID=test_connect;PWD=pw1test;Initial Catalog=testdb;Data Source=" &database_name....Set cmd = New ADODB.Commandcmd.ActiveConnection = db_connect_stringcmd.CommandType = adCmdStoredProccmd.CommandText = "testStoredProcedure"cmd.ExecuteDo I need to give test_connect permisions to run the test stored procedure.I hoped that because the VB called a stored procedure and the connection hadpermissions to execute the SP then it would be the SP that called thexp_command....can anyone tell me the accepted way to do thismany thanksAndy
I have a SQLDataSource that gets it's data from a SQL 2000 database table. I have configured it to generate the Update/Delete commands, which look correct. I then have a GridView that is using this SqlDataSource to show the data with "Edit" & "Delete" buttons (the default ones from the GridView). My problem is that while all commands (Edit, Delete) work on my local server, they do NOT work on my live server. In my connection string, I specifiy a username and password like this:
<add name="Project_Management.My.MySettings.WebReportsConnectionString" connectionString="Data Source=Karlweb;Initial Catalog=WebReport;Persist Security Info=True;User ID=VbUser;Password=VbUser" providerName="System.Data.SqlClient" /> I have access to change the permissions on my production server, so I gave this "VbUser" every allow permission I could find and still I could not Edit or Delete records. What I mean by they "do not work" is that when I click the Edit button, the GridView switches to edit mode, but when I click Update, the changes are not written. When I click the Delete button, the page just refreshes and the record is not deleted. As this is working on my local server, I think the problem lies in some permission or configuration of the server. Does anyone have any suggestions of where I could look or what to change? Thank you!
Can someone tell me where user permissions for a table are stored within the database? One of my programmers would like to access this table for the security portion of a program he is writing.
Just trying to figure out how to set a users permission in SQL 2005. Ive got one user but I only want them to be able to view data from one or two tables or and views, but can I do this without having to deny access to every other table?
Hello, Our application works this way: attaches database, modifies data and dettaches database. This works fine when user has administrative permissions, but does not work for users with standard permissions. The reason is that SSE ovewrites MDF file permissions (after MDF is attached) and then it is accessible only for Administrators, Network Service and SQLServer2005MSSQLUserxxxxx. How to avoid this behaviour? We want to keep permissions of MDF file as they are, i.e. inherited from parent folder. This becomes serious problem because some users are migrating to Vista where standard permissions are default (but the problem exists also on XP). Has SSE been designed to work in such environment?
Thank you in advance Roman
P.S. We work with SSE SP2, BUILTIN/Users have sysadmin priviliges, problem is with file permissions of MDF. This behaviour can be easily reproduced with Management Studio - just attach MDF file and look how permissions were changed.
I am having difficulty connecting to a SQL database from C#. There are no user signons set up on my PC and I am using windows authentication with C#. Whenever I try to connect to the data base I get a message "You do not have permission to open this database.........".
I have created a vb app that connects with an sql database on server express 2005.
When i install the app on my machine, i can connect and edit records no problem, but when the app is installed on other machines on the network (using windows authentication) i get this error: SELECT permission denied on object 'tbltest', database 'test', schema 'dbo'
************** Exception Text ************** System.Data.SqlClient.SqlException: SELECT permission denied on object 'tblTest', database 'Test', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at Test.TestDataSetTableAdapters.tblTestTableAdapter.Fill(tblTestDataTable dataTable) at Test.Form1.Form1_Load(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Windows.Forms.Form.OnLoad(EventArgs e) at System.Windows.Forms.Form.OnCreateControl() at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible) at System.Windows.Forms.Control.CreateControl() at System.Windows.Forms.Control.WmShowWindow(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ContainerControl.WndProc(Message& m) at System.Windows.Forms.Form.WmShowWindow(Message& m) at System.Windows.Forms.Form.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
I think it may have something to do with user permissions for the database on sql server, but no amount of editing permissions will allow access. Help !!!!!!
Hi, I'm working on a project that connects to a database on every view of a site. It works fine for a few hours but eventually it just stops connecting to the database. There's nothing in the application logs to suggest where to look, it seems like the connections are just dying. I am creating the connection in a pretty normal manner:SqlCommand myAwesomeCommand = new SqlCommand();myAwesomeCommand.Connection = new SqlConnection(myAwesomeConnectionString);myAwesomeCommand.CommandText = myAwesomeSQL;myAwesomeCommand.CommandType = CommandType.Text; // Otherwise known as awesomeText myAwesomeCommand.ExecuteScalar(); I also use a SqlDataAdapter and fill it for some of my stuff for grabbing more complex data, but all happy System.Data.SqlClient stuff. But yeah, the thing seems to just give up after a few hours. Everything I have read about connection pooling in ADO.Net says that the connections won't be reused. And yes, I believe I am closing all of my connections after I use them. The site is getting, oh, 7500 hits an hour maybe? Somewhere around that, maybe more maybe a little less. Anyone have any ideas?
I have 2 machines, each running the same version of SQL Server. I'm trying to use the dts to export my data from one to the other, and it works fine, except for the fact that I lose my unique index on a field. Is there a way I can keep this from happening, or am I going to have to recreate it each time? I wouldnt think I would have to, seems like bad design to me.
I've seriously looked, but this simple concept eludes me. How do I go about viewing all the permissions granted to a database user? Like whether or not they can execute a stored procedure.
Can someone tell me where the information about the permissions granted to a user are stored? For eg, user xyz in db1 has SELECT permissions on Col1, Col2, Col3 of Tab1 and UPDATE permissions on COL2 of the same table. Which system table(s) will hold this data?
