This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1 AS DECLARE cursor_1 CURSOR FOR SELECT 'This is a executable query' FROM table_1 DECLARE @table_2 DECLARE @stoptime DATETIME = NULL;
I am working with a SQLServer installation where all public permissionshave been revoked from the system.I currently hold the securityadmin and sysadmin roles to perform mylogicall access control work (creating logins and adding users todatabases and changing group memberships.)There has been a question as to whether or not I need the sysadmin roleto do my job, so we tried an experiment in dropping the sysadmin rolefrom my id.With no public permissions, I see no user information on the server,which really limits my ability to do the job.Has anyone ever worked with a super-locked-down server without thesysadmin role for doing security admin work?What I'm looking for is hopefully SQL to perform the access privilegesneeded for the security admin role by itself.Thanks in advance!
Query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
I have a Windows NT group that is used to delegate certain database responsibilities to other members of staff and I am trying to grant permissions for the members of the group to be be able to establish database mirroring sessions, as in run the following:
ALTER DATABASE <database> SET PARTNER = 'tcp://principal_server.domain.com:port';
Although the group has db_owner role membership to the user database which grants the ALTER permission on the database, the following is being generated in the error log when they get to this step on the intended Mirror instance after restoring the database correctly in preperation:
SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. * ******************************************************************************* * * BEGIN STACK DUMP: * 10/29/15 11:16:15 spid 59 * * * Exception Address = 00007FF9A6AF838C Module(sqlmin+000000000003838C) * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION * Access Violation occurred reading address 00000000000000D8 * Input Buffer 210 bytes - * alter database <redacted> set partner = '<redacted>';
As you can see, the statement is denied to the user. There are no issues with the database as I am able to run the same query successfully using my own sysadmin account after the failed attempt. What other minimum permissions the group might need to successfully enable them to setup a mirroring session?
If you were to do a fresh install it would set permissions on the disk so everything just works.
Now when changing the service account (e.g. to a domain user) use the configuration manager, does it do the same magic (possibly sans if the database data/log files are on another disk)? Or do you need to trawl through the dozens of folders and assign rights manually?
Hi I have upgraded my local to sql server 2005, but still need to access the remote instance which is still at version 2008/8.0. Enterprise Manager has been removed (by the SQL server 2005 installation) Does anyone know how I can restart older version of SQL server instance (ie 2000/8.0) using SQL server Management Studio?
I don't have admin access on the server which the sql remote instance is hosted. Can get around it by asking the sys admin guy to do it for now but would prefer to continue to be able to do it myself. SQL Server Configuration Manager can only manage SQL server 2005 versions - a nice little caveat!
Is there a way to stop/start remote services using 'net stop/start'? - without having to write code.... I'll continue to search for a solution, but if someon can point me in the right direction, I'd be most greatful.
I have a very simple query that simply does this: select * from badgetemplates where badgeid=39 , the badge template table has four fields, one of them is of type IMAGE, however the data on badge 39 IMAGE field only amounts to about 600kb, it used to run very quickly (a second or so) now it takes over a minute, i have checked this via the SQL profiler. What is strange is if I remove some of the data stored within the IMAGE field and reduce it to much smaller that 600kb it runs very quickly again. 600kb is fairly small and it used to return this very quickly as I say. Other queries are running at normal speed and the server perfomance does not seem to be reduced elsewhere, it only seems to have effected this only table but only with data over a certain size, the table itself only contains 20 or so rows. What could this be?!
Any help would be really appreciated as I have no clue.....
what are the minimum required permissions for being allowed to deploy a report? When I try to deploy a report in BIDS I get the error message that my user has not sufficient rights for doing so.
Some key data for my configuration:
Windows Server 2003 Standard Edition with Service Pack 1 SQL Server 2005 Standard Edition with Service Pack 1 I'm not a local administrator, but I have administration rights for SQL Server and Analysis Services I'm in the Reporting Services' system administrator and system user groups
I can access http://localhost/Reports, but not http://localhost/ReportServer I have access to the directory (incl. subdirectories) MSSQL.2, but not to MSSQL.1MSSQL and MSSQL3Reporting Services I can't run the Reporting Services Configuration Tool (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1565766&SiteID=1) I can't connect to Reporting Services in SQL Server Management Studio
My question is (as I have already mentioned in the beginning): what are the minimum rights the IT administrator has to assign to my user so that I'll be able to deploy reports? Giving me local administrator rights is not possible.
Thanx in advance and kind regards, Gerald
Update:
In the meantime I have found out, that I'm most probably not a member of the Publisher role. But although I am in the System Administrator role I cannot assign myself to this role. When going to http://localhost/Reports the required links are just not visible. Is this because I'm not a member of the groups SQLServer2005ReportServerUser$... and SQLServer2005ReportingServicesWebServerUser$... ?
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?
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!
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
I am trying to find some resources for SQL 2000 admin. I have a book and will be taking a class, but am still looking for a forum and a bit of Technical expertise to discuss issues with.
Hi guy,this is my problem: user Sam has permission only like db_writer,db_reader and another, test, customizable. He isn't a ddladmin or db owner. Now, in the db, there are object only with dbo as owner, and this is the way that have to remain. But in the application there are a lot of statements (like truncate or alter table) that need higher permissions to be executed, higher Sam's permissions. I read that in sql 2005 it is possible with EXECUTE AS command on a s.procedure, but it seems that in sql 2000 there is any way to do a similiar operation. So, my question is: is there a procedure that grant permission to Sam to manipulate dbo.object (truncate or alter table)? Please answer me in simple english...i'm italian guy.
Will start supporting mission critical application soon and looking a best way to remotely support it, short of dragging a laptop with me everywhere. Was going to eval PocketDBA. ANy other ideas/solutions?
We have SBS2003 and SQL2000 is running on it. Recently we built a trips database and Installed trips application on it but the problem is that only the administrator can run Trips application. Other domain users cannot get access to the new trips program. I added the users on database and double checked.
When normal user logs on, these messages show up:
Message 1: "MS sql Server login Connection failed: SQLState: '01000' SQL Server Error: 53 Microsoft ODBC SQL Server Driver DBNETLIB ConnectionOpen (Connect()). Connection failed: SQLState: '08001' SQL Server Error:17 Microsoft OBDC SQL Server Driver DBNETLIB SQL Server does not existor denied."
Message 2: "File dbo.control could not be opened. Error (r=100). Press OK to end this application."
Hello,We have a database in MS SQL 2000 and I'm trying to link to it from MSAccess XP. I've created a user with full access to one specific tablein the MS SQL database, but Access won't write to it.I can open this table in MS Query Analyzer and log-in as the user, andI can do update, delete, and select queries just fine... but when Ilink to the table from MS Access using the same MS SQLusername/password, I can only read the data... no write or delete.Since I can update data fine in MS Query Analyzer, I assumepermissions are setup properly. Anyone know why I can not edit thelinked data from MS Access?Thanks,Alex.
I am in need of a utility (stored procedure, third party app, etc)that will help streamline the process of managing and auditing objectpermissions, users and roles on multiple sql server 2000 instanceswith multiple databases on each instance. That's a quick overview ofwhat I need, do I need to be more specific?Thanks.
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
Based on our database infrastructure, we need to secure our SQL databases. The security issue concerns on allowing a limited number of Domain Admin users to access the SQL databases. We tried certain ways, based on the documents in the Microsoft web site, but we couldn€™t reach to the point of preventing the Domain Admin users accessing the SQL databases.
We are currently running sql 2000 and are moving our database onto sql 2005 running on a different box.
We have managed to move the entire database, with users however the users permissions on specific tables/views/stored procedures have not been transferred, does anyone know a way of transferring user permissions rather then doing them all by hand?
The system is a large (over 500 table/views/stored procedures) and a very active one and therefore downtime is not optional.
I use EM to handle 2 SQL servers. One I can `stop`; the other I can`t. (except I think I used to be able to do so).
When I select the `stop` I get the following message from EM:
"An error 1051 - (A stop control has bee sent to a service which other running services are dependent on) occurred while performing the service operation on the MSSQLServer service."
How do I track down what this other running service is? How do I stop SQL?
We have a web applicaiton backend database uses sql server. Now we stop using this web application. I would like to stop this sql server database. What do you usually do? I can backup this database and delete this database. Or if i want to keep this database. What should i do? Thanks for you help.
