I'm trying to lock down my server. I've removed Guest from all DBs except Master & TempDB. Since I can't whack Guest from Master, and since I can't whack Guest from the Public Role, on Master I ran the following:
USE Master
DENY ALL TO Guest
GRANT SELECT ON SysLogins TO Guest
But after doing so, any given user from another db can still see, say, SysComments or SysUsers, etc. I would like to avoid manipulating the 892 rows returned by EXEC sp_helprotect NULL , 'public'. ;-)
I'm perplexed, because not only did I think I'd already mastered this topic, but because of the following regarding DENY from BOL: "Two special security accounts can be used with DENY. Permissions denied from the public role are applied to all users in the database. Permissions denied from the guest user are used by all users who do not have a user account in the database."
Surely I'm overlooking some newbie thing...Can someone help me out?
I needed to take a backup of a database and use it on another server and followed the steps in the following article to do so:
[URL]
It restored fine on the destination but I still need to use the original and I get the following error from the application when trying to use it:
create a master key in the database or open the master key in the session before performing this operation.
This is after doing the following to try and restore the master key.
USE <database>; GO open master key decryption by password = '<password>' RESTORE MASTER KEY FROM FILE = 'C:exportedmasterkey' DECRYPTION BY PASSWORD = '<password>' ENCRYPTION BY PASSWORD = '<password>'; GO
After doing this I got the message:
The old and new master keys are identical. No data re-encryption is required.
Newbie to backups here. I'm trying to setup a Maintenence Plan for backing up the system tables. For some reason the Differential backup on the Master table is failing.
Can you NOT run differential backups on the Master table?
I have setup SQL Server 2012 - MDS and can run the web client from IE11. I've created the model and entities from System Administration ok, however when I try and use Explorer to work with the master data I've setup, there's an error "The remote server returned an error: NotFound".I ran Profiler while clicking Explorer, and it's connecting with the XML statements and running without any errors - so it's seems it's not any issue with connecting to the MDS database.
I have a login that is mapped to a Windows sysadmin account. I used it to login to Sql Server 2005. I then created a database called Freedom. I then added a Windows login and user called FreedomAdmin, with Freedom set as the default database. When I login in to Windows using FreedomAdmin and then try to login in to Sql Server 2005, I get the following error:
The server principal "FREEDOM1FreedomAdmin" is unable to access the database "master" under the current security context.
If the default database for FreedomAdmin is Freedom (and it is - I checked from my sysadmin login account), why can't I login. Must I give FreedomAdmin permissions to master?
I executed the below query and getting the capacity values only for master database.All other DB shows NULL values for spaceused. I'm actually looking for a query to get all the capacity information other than using temp table and the procedures. Is there any way using SQL query ONLY.
select db.[dbid] as 'DB ID', db.[name] as 'Database Name', af.[name] as 'Logical Name', convert(decimal(12,2),round(size/128.000,2)) as FileSizeMB, convert(decimal(12,2),round(fileproperty(db.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB from sys.sysdatabases db inner join sys.sysaltfiles af on db.dbid = af.dbid
Below is the output
DB ID Database Name Logical Name FileSizeMB SpaceUsedMB 1 master master 4.00 3.44 1 master mastlog 2.00
Hi - I have developed a database using Windows Authentication in Management Studio (CTP) but now need to set up logins for users externally - crucially NOT using windows authentication. I want to resrict these new logins to only run specific stored procs and completely deny all other privileges.
I've created a login 'bc_user' but when I try and connect using management studio (ctp) I get:-
Cannot connect to [server_name]SQLEXPRESS.
Login failed for user 'bc_user'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452).
(Connecting in code from VS produces a less specific message).
My user creation T-SQL looks like this:- USE master go CREATE LOGIN bc_user WITH PASSWORD = 'test_pwd', DEFAULT_DATABASE = testdb, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF go USE testdb go CREATE USER bc_user FOR LOGIN bc_user go CREATE ROLE restricted_users go EXEC sp_addrolemember 'restricted_users', 'bc_user' go USE testdb go GRANT EXECUTE ON dbo.example_stored_proc TO restricted_users DENY ALTER ON dbo.example_stored_proc TO restricted_users DENY CONTROL ON dbo.example_stored_proc TO restricted_users DENY TAKE OWNERSHIP ON dbo.example_stored_proc TO restricted_users DENY VIEW DEFINITION ON dbo.example_stored_proc TO restricted_users
I have searched for this error code and it is documented as a 'bug' in SQL 2000, fixed in SP2. Any ideas what might be causing this or what I have missed? I there a setting in SQL Server (express) that causes this? I have both TCP/IP and Named Pipes connections available and Windows authentication works fine from both VS and Management Studio (CTP).
When logged onto a server and connecting using windows authentication (either by server name or localhost) I am getting in fine, but when I connect using the IP I get the following error.
I can log in using SQL Server authentication with the IP.
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1 CREATE DATABASE permission denied in database 'master'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
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.
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.
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says: Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Is there any possibility to schedule SQL job execution as Windows Security Group? I need to run powershell script through SQL job with one of this group member's permissions.
I have Sql Server Express installed on Vista (service pack 2)
I have Visual Studio 2005 with an application that I'm trying to access it with within a WCF service.
The login ID of the service is added to the database.
The database has remote access turned on.
The ID is granted access to all databases within the server.
The thread is being set with WindowsProvider and the services set their thread to WindowsProvider.
The dataserver is set with using Windows Authentication for security.
When I open my connection to the database, though, it reports the typically useless message that the connection is not allowed and that the server may not allow remote connections.
How to I get past this? I've done everything right.
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
I am looking for a way to log all security related events for SQL in Windows Security Log. I am trying to use SCOM for monitoring SQL and I am looking at ways to generate alerts in my SCOM Console for specific events in SQL e.g. A table is deleted, user is modified, deleted, etc. Is this possible and if yes how do I achieve the same?
In an environment where there are many initaitors speaking to a central target with frowarders in between, from what i can understand this best policy is to disable encryption on the endpoints, since dialog encryption will be enforced this is all that is really required, is this correct.
If the endpoints used encryption the message would need to be encrypted and decrypted at each forwarder resulting in slower perfromance, where as dialog encryption would only encrypt at the sender and decrypt at the target, so is this the best way to go?
Secondly is it best practice to open a dialog initally and send messages over this dialog for years never ending the conversation? This way the services only have to authenticate eachother once, if there are no reboots etc that is of course.
I would think performance wise sending each message and ending the conversation each time is a much greater overhead ? So would it be best practice to keep dialogs open and keep sending messages ?
Initally when i was learning service broker i thought that one must send a message and end the dialog until the next message, but i think the other way is the best option ?
I'm designing a distributed application where I will have SQL Server 2005 distributed databases replicating data to my central hub which is again a SQL Server 2005 database using SQL Service Broker. Data will be sent from the central hub to the distributed sites and vice versa. I need to authenticate the communication and also secure the communication by encrypting the messages. Which security shall I use? Where do I configure the type of security being used? What is the difference between transport security Vs dialogue security - Full security model?
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?
Hi everyone, I have a question about SQL Server 2005. I have written an ASP.Net 2.0 Web Application and it is using SQL Server 2005 as Database. In the last few days I noticed that the app is down sometimes. To analyze the problem I looked at the activity monitor in SQL Management Studio. I can see there approximately 170 processinfos. I want to describe the column values of the process infos: Process-ID: Unique ID and a red down-showing-arrow-icon User: My UserDatabase: My DatabaseStatus: sleepingCommand: AWAITING COMMANDApplication: .Net SqlClient Data Provider When I click Locks by Object, I can see the IDs of the Processinfos. Again I will show some colums:Type: DATABASERequirementtype: LOCKRequirementstate: GRANTOwnertype: SHARED_TRANSACTION_WORKSPACEDatabase: My Database So my question is, does this mean, that i have locked the db? How are they handled? For example I have a windows service, which is doing checks in db every 10 seconds. I can see, that each check generates a new processinfo? Is this usual, or am I doing something wrong? Thnaks for help,Byeee
When I run a select statement : select 'X' from table1 where c1 = condition locking on indexes behaves as expected
However if I run select 1 from table1 where c1 = condition locking on indexes goes wild locking pages and rows on indexes that are not even referenced in the query. Any ideas Why?
Hello All, I'm just migrating from oracle to SQL.Can anybody tell me that how effectively I can use Row level locking in SQL? If tow users are attemping to Moify same record how i can deal it in Back end(SQL)? Thanks in Advance. Suchee
i have an application in production(sql 6.5 ) which causes locking which times out my other processes , iwant to capture time the locking takes place i have found in bol that i can get time deadlock occurs using trace flag 3605 in sql7.0 ,if i have to use trace flag is it ok with dbcc traceon or -T option in startup is recommended. any advice would be appreciated tia ram
I have used DTS in the past to copy information in certain tables in production over the top of those same tables in test. However, the process is now failing. Does DTS require an exclusive lock on the source table, as well as the destination table during the export process? Will shared locks on the table I need to copy prevent DTS from completing the process?
We are running out of locks while updating a particular table (table name = history, rows = 25,000,000) in SQL Server 6.5.
LE threshold maximum is set to 200. LE threshold minimum is set to 20. LE threshold percentage is set to 0.
Locks is set to 0.
I have also included the stored procedure, which we use to update the history table.
As you can see, from the first four lines, we ran this SP 4 times processing around 6 million rows at a time. It runs out of locks once it is around 5.5 to 6.5 million rows. Is there a way of locking the table so that this SP can be run just once which will effectively process all the 26 million rows in one go?
Any help will be greatly appreciated.
Winston
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 5635993 and 12000000) --declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 12000001 and 19000000) declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno > 19000000)
open minihist fetch next from minihist into @huin,@huan,@hmailingdate while (@@fetch_status <> -1) begin
if (@@fetch_status <> -2) begin
select @mailtot = 1 select @mail12m = 0
/*** Get the gender ***/ select @sex = gender from name where uin = @huin
/*** Calculate if mailed in the last twelwe months ***/ if (@hmailingdate <> null) and (@hmailingdate > '19980524') select @mail12m = @mail12m +1
/*** Get info for this uan from address_summary ***/ select @mailtot = (@mailtot+mailed_total), @mail12m = (@mail12m+mailed_12months), @lastday = last_date from address_summary where uan = @huan
/*** Insert a row into address_summary if doesn't exist ***/ IF @@rowcount = 0
Hi, We are running SQL 6.5 in Produciton and I'm getting one blocking problem but mostly I kill the process and whenever I check the SQL Error Log I see this message : Error : 17824, Severity: 10, State: 0 Unable to write to ListenOn connection '1433', loginname 'XXXY', hostname 'DT SA'. OS Error : 64, The specified network name is no longer available.
