I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services.
The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database.
In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint.
Am I correct in the above, or is there a more efficient way to achieve these results?
I have a MSSQL Report Services Report Model set up to allow users to create their own ad hoc reports. The data source for my model is a Named Query that queries a MS SQL view that actually pulls data from a series of tables & other views. When you create a report from this model and attempt to filter the data some of the fileds will provide you with a pick list to select which values you would like to filter on and other fields do not provide you with a pick list but require you to enter the data directly that you wish to filter on. Most of the data fields that I am trying to filter on are varchar fields and like I said some will create a pick list for you to selet from and others will not. This all seemed to start after I changed my data source to a Named Query rather than having my data source as the MS SQL View directly. I did this becuase it seemed that anytime I had to make a change to the views that the data source pulled from it would mess up any existing reports that had been created and this does not happen if I use a named query. I have gone crazy trying to figure this one out so any ideas would be greatly appreciated!
I am very new to the SQL database. I have the following query. I would appreciate if someone could clarify this for me:
I have created two users (user1 & user2) under the same login name test1 in SQL Server 2005 Database. Further I used the login name (test1) & password (******) of SQL Server in connection string to connect to database.
Now I want to know that how & where can I refer the user name (user1 or user2) to use its previliges.
How will I know that which user's privileges level is used in the connection.?
My VB.net application manipulates data in a local SQL Express database. When the app is installed, the database does not exist, but it cannot be created at run-time by anyone other than a user with administrator privileges. In addition, the application shares the data stored in SQL with a critical 3rd party component that can only reach the database via named DSN (also not existing prior to installation).
I see my primary SQL security options as being:
(1) Use SQL or Mixed Mode authentication with an admin-level username/password combination, or (2) Create a db user/group with admin-level privileges and grant membership to all NT authenticated users
Secondary problem: Creating the DSN.
Does it make sense to create a Custom Action (.dll) that is called at the end of the installation process in order to create the database, the user security context and the named DSN?
Am I overlooking some built-in functionality provided by Visual Studio 2005 that will accompish some or all of this for me? I am aware that customizing the 'silent' installation of SQL Express to use a different authentication mode requires manifest tweaking -- I just don't know anything about setting up the appropriate security for this situation. Would you put the db and role creation stuff in a SQL script and execute it post-install?
Thanks in advance for any insight you can provide.
Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.
I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.
is there a master table that i could querry to buiild a report on the users who have acces to the various databases on the SQL server. I am trying to use master.dbo.syslogins but that is not generating what i need.
I need to be able to list all User connected to a particular database, after doing a bit of playing I have writen a simple view in the database to show this information which looks like
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'AutoCost2008
But my problem is that as this needs to be run from an application where the user is logged on as a standard user and therefore when this is run it only shows the current user, I am not sure what permissions I need to give this login to allow them to view all users connected to this database without giving them full Admin permissions, can anyone please help.
Is there any way in which I could distinguish a group of Database users from the other users. Say for e.g. store the Database users hierarchically, etc. Adding a Prefix/Suffix to the user name as a distinguish-er will not work in my case. I want to restrict the deletion of these Database users. Even the login with sysadmin or serveradmin rights should not be able to DROP those Database users.
My developers would like a 'sandbox' database with full ddl and dml permissions, however, they do not want others to read/change/drop their objects. With SQL 2005, can DDL permissions be granted to a user at the schema level? I'd rather not set up a database for each developer.
we have an application which lets users connect to production database with windows credentials, They are able access the sql tables too with windows login. I want to restrict them from accessing the sql tables. How do I do that.?I tried a db_deny but that prevented them from accessing the application too.
A client has received the results of a security scan suggesting that Execute privileges granted to the certificate ##MS_AgentSigningCertificate## on the master database are a security hole which needs to be fixed. I'm unsure of the affect do doing this. We have a very vanilla sql server database, we don't use certificates, we just connect via jdbc to the db and do OLTP work. Are there basic functions associated with ##MS_AgentSigningCertificate## that will fail if I revoke privileges? We only need to be able to log on to a user database and do DML and basic backup and recovery.
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:Â
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
SQL Server in on a ‘member’ server in my company domain (We took the ‘stand-alone’ option when installing NT on this server).
I have set up an NT domain account for SQL ServerAgent ‘Service startup account’ which is a different account than the NT domain account listed in the SQL Server Properties, Security tab, ‘Startup service account’.
I log on to this server with the login in the latter.
Replication is working OK, but my application log keeps filling up with the message “SQLServerAgent security context does not have server autorestart privileges”.
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
I need a way to automatically generate a daily report of user names, time of access, and type of access (login, select, insert, update, delete, etc.) to a database.
We need to audit all accesses from all accounts to find out who is accessing the payroll system. We have already defined the security to be as tight as possible - however we need to monitor all Administrator accounts to ensure they are not being used to obtain sensitive information.
I have looked for some software to read the live SQL Transaction Logs, but none of the tools I have seen can do exactly what we require. For example, I've tried Lumigent Log Explorer (http://www.lumigent.com/products/le_sql.html), but it's not automated and the report output is just garble to our payroll staff.
I have looked up all Microsoft SQL Server partners but their software is all about data recovery and data reporting. We need Security Reporting.
I have created a general SQL Profiler trace that may suffice if everything else fails, but I still have no way of automating it - it relies on Real-Time monitoring which I can't trust (admin can turn this off, do their stuff, turn it back on). Also this is dependant on someone turning it off, save the log, turn it back on, every day - this person would be the Administrator - what admin would monitor themselves?
- SQL Trace seems like a good idea, how do I read the trace log file? How do I automate a trace?
- Can we automate the SQL Profiler to read the transaction log to find all accesses to a database?
- Does anyone know what we can do to automatically audit accesses to a database? What format can the report come in (pdf, xls, dbf, html etc.)
- Recommended software to try?
- Ideas for alternatives to try?
- Any companies/developers out there who know of a tool or are developing a tool to provide this sort of report?
- Is there a spec somewhere to let us write our own software to query the live transaction log?
Thanks, I appreciate any help. Kind Regards James Payne
I am trying to find a select on sysprocesses that would list all the activelogins. An active login is a login that has a TSQL statment being executedon the server,This didnt work to well! Any ideas. Thanks in advance.select sp.loginame,-- more columnsfrom master..sysprocesses spwhere sp.status not in ('sleeping','background' )order by 1
I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master alter database xxx set read_write with rollback immediate alter database xxx set single_user with rollback immediate
use xxx update tablexxx set columnxxx=yyy use master alter database xxx set read_only with rollback immediate alter database xxx set multi_user with rollback immediate
I know how to look at active users from Enterprise Manager, but how can I query out the information - what table is that info stored in ??? I don't want a list of all the logins, I just want the list of active users....
My insert statement for #Data - I only need to process each @EmployeeID one time, which is why I thought a loop would be sufficient, but I let this process run for 2 hrs and it still had not completed, so I feel I must have set-up something incorrectly!
This is my syntax, I am creating a table of active users, then wanting to get data for each of those active users. Â But only get the data for each active user 1 time.
Declare @EmployeeID varchar(50) CREATE TABLE #ActiveUsers ( ID INT IDENTITY NOT NULL ,EmployeeID varchar(50) ,processed int ) Create Table #Data
I'm trying to extract all the users and their membership to groups, and the membership of groups to groups from active directory though a link to server.
I can get the users. I can get the groups.... individually.
I can't get the info of what user is a member of or who are members of a group.
Anyone know how to do this or am I going to have to right a vb app? (Anyone already got the code...)
I want to load this data into tables for reporting in my Data Warehouse.
I am trying to get list of users on different dbs located on different servers.Currently i get them manualy by going on each db and copy the users to excel sheet.I tried to use sysusers, but i needed status of their account, created . Is there any easy way where i can pull list of users from all dbs from at once.I also need to exclude dbo accounts ans sa accounts if possible
Say this table grows very large, what would be a good way to fetch a list of users that was configurable to bring back all or some of the users ? How about a procedure that uses a range of user id's. So if you wanted ALL of you users, you just use a range of inputs that span the entire range.
comments ?
CREATE PROCEDURE EndUsers_Select @lowuser int, @highuser int
AS
SELECT eu.FirstName ,eu.LastName ,ci.Email FROM UserTable eu JOIN ContactInformation ci ON eu.ContactInformationID = ci.ContactInformationID WHERE EndUserID < @highuser AND EndUserID > @lowuser