I wanted to set up a trigger to alter me or log to a table when someone is added to a server role. The event for the trigger I wanted to use is ADD_SERVER_ROLE_MEMBER. When trying to create the trigger, I get the following information:
"ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.
Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.
Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use? I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.
If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.
Many thanks,
Mark
I'm developing an ASP.NET2.0 application which accesses a SQL Server 2005 Express database. I plan to use integrated security for access to the database.
I'm confused about the relationships between Windows groups, the ASP.NET web.config file <allow roles=.../> and SQL Server roles.
I would like to create a Windows group to which I can assign multiple users and grant that group access to a Web Site using windows authentication and also grant that windows group access to the database my web application uses.
I have gotten the combination of Windows Authentication to the web site and to the database to work for a specific windows user but I am having trouble determining the combination of database security entities I must create to allow access to my database by members of the windows group.
For a Windows user:
1. Create Windows user
In SQL Express
2. CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE =
3. CREATE USER FOR LOGIN
4. CREATE ROLE
5. EXEC sp_addrolemember <role-name> <user-name>
For a Windows group, what would be the equivalent commands necessary to grant a windows group access to my database? Specifying the Windows Group name in sp_addrolemember does not appear to be sufficient even though the documentation states that a windows group name is a valid value for the member name argument.
I am in the process of locking down the SQL Server in an environment that is considered to be in production (pilot stages) and there is no staging or test environment that mirrors it. I need assistance in determining the server and database roles to assign to existing logins, most of which currently have sa and dbowner rights. Because it is not a development environment, I need to be sure that downgrading the server and/or database level permissions will not break any functionality.
I'm starting with the logins that have the SA fixed server role. These logins need to be able to install applications that require the use of a backend database, which will be stored on SQL Server. In addition, through the installation process a new login/password for the newly created database(s) is normally created. For the existing logins with the SA fixed server role, will downgrading to the securityadmin and dbcreator roles be sufficient to facilitate those needs, or are those too much/ too little? And should any user account ever be granted the SA role? If so, what questions could I ask to determine this need?
Since these install process for these applications usually prompt to install using SA or local system account to authenticate to SQL to create the new database(s), that account should have securityadmin and dbcreator roles to create the database and its tables, as well as add a new login to that database.
Please address this question, keeping in mind that the logins will only be performing the described actions, installing apps using SQL Server as the backend database and adding a login to that database (which may or may not be done during the installation process).
Thank you,
nu_dba
Hello,
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Happy coding...
Hi,
I'm looking for some guidance/help regarding setting up a sa - lite account in SQL 2005. I need to give another admin rights to create/monitor maintenance plans, backup and restore databases, monitor performance/logins, but NOT be able to have any rights on several tables (and of course not being able to set user permissions).
I've tried using server and db roles but haven't been able to determine how to give someone w/o full sa rights access to maintenance plans.
If you can think of soemthing, please let m eknow.
Jenn
Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?
I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.
After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?
After reading Books Online, I am still confused with Database Role vs Application role.
My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.
If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?
Hi,
i need to set up some security standands in sql 6.5/7 . These includes auditing login attempts success and failure, access to db objects etc. I know sql is has very limited capabilities . can anyone tell me how to implement this without using event viewer etc??
I want to know are there any other third party tools that are used for Auditing the SQL Server Like...DBAudit.
View 2 Replies View RelatedHi all:
I need some help with this. I have a development server and all the developers log in as sa. Lately test data has been mysteriously deleted from selected tables. I need to track the time that the activity is taking place so I can figure out who might be playing this little game. Can anyone suggest what I can do to find this out? Your quick response will be appreciated.
I'm wanting to do some auditing with sql 2k and wish to get the users first and last name of the windows account to log into a table. You can easily access the windows account name by using the System_User keyword. However, is it possible to get the first and last name of the system_user? If so, how?
Thanks.
Does anyone have a query to determine if auditing is turned on and what it is set to (It needs to be set to failed logins)? Also where the log directory is going? I need the query to work on both SQL Server 2000 and 2005 servers. Any help is appreciated.
-Kyle
I am trying to create a sql script that will check the database instance for any new objects that are created in any database on my system. These objects I want to audit are users, tables, databases, stored procs, etc.. I also want the script to email me and write the information to a text log file. Thanks in advance for any help.
View 2 Replies View RelatedHello,Can anyone tell me how to monitor logins/logouts to Analysis Servicesdatabases? I use Profiler to do so in SQL, but cannot find a way to doit in AS.Thanks,Tim*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedBelow is a script I found that will help me turn on C2 auditing. The problem is that I am generating trace files that take up way too much space.
I need to know what column id and event id, so I am only turning on "failed login" and nothing else.
exec sp_trace_setevent @TraceID, x, x, @on
Code Snippet
CREATE PROC AuditTrcProc AS
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1
-- Please replace the test InsertFileNameHere with an appropriate
-- filename prefixed by a path, eg.. c:MyFolderMyTrace. The .trc extention
-- will be appended to the filename automatically. If you are writing from
-- remote server to local crive, please use UNC path and make sure server has
-- write access to your network share
declare @cmd sysname
set @cmd = 'copy c: empsessiontrace.trc c: empsession' + cast(cast(rand() * 1000000 as int) as varchar)
print @cmd
exec master..xp_cmdshell @cmd
set @cmd = 'del c: empsessiontrace.trc'
print @cmd
exec master..xp_cmdshell @cmd
exec @rc = sp_trace_create @TraceID output, 2, N'c: empsessiontrace.trc', @maxfilesize, null
if (@rc != 0) goto error
-- Client side file and table cannot be scripted
-- set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
return @rc
finish:
return @TraceID
Is there any way to know the auditing table name in sql server 2008.
I am performing auditing for practice . i selected application log to store the audit logs.
I want to know in which table auditing results wil be stored .
Hello,
We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.
Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.
Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...
It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)
3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.
We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?
Thanks!
Marc Lacoursiere
RooSoft Computing
Hi! Can anyone say which ms sql server predefined roles are similar to the following oracle predefined roles: dba, connect, resource. I already know that sysadmin in MS SQL Server is the same as DBA in Oracle but what about the rest?
Thanks a lot.
I'm a VB programmer creating apps to write/edit/delete data to a SQLServer 2000 database.For HIPAA requirements, I need to track all changes to all the tables inour database. I'm looking for the easiest and cheapest solution.I have no experience writing Sql Server 2000 triggers and storedprocedures.I have found the following application which might do what I need to do:Upscene: MSSQL Log ManagerPrice $125http://www.upscene.comKrell Software: OmniAuditPrice $399http://www.krell-software.comApex SQL Software: Apex SQL AuditPrice $599http://www.apexsql.comLogPI: LogPIPrice $825http://www.logpi.comLumigent: Entegra for SQL ServerPrice ???http://www.lumigent.comAny comments sugestions appreciated.Gregory S. MoyInformation Processing ConsultantEpiSense Research ProgramDepartment of Ophthalmology & Visual SciencesUniversity of Wisconsin - Madison
View 1 Replies View RelatedHi,
I have few tables. I want to identify the RECORDS for a table which has been created/modified for a particular date and time. I don't want to write a trigger to capture the event for add/update.
Is there any system table which track for date and time using stored procedure each individual records which has been last updated or newly created records??
Note : The application already created without lastModified date and each table... so, we don't want to modify the application or db.
Database : SQL Server 2000 sp4
thanks in advance.
I am trying to import this years worth of failed logins and last successful login for each user out of the logs using master.dbo.xp_readerrorlog. The script essentially loops through the linked servers I have on my DBA box and reaches out for the log data. It works, but here is the error I am getting on most of our production servers:
OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 17
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" was unable to begin a distributed transaction.
I know how to enable distributed transactions on the servers that error out, but if it is not needed for anything other then my audit script, I doubt the business will approve me turning on distributed transactions at those locations (so I am not even going to ask).
I am attempting to setup a singular audit .rdl with the information I want to review quarterly.
CREATE PROC [dbo].[Import_Login_Data]
AS
IF EXISTS (
SELECT 1
FROM master.sys.servers
WHERE is_linked = 1
[Code] ....
Hi all,
I am facing some trouble in my asp.net application. We have decided to add some more security at the DB. Every user gets his own login in SQL-server. (I know for connection-pooling it is better to use the exact same connectionstring, but security is the most important fact in our project).
What I want to do is add sql-server roles to new created sql-server users. I can create sql-server users from my code and I can GRANT or DENY rigths to a specific table, but I don't know how to give a user a role.
Any ideas?
Thx,
BKT
Hi,
I want a user login to have permissions to view sql server jobs that they are not the owner of without giving them system administrator permissions,
Is there any way to do this?
Hi,
I have just received my notebook from my work, and Sql Server 2005 is installed on it. There are 2 accounts, the SA (i dont know this password), and my Windows account.
When I go to security - server roles; I see that only the SA account is assigned to the sysadmin role. But my account is not assigned to any server role and when I want to create a database I get the following error: CREATE DATABASE permission denied in database 'master'. (This is at my local database)
Is there anyway I can get passed this restriction? (without hacking my own pc?)
With kind regards
What roles or permissions does a user need to create stored procedures?
Jshurak - The International Businessman of Mystery
Hi,
I have some stored procedures that I use in my .Net application. These stored procs are created by the role of [dbo]. Now I want to deploy the application along with the database. On the live database I have created a new user, but I cannot give this user the role of dbowner. I have only given it the roles of public, dbreader and dbwriter.
Now the problem is that I am not able to execute my stored procedures. So what should be the solution for this problem. Should I modify my stored procs and create them with the role of public or should I give the user the role of a dbowner.
I also want to know, what is the best set of roles I should give a db User and yet not compromise the security of the database.
If any one knows the solution to my problem, please reply or provide some links that would help. Urgent
Regards
Vineed
I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.
When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.
Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.
After "copying" a SQL Srv DB from one server to the next, we have found that the permissions within the developer-created database roles did not transfer. How can we insert these permissions without doing it manually? Or can we create some type of DTS package that will bring the roles' permissions for us? Or how do you move a SQL database from one server to the next and get everything within it to come along??
View 3 Replies View RelatedShort of system administrator, does anyone know what server role a login must be assigned to in order to execute DTS package in Query Analyzer?
View 9 Replies View RelatedI am currently working on a SSIS configuration database. However, I am having trouble writing a script to get the the users that have each server role permissions. I'm pretty sure that I need to use sp_helpsrvrole, but am not sure where to go from here. If anybody has any ideas or has seen a script on the internet to get this then that would be a tremendous help.
Thanks in advance,
Kyle
Hi!
I have developed a database in MS SQL Server 2005 Express, to which I would require only bulkadmin server role from an external java application, because I only need to update rows, insert values or use select queries in the database.
The problem is that, using either the Microsoft JDBC Driver 1.1 or the Java JDBC ODBC Driver and the Windows XP Data Base (ODBC) configurations, I need a user with sysadmin server role inside Sql Server, otherwise JDBC won't connect to the database using the selected user. Even if I leave the sql login with setupadmin or any server role lower than sysadmin, the connection is refused.
Is there no way to connect using JDBC to MS Sql Server 2005 other than granting the connected user sysadmin rights? My code looks as follows:
Code Snippet
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://FIREBLADE\SQLEXPRESS";
String user = "username";
String password = "password$$";
Connection conn;
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
if (conn != null)
System.out.println("SQL Server Connection established ...");
I have heard that Java JDBC connections to Microsoft require high-level access.
Any informed answer is more than welcome. Thanks for reading my post!
I need to find out how to add roles to my SQL that is on a hosted server.I have created 2 roles, but after publishing and uploading my site, when I try and create a user, I get the error:"Exception Details: System.Configuration.Provider.ProviderException: The role 'userAtype' was not found."There are two other roles that the same happens with.Running it locally all was fine, I just didn't seem to get this info where it needs to be when I uploaded it.I'm new to messing about with SQL, so sorry if this is really elementary.I have VS2005 Standard, and the SQL Server Management Studio Express.The hosting provider I have does not allow remote connections to the DB on shared hosting, so I need to (?) generate a script to populate the DB? and run it in their browser-based Querry Window?How would I do this?Thanks.
View 4 Replies View Related