I've got a tool that accesses syslogins to pick up some information.
When I run the tool, I get the error message that sasys that my login
does not have sufficient permissions to read syslogins. If I run under
my admin ID, everything is fine. HOWEVER, the intention is that this
tool will be used by non-DBA staff members so we have a generic id
created to run this tool specifically (non-DBA account).
OK, so I go to Enterprise Manager and open up the master database, go
to syslogins and add my non-DBA id to the permissions list as having
SELECT access, and click on EXECUTE.
BOOM! No sign of my permissions being set.
Am I missing something? If so, what do I need to do to set permissions
against this view?
How can you manually update the syslogins table in SQL 2000? It keeps giving me the following error:
Server: Msg 4406, Level 16, State 2, Line 1 Update or insert of view or function 'master..syslogins' failed because it contains a derived or constant field.
What considerations do I need to take into account if trying to enlist a server into a master server when (a) both instances are on different physical servers and the SQL Server and SQL Server Agent services run under different domain accounts?
The reason I ask is when attempting to create a master server through the SSMS wizard, I receive these errors:
Enlist TSX Progress
- Create MSXOperator (Success) * Checking for an existing MSXOperator. * Updating existing MSXOperator. * Successfully updated MSXOperator.
- Make sure the Agent service for 'MYMSXINSTANCE' is running (Error) Messages * An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
- Ensure the agent startup account for 'MYTSXINSTANCE' has rights to login as a target server (Error) * Checking to see if the starup account for 'MYTSXINSTANCE' already exists. Messages * An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
- Enlist 'MYTSXINSTANCE' into 'MYMSXINSTANCE' (Error) * Enlisting target server 'MYTSXINSTANCE' with master server 'MYMSXINSTANCE'. * Using new enlistment method. Messages * MSX enlist failed for JobServer 'MYTSXINSTANCE'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'MYMSXINSTANCE'.) (Microsoft SQL Server, Error: 22026) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
Now, the first two errors are what are puzzling me. I'm certain I can address the third and last error by making the accounts the SQL Server Agent services run as the necessary SQL permissions on each instance.
I've tried making NT AuthoritySYSTEM accounts on both servers, no luck. Any suggestions?
I'm trying to identify the objects in master that the role public has select permissions on, but when I run this query, I get 4 results where the default schema is null and the major_id column does not correspond to any records in the sys.all_objects table. Where else can I look to find what objects these are. DBO is listed as the grantor.
I appreciate your help.
SELECT * FROM SYS.DATABASE_PERMISSIONS P, SYS.DATABASE_PRINCIPALS R WHERE P.GRANTEE_PRINCIPAL_ID=R.PRINCIPAL_ID and permission_name='SELECT' and class_desc='OBJECT_OR_COLUMN' and r.name='public' order by r.name desc
I hope I'm in the correct forum for this question. If I'm not, forgive me and point me in the proper direction.
I have SQL Server 2000 databases that I am trying to secure. To that end I've deleted the guest account from all but the master and tempdb databases.
Within the master db I've denied access of any "flavor" to all objects but spt_values, syscharsets, sp_MSSQLDMO80_version, and sp_MSdbuserpriv (only because I've discovered they are necessary).
Can anyone tell me where I might find the absolute minimum permissions configuration for the guest account in master? I have no third party vendor software accessing my SQL Server 2000 databases. The thought of
Demographics: SQL Server 2000 sp4 running on Windows 2003 Server with the current service packs.
we are using windows authentication. Is there a faster way to set permissions for each database than to go through each table and set them? Currently, to set the permissions from SQL Server Enterprise Manager, I click on Security > logins > select the properties for the IUSR account > Database Access > then check off each database that we allow access to. Next I go through each tables properties and set the permissions. Is there an easier, faster way to do this?
I know you can set permissions on a column via T-SQL, can it be done via Enterprise Manager? If so, how do you do it? I've looked everywhere but dont seem to be able to find how to do in EM.
i need to have a user account with the following requirements:
a. can create/alter/drop tables/views that is created by this user; b. can read/update/delete records from tables created by this user c. can read/add records to tables created by other user d. can create/edit/execute stored procedures
is this possible? how can i do this? how can i allow a user to create new objects or alter objects owned by him at the same time prevent him from dropping objects created by other user? how can i give a user full access to objects that he created and limit his access rights like dropping objects, deleting records or updating records from tables that is not created by him?
can i do these thru enterprise manager or do i have to run some scripts in order for these to happen? if so, can somebody help me with the scripts or links that i can use for references?
thanks a lot! hope you can help me with these... thanks a lot...
I hope this is the right forum to post this question. I am working on creating a CLR stored procedure, the first one I have ever done, and I am using Visual Studio 2005 (VB) to try to debug it. I keep getting this error.
User 'dbo' could not execute stored procedure 'master.dbo.sp_enable_debug' on SQL Server psalm1sqlexpress
I found this link which helped a little. http://msdn2.microsoft.com/en-us/library/ms241735(vs.80).aspx. The problem is that dbo is the user that is shows that is throwing the error. However, I am logging in as sa, which is the sysadmin.
I tried adjusting the permissions for dbo, and I changed the owner of the db to a sysadmin, but I still get that error.
I would be very grateful if someone could point me in the right direction to get this working. By the way, it does install the clr stored procedure, it just won't debug it.
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 am using SQL 2000 with the Server Enterprise and the Query Analyzer programs. Almost everytime I create a new Stored Procedure, I forget to go into Server Enterprise and grant Execute permissions to my users. Is there any way in a Stored Procedure to set the permissions when the Procedure is created?
Hi, is there a way to set permissions on a database so that a certain user can view the design of all the tables, sps and triggers etc but not be able to view the data. If so, how do you do that? thanks Zoey
I have a vm machine running SQL Express. So as to centralise my data, on the host machine I have a folder with all my data. This folder is shared. In the vm machine I have set up mapped to this folder, so it is visible within in. I can add/remove from the folder in explorer so the permissions look okay up to there.
However, when I go into SQL Express and attempt to restore from a backup into a DB, I cannot see the folder in the folder list presented. When I try and force the connection through the mapped drive letter or unc name, the server fails with a you do not have permissions.
From my reading it looks like I have got a network service account set up but, when I look at the logins in SQL Express there doesn't appear to be such an account.
Could someone either a) point me in the right direction if the netowrk service account is not the way to go b) tell me how to set up a netowrk service account with the correct privileges to enable me to read /write this mapped folder.
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.
My company is new to MDS. I am trying to set an attritube in an entity to read only so the users can't change the value in that field. When I did that, the whole model disappeared. I thought I had deleted it by accident so I created a test model and tried to do the same. The test model disappeared. This time, before saving the new settings I took a snapshot. After saving I took another snapshot. You can see that the whole model is gone (zz_RN_Permissions_Test). I tried every other coworker with admin rights and nobody shows it on the Models list. The behavior on the Excel add-in is correct. I can't change any values on that column. But I need to keep the models available.
In a recent Disaster Recovery TEST, I successfully recovered some SQL Server 6.5 databases by re-installing SQL Server, applying the SP5a, restoring the application database, and then BCPing IN the syslogins. Everything went normal, as I have done this many times.
I had one SQL 7 to recover. I used the same procedures, however, the BCP in did not work. I ended up getting a Dr. Watson message (not sure if it was related to the BCP or not). Luckily I had the Logins scripted and was able to apply them as follows: USE master go EXEC sp_addlogin 'user1', 0x21312144332c534025532f3a442d5e4c, @defdb = 'DBNAME', @deflanguage = 'us_english', @sid = 0xf8ddfa809a05d411a56600508ba705cb, @encryptopt = 'skip_encryption' go
This seemed to work, and luckily there weren't many users in this database.
whenenver i run cold fusion, it automatically gets my tables out of the master database instead of the new one i have created. i've added a user that has a default to the new database instead of the master, but i need to know how to use that new user correctly, and what i will need to do once i start adding more databases so that cold fusion will go to the appropriate database and not to the master.
We are looking to migrate an entire server with several databases which collectively contain every user in the company. Obviously, I would prefer to directly migrate the logins without having to make everyone reset their own passwords. Anyone know of a way to do this?
I tried dts'ing the syslogins table but it fails due to password collumn being read only.
hi, I created a new 6.5 sql server. I brought all the logins from the old server. I bcp out the syslogins from the old server and bcp into the new server. There were no errors from the bcp in. Somehow, I still can't login to the new server using the same password from the old server. Why? how can I fix it?
my problem is : I want to export syslogins from database SQL6.5 to syslogins SQL7.0. I use BCP (command : mssqlBCP master..syslogins out d:empsyslogins.dat -c -Sservername -Usa -P -ed:emplogins.err) It runs well. AFTER to export to SQL7 database : (command : mssql7BCP master..syslogins in d:empsyslogins.dat -c -SNEWservername -Usa -P -ed:emplogins.err) I always have DR WATSON. If someone has encountered this problem and solved it. Your help will be strongly appreciate. Patrick
Is it possible to have a two way replication of the Logins and Users between two servers? What we have is two SQL 7.0 servers, for an CRM package. They use the CRM replication program to move data back and forth. However, we need to keep the logins and passwords between the two machines ther same. Any thoughts on how to do this?
Just installed SQL Server 2000 Beta 2. I have a procedure that selects the suid from the syslogins table from a SQL 7.0 version that is now saying that column is not valid. All the documentation I can find still refers to that being a valid column. Any suggestions?
Does anyone have a script already which pulls all the logins and what databases they have public/dbo access to? I'm looking for something more than just pulling syslogins/users and their default dbname, I need a list of all the databases they have dbo access to.
I need some help I have a 2003 MS Small business server with MS SQL 2005. I have a access front end database and SQL server back end. I need to connect a web site form in iis on the same server to the sql tables . I am generating errors in the event log when I submit the form data from the web, €œLogin failed for user SERVERNAMEIUSR_SERVERNAME! (CLIENT:192.168.1.37) ! Can anyone direct me to a paper that will step me through setting up this access?
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table. Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.* FROM MASTER WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
