Setting Permissions On Master.dbo.syslogins
Jul 23, 2005
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?
View 1 Replies
ADVERTISEMENT
Jun 21, 2001
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.
I used to be able to do it in 6.5.
View 3 Replies
View Related
Apr 18, 2008
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?
View 3 Replies
View Related
Nov 8, 2007
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
View 9 Replies
View Related
Jan 7, 2008
Hi All:
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.
Any help is greatly appreciated.
caeriel
View 1 Replies
View Related
Dec 27, 2005
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?
Miranda
View 2 Replies
View Related
Jan 7, 2001
Howdy
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.
Thanks
W.
View 1 Replies
View Related
Feb 6, 2006
hi, please anybody help me with this....
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...
View 1 Replies
View Related
Jun 14, 2006
All,
I'm wondering if there are any methods to set the permissions for a specific user on a specific report within SQL Server Reporting Services.
I got how to get permission from them programmatically, but not how to set them but the user interface..
Please apologize me if there was a similar thread in the recent past; I tried to find it out, I didn't succeed.
Thanks a lot in advance for your timely response (as usual deadlines are by "yesterday").
-G
View 12 Replies
View Related
Apr 2, 2007
Hello,
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.
Thank you for your help.
View 3 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Mar 14, 2007
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?
View 4 Replies
View Related
Dec 19, 2001
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
View 1 Replies
View Related
Aug 12, 1998
If I want to easily and quickly grant all permissions to a group of objects
for one user (or group), how can I do this with the tools provided?
In 6.5, I could right-click the user or group and click the "Grant All" button.
In 7.0 this is either missing or has been moved.
Thanks!
Jim
View 1 Replies
View Related
Feb 13, 2008
Hi
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.
Thanks
Simon
View 2 Replies
View Related
May 15, 2006
How would Set permissions Sql Server 2005 so that I can access a asp page created in VWD and Sql Express.
On the production server i have just the one instance of Sql
I have the following connection string in the web config file:
<connectionStrings>
<add name="ConnectionString" connectionString="server=serversNameXPRESS;uid=aspuser;pwd=aspuserPassworkd;database=DATABName" />
</connectionStrings>
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.
Any help would be greatly appreciated.
View 2 Replies
View Related
May 21, 2013
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.
See before and after snapshots below.
View 8 Replies
View Related
Jul 25, 2000
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.
How do you get the syslogins in for SQL 7?
Thanks,
Carl
View 5 Replies
View Related
Apr 17, 2001
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.
View 3 Replies
View Related
Apr 6, 2001
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.
View 5 Replies
View Related
Jul 18, 2001
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?
thanks
View 3 Replies
View Related
Mar 23, 2000
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
View 1 Replies
View Related
Oct 22, 1999
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?
View 1 Replies
View Related
Aug 24, 2000
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?
View 1 Replies
View Related
Aug 17, 2004
Pls help me out with this query as
select * from master..syslogins WHERE hasaccess = 1
as it is both giving me record where hasaccess = 0 and 1
brgds
View 5 Replies
View Related
Dec 15, 2004
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.
Thanks, Jim
View 5 Replies
View Related
Oct 26, 2006
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?
View 11 Replies
View Related
Jul 12, 2006
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
View 12 Replies
View Related
Jun 19, 2007
I got a File with sales orders and their details.
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.
Any Idea?
View 4 Replies
View Related
Aug 2, 2006
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.
View 5 Replies
View Related
Mar 25, 2008
Hello,
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.
Hope this makes sense.
How can I do this?
GrkEngineer
View 9 Replies
View Related
Nov 2, 2015
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?
View 4 Replies
View Related
Oct 22, 2007
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?
View 1 Replies
View Related
