Tag Info

New answers tagged

6

Multiple ways to get this information: SELECT APP_NAME(); SELECT PROGRAM_NAME(); SELECT [program_name] FROM sys.dm_exec_sessions WHERE session_id = @@SPID; Just keep in mind that it can be spoofed in the connection string or in Management Studio's connection properties. If I connect using the following parameter, all three of the above will return ...


0

I have an old post where I discussed an actual product I was evaluating : How to properly secure MySQL database?. The product Gazzang (now Cloudera) would basically encrypt the data and the framework requires using specific keys. Even if you copied the files, the keys not being present in the target system made everything inaccessible just from an OS ...


0

To see/access the files in another folder, give NT SERVICE\MSSQLSERVER permissions on the folder. See my answer posted on .bak file not visible in any directory for screen shots and the steps involved. (Slightly different from adding normal user/group permissions to a folder.) Hope that helps!


2

Looks like somebody is really trying to hack into your SQL Server with brute force attacks. I recommend taking a look at this Whitepaper on SQL Server security, that will help you a lot. Since most attacks happen at the same time, make sure you don't have a security scanner on your network that runs its checks at that time. Products such as Nessus can ...


0

In SQL Server 2014 you could use this two new permissions: CONNECT ANY DATABASE SELECT ALL USER SECURABLES Source For SQL Server 2008R2 you could add your user to model database or write DDL trigger for database creation action to add your user to new databases, when they are created.


0

If the user that you added to use the Linked Server is not for the login you are currently using (and it is the only one you've set up), when you click OK, the connection test will fail because the current user will have no valid mapping to use the Linked Server. To fix this simply create a valid mapping for your login as well. That way when you click OK ...


3

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used. Regarding "Security": If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = SAFE, then there aren't any issues that I have ever been able to find. And ...


3

SQLCLR assemblies can be installed with three levels of security access: SAFE | EXTERNAL_ACCESS | UNSAFE. This is amply documented, refer to CREATE ASSEMBLY and Designing Assemblies: Managing Assembly Security You can control how much an assembly can access resources protected by .NET Code Access Security when it runs managed code. You do this by ...


1

Yes - if you use functions in a language other than SQL, or if you define them as STRICT. Essentially, you must prevent inlining of the function. If the function isn't inlined, then predicates can't be pushed down through it and it can't be flattened. Only SQL functions are eligible for inlining, and only if they are not defined as STRICT.


1

There are three ways to find who is connected to a database in sqlserver. First one: Use the SQL SERVER Management Studio -- Management -- Activity Monitor This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc. Second One: Use the built in ...


0

Always just give the grants necessary. If you want to restrict on the row number, you have to create views like CREATE VIEW view_name AS SELECT whatever FROM a_table ORDER BY whatever LIMIT 10; Then just grant select on this view, not the table the view is reading from. Read more about views here. With DEFINER and SQL SECURITY you can also select on ...


5

Since you do not know the schema or tables names to grant them to, no to specifically doing it. You can add your AD account to the db_datareader role within the model database, and this should be copied into each database created on that instance. In addition, if databases happen to get created via restore or anything other than create database statement ...


2

I will say up front that I do not know why your company would have policies that lock down the OS so much that a Microsoft product cannot be installed and provided the appropriate permissions during an installation. I would think they might be doing that backwards. I supported the military as a contractor for many years and that included secured networks, we ...


-1

I'm trying to find out when a SQL server installation grants permissions on the OS. During installation during Database Engine Configuration please make sure to add yourself as Administrator for SQL Server Administrator so that you have an access to Specific instance after installation. During this step you can include user groups to access SQL Server ...


3

Yes, sa can be renamed and disabled since SQL Server 2005. It can't be removed You can then if you wish, create a dummy sa account with, say, a GUID as password and then disable that or for any software that needs it. Note that this will let you see the new name of the original sa account SELECT SUSER_NAME(0x01)


5

You can use a Logon Trigger for that. CREATE TRIGGER TR_check_ip_address ON ALL SERVER FOR LOGON AS BEGIN DECLARE @ip_addr varchar(48) SELECT @ip_addr = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID IF ORIGINAL_LOGIN() = 'bob' AND @ip_addr <> '127.0.0.1' ROLLBACK; END If you try to ...


3

You can achieve this using logon trigger as following USE master GO -- Create table to hold valid IP values CREATE TABLE ValidIPAddress (IP NVARCHAR(15) CONSTRAINT PK_ValidAddress PRIMARY KEY) -- Declare local machine as valid one INSERT INTO ValidIPAddress SELECT '<local machine>' -- Create Logon Trigger to stop logins from invalid IPs CREATE ...


3

First, sp_change_users_login is deprecated feature and is discontinued in SQL Server 2014. Secondly, EXEC sp_change_users_login @Action = 'Report' is not a proper way to get orphan users list. As with 'Report' parameter, it will execute following query. According to which it will return users where issqluser = 1 (It must be a SQl Server Login), and no ...


0

You can see in this blog post that it is possible to do via powershell using WMI. I also saw this post on stack exchange. I'd definitely try it out on a few test servers if you can, but otherwise I don't see why you can't do it via script. You'll probably want to ensure you add logging into your script so you can validate that the service restarts or add ...


1

Option 1 I would probably take advantage of using a Central Management Server to run the password change script across all the different servers at once. This should provide an easy method for verifying the output from each server while keeping it easy to manage by executing once for all target servers. Option 2 If I was doing this in a tighter change ...


0

The first issue was someone denied the SQL Server Services in the NDF and MDF files and once that denied was removed and granted appropriate access, the first issue was resolved. The second solution was that corrupt NDF file had no data so I removed it and database was back online.


0

Instead of just replacing the Database can they not script the changes (schema/data) and run the changes on the existing db at your site?



Top 50 recent answers are included