Allowing users to access only the data they are authorized to access.

learn more… | top users | synonyms

5
votes
1answer
164 views

Auditing database administrator actions with SQL Server 2014 Standard

We have a requirement to audit database administrator access to customer databases - at a minimum, knowing which customer database they were accessing (and ideally why). Without upgrading to SQL ...
5
votes
2answers
4k views

RSExecRole missing

I have multiple servers and 4 of them have reporting services installed with the ReportServer & ReportServerTempDB databases. A few days ago I was setting up security for a new login and I found ...
5
votes
1answer
857 views

SQL Server Security Hierarchy

I have been working as a developer with SQL Server for years without asking the question, "what is dbo", a shame. Recently I have been studying the security model of SQL Server 2008. I have been ...
5
votes
2answers
5k views

SELECT permission on view querying table from another database

SQL Server 2008 R2 SP1 My company uses the Great Plains (GP) financial system with several customizations. Our Value-Added-Reseller (VAR) for GP has setup most of these customizations in a separate ...
5
votes
1answer
179 views

Security implications of guest account and database chaining on sql server?

I have another database Ydb accessing data in database Kdb. They both have the same owner. In order to allow cross database chaining, it seems I need the guest user to be enabled in Kdb. I did so ...
5
votes
2answers
2k views

Role db_ddladmin not working properly on SQL Server 2008

I want to allow certain user to create/modify objects on a database(tables, views, sp's, functions, schemas etc) and don't want to give db_owner. I thought I could do it with the db_ddladmin role, ...
5
votes
1answer
56 views

Recover SA Password - PSTools - Not working

I'm on a new company, and the first thing I saw is that they don't even know how many instances they have. they don't have passwords and the old DBA had no Idea what he was doing. long story short, I ...
5
votes
2answers
362 views

SQLAgentReaderRole and SQL AD ID login case affecting ability to edit jobs

I have AD ID users on a SQL 2008 server that can't edit their own jobs. They can create them, but in order to edit them, they have to delete them and then re-add them. The issue is explained here: ...
5
votes
1answer
332 views

SQL logs showing login that I can't find

Within my SQL server logs, I am showing a successful login to a database from an account (SERVER\loginname) that I cannot find in either Security > Logins OR Database > Security > Users. This ...
5
votes
1answer
405 views

SA permissions issues with many nested objects

I have a broker application that's relatively complicated. Today, after I made some changes, I started getting the error: The server principal 'sa' is not able to access the database 'XYZ' under ...
5
votes
1answer
658 views

TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices

I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the ...
4
votes
3answers
5k views

SQL Server: does ddladmin rights contain datareader and datawriter rights to the database?

Does db_ddladmin fixed database role include db_datareader and db_datawriter rights, so that you don't have to grant those rights explicitly?
4
votes
2answers
1k views

What are the risks (if any) of sharing a service account on more than one server?

I use domain accounts for SQL Server service accounts. Sometimes, when I have multiple servers that are logically or thematically related, I'll use the same set of domain accounts for the service ...
4
votes
1answer
5k views

Rights to execute stored procedure, but not read underlying tables directly

I have a stored procedure that contains lots of different columns from many different tables; I want to give 20 users access to the results of the stored procedure via Excel. Is it possible to make ...
4
votes
2answers
2k views

Security or performance risks using SQL CLR [closed]

Are there any particular security or performance risks in using the CLR in SQL Server ?
4
votes
1answer
1k views

Make sure MySQL is not accepting remote connections

I am currently doing development for a LAMP-based website. I do not know what configuration changes have been made to MySQL since it was installed on our server. The way that we use MySQL, there is no ...
4
votes
1answer
804 views

Understanding SQL Server encryption (by password)?

After asking this I still have a question. A company need to store sensitive data. Please let's not talk about certificate right now. A DBA does the following : CREATE SYMMETRIC KEY ...
4
votes
2answers
1k views

SQL: transfer database schema

If i want to transfer schema ownership to another schema i know that i need to use something like: ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname; But how is it different from: ...
4
votes
2answers
114 views

Is there a way to grant SELECT for every database (including new ones) at a server level?

I have a unique (well, I think it is unique) situation where databases (yes, entire databases) can be created more or less dynamically. Ignoring the obvious issue with that, I need to be able to ...
4
votes
1answer
3k views

Using AD groups vs roles in SQL Server

What is best practice for sql server security? Using AD groups that are given rights on schema objects or should I add the AD groups to a database role which is given rights on schema objects ? If the ...
4
votes
1answer
9k views

What are SQL Server Securables?

I migrated two applications to the new server. One of the them works without any issues but the other ran into back end sql permission problems like The EXECUTE permission was denied on the ...
4
votes
1answer
219 views

Security Question involving Sys tables

We have an application user which has been given read write to the appropriate databases. This user has the ability to query the sys tables by default. I've also heard that there are times when this ...
4
votes
2answers
493 views

Easily give role in one database access to tables in another database on the same server

I have a database server (Sql Server 2008 R2 Enterprise) supporting two separate but related vendor products, each with it's own database (call them X and Y) In database X I a have a lot of users ...
4
votes
1answer
5k views

In creating view use SQL Security definer or invoker?

CREATE VIEW syntax is CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(...
4
votes
2answers
4k views

Cross-database call fails in a job but succeeds in SSMS

I create two databases, a table in the second database and a stored procedure in the first database. The stored procedure cross-database accesses the table. I create a sql server login and I also map ...
4
votes
1answer
295 views

Just how secure is the data I put on SQL Azure?

My wife has been asking me to create a web application for her business and from what I've read using SQL Azure would be perfect. Except for a couple of small (really really big) gotchas. She ...
4
votes
1answer
4k views

SQL Server 2008 - is “NT AUTHORITY\ANONYMOUS LOGON” login “mandatory”?

My spider senses are tingling when I found this "NT AUTHORITY\ANONYMOUS LOGON" account on a SQL Server logins. I tried to research the matter and I even have a book about SQL Server management. Also ...
4
votes
2answers
620 views

How to detach a sql server 2008 database that is not in database list?

I installed sql server 2008 on windows 7 then i create a Database! after 2 days, i reinstall the windows and isntall the sql server! now i am trying to attach my database file, bot i encounter to ...
4
votes
2answers
9k views

sp_send_dbmail with attachment

SQL Server 2008, connecting via SQL Server Authentication. I have a sproc in DatabaseA which calls sp_send_dbmail in msdb to send an email with a file attachment. The file is on the db server, not on ...
4
votes
1answer
753 views

Is it safe to let application administrators look at v$session, v$process, and v$sql?

In an Oracle database shared among multiple application, is it 'safe' (from a security and system integrity perspective) to allow application administrators access to v$session, v$process, and v$sql? ...
4
votes
1answer
52 views

Is it safe to use set-returning sql functions instead of security-barrier views?

Since 9.2, it's been possible to use the security_barrier attribute to guard against maliciously-chosen functions and operators accessing data 'hidden' behind filters in views (full info in the ...
4
votes
2answers
7k views

How do I fix a login timout in SQL Server 2005 when using a linked server and a stored procedure called from a SQL Agent Job?

I have a sql agent job running on ServerA. This job executes a stored procedure ServerA.DatabaseA.dbo.SPROC_A, inside this SPROC there is a query that selects data from ServerB.DatabaseB.SchemaB....
4
votes
1answer
491 views

SQL Server 2008 encrypted connection using multiple server names

I have a SQL Server that must be accessed using various DNS names (SQL1, sql1.contoso.local, sql1.contoso.com, etc) and I'm struggling with creating a correct SSL certificate that can be used to ...
4
votes
2answers
1k views

Integrated Security Success on Master, Fails on Other Database

I'm a bit perplexed as to why my connection string is selectively succeeding on the master database in SQL Server 2005. Here are the details of my app. Any ideas where to look? I'm clearly missing ...
4
votes
1answer
183 views

Do multiple SQL Server instances increase security?

Context Let's assume you have a server which exposes a web server and one or more web services to store and manage sensible information about physical persons (assume, for this example, complete ...
4
votes
2answers
3k views

How to obtain SIDs of Sql Server installed NT Service Accounts

I am using WMI to obtain members of server local groups, as well as the SIDs of the members. The WQL queries work for members that are local users, local groups, AD domain users and AD domain groups. ...
4
votes
1answer
302 views

DB Master Key Encrypted By *Correct* Service Master Key?

My client has frequently run process where we move databases from a prod environment to lower environments (refresh QA with prod database, etc). These DBs have database master keys on them. We have ...
4
votes
2answers
53 views

Snooping-safe Vote tables

I have a table with both user_id and vote. They are stored together so each user can only give one vote. However it's also problematic because admins can simply read who voted what. Is there a way to ...
4
votes
2answers
626 views

Security for Oracle linked server from SQL Server

I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data ...
3
votes
2answers
381 views

Can I configure MySQL's typecasting to consider 0 != 'foo'?

This article points out the following security problem with MySQL: USERS id | email | password_reset_token 1 | one@example.com | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx 2 | two@example.com | ...
3
votes
2answers
360 views

Prevent SysAdmin from doing a Database Restore

I am a sysadmin on my SQL Server 2008, and I need to be, but I would like to setup my security on the Production server to prevent me from accidentally restoring to the production database. I ...
3
votes
3answers
475 views

Changing the port of the postgresql server for security reasons

For security reasons, it is common to change the port of Microsoft Sql Server. The subject is discussed here in Is changing the SQL Server port really that much safer? my question is: does this apply ...
3
votes
1answer
12k views

How to transfer the ownership of the dbo schema in SQL Server 2012

I've accidentally given a user the ownership of the db_owner schema (using the check box in the UI as below) and now I cannot: Transfer ownership to another user Drop the user from DB (though I can ...
3
votes
2answers
11k views

Granting privileges to dbms_crypto

I'm trying to grant proper privileges for using DBMS_CRYPTO. Whenever I call it from package it gives me error: PLS-00201: identifier 'DBMS_CRYPTO' must be declared User already has privileges: ...
3
votes
2answers
127 views

Grant Admin to an Active Directory account in SQL Server

I have a SQL Server 2012 instance, and an Active Directory group, which I can add Active Directory users to. I have my personal AD account (MyAccount) and a service account (ServiceAccount) in that ...
3
votes
1answer
1k views

How do I identify the program that called a stored procedure?

As part of an application database design spec, we've been asked to block execution of a set of stored procedures if they have been fired directly from SSMS / osql / sqlcmd and so on; that is, they ...
3
votes
4answers
2k views

Working Linked Server Query fails in sp_send_dbmail

Take the following example: EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@whatever.co.uk' , @query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' , @attach_query_result_as_file = N'True' ...
3
votes
1answer
1k views

Different Results From sys.databases As Different Users

We have a SQL Agent job that is configured to run as user "dbo". This job gets a list of databases defined in sys.databases that match a naming scheme. The job then performs some dynamic SQL to ...
3
votes
2answers
3k views

What is the difference between a SQL Login and a Database User in terms of security?

I am currently working with a consulting agency to set up a new production environment. They have set up a separate SQL login for each database. However, there is no corresponding database user ...
3
votes
2answers
865 views

Microsoft SQL Server 2005 Encryption Options

I'm trying to explore some encryption options on Microsoft SQL Server 2005. I've recently got around SQL Server 2008 and was able to enable Transparent Data Encryption (TDE). Now I'm trying to figure ...