InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 21 Nov 2009 03:48:21 GMT20http://www.sqlservercentral.com/Forums/Topic822769-359-1.aspxWe are planning to open up access to our reporting server (i.e. non-production SQL database) to a broader range of users who could write their own SQL code and pull their own reports. This way they won’t have to wait on our small and overstretched department for their more trivial requests. These people know just enough SQL to be dangerous. They would, of course have only read access ([font="Courier New"]SELECT[/font]). However, even with read-only access, it appears that users can still use Hints to obtain exclusive locks, like [font="Courier New"]TABLOCKX[/font] and [font="Courier New"]XLOCK[/font]. We are concerned that someone might try using it and end up locking people for the duration of their possibly long running query – almost a DoS (Denial of Service) attack, if you will. Even though this is a non-production server, we don’t want this to happen.Does anyone have any suggestions for preventing users from obtaining exclusive locks? If not, do you have any suggests for automatically killing such queries after the fact, like a script that SQL Agent runs every 5 minutes, or something like that?Thank you so much for your help and time.Fri, 20 Nov 2009 16:02:43 GMTjoseph.wagnerhttp://www.sqlservercentral.com/Forums/Topic821892-359-1.aspxOne of my coworkers has been getting this error when he tries to log into our SQL Server 2005 instances from home. What we have found strange is that he can connect to any of the instances using his laptop when he's plugged in at the office, but he gets the "Cannot generate SSPI context" error when he tries to connect from home. We have Juniper for the VPN and he can access other network resources when he's connected. When I connect from my house and try, I have no problems connecting to the SQL Server instances.I've tried checking profiler and logs when he's made connection attempts, but I don't see anything. We've been digging around at this for several days now and tried checking quite a few things. His login looks alright, we've tried pinging the server, flushing the DNS on his machine. Just can't figure it out.Any ideas?Thu, 19 Nov 2009 12:42:53 GMTIan Massihttp://www.sqlservercentral.com/Forums/Topic671366-359-1.aspxWe have a request from client to hide all system views/tables from users in SQL server 2005.As user assigned to a specific database role, client do not want the user to see all system tables and INFORMATION_SCHEMA views, so they can have a clear view for only user tables in their schema.However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views.Also when connecting from SQL Management Studio, they are getting same list.We have taken following steps, but no luck.1. DENY permissions on "View Definition" at all scope levels but still the users can see all these views using ODBC.2. Tried denying access by changing permissions to deny in the public role, but still the same.3. Created one Role including deny permissions to all sys and INFORMATION_SCHEMA views and assigned to user, but same issue.Please advise is there any way of doing itMon, 09 Mar 2009 05:49:04 GMTsivaprasadhttp://www.sqlservercentral.com/Forums/Topic821028-359-1.aspxDoes anyone have any experience of using this? Microsoft have a 32bit version for SP1 and SP2 but nothing for SP3 (which we have installed at our place). Can anyone tell me if the SP2 will work fine under SP3?Any information regarding the above or anyones experiences using it regarding performance and what you can acutally get from it would be greatly appreciated!!Wed, 18 Nov 2009 10:43:31 GMTSwirl80http://www.sqlservercentral.com/Forums/Topic820298-359-1.aspxHi,Im new to SQL and am really struggling with this problem.From a VB application(a SCADA package) I am opening a connection to an SQL database to run a report - the connection code opens fine (ODBC). I am using Windows Authentication. The User that is launching the report is a domain user. This Domain user is part of a Domain Users groups on SQL. On running report after approx 30-45 seconds I get the following error [Microsoft][ODBC SQL Server Driver][DBNETLIB]Connection Write (send ()).However If I create the Domain account in SQL rather than depending on it being recognized as part of the Domain group than this error dose not occur and report launches within seconds. This is an intermittent error. So at times its seems that that it cannot authenticate the user with the domain controller.Hope someone can shed some light on this for meThanksAntTue, 17 Nov 2009 12:15:12 GMTanthonymurray30http://www.sqlservercentral.com/Forums/Topic820677-359-1.aspxMy sql server 2005 got corrupted and when i tried to reinstall i am getting an error like [b]you may review set up logs[u][/u][/b] . Can any one help me out iin solving this problem. I even tried to uninstall and install again but the same problem i am facing again and again. I even tried to remove the folder of MS SQL SERVER in program files but i was unable to delete the .dll files and log files. Please any one help me out soon.....?I am attaching the error snapshot please check that out and figure out a solutionWed, 18 Nov 2009 03:26:13 GMTramu.qyaohttp://www.sqlservercentral.com/Forums/Topic819545-359-1.aspxHi, I am new to sql server. I logged into sql server 2005 (windows authentication)with admin rights and created a new database. There are no tables created under system tables. Can anyone explain why they are not created? Thanks for your help.Mon, 16 Nov 2009 11:09:50 GMThiranbhttp://www.sqlservercentral.com/Forums/Topic818319-359-1.aspxHi All,Is there any way to prevent the DBA's (having SA role) from reading the data.The database is a kind of sensitive data, so the DBA's should not be able to view the data in tables, but they will have all other privilleges to administrator the SQL ServerFri, 13 Nov 2009 02:53:03 GMTSubhash-63067http://www.sqlservercentral.com/Forums/Topic819184-359-1.aspxHi.I wanna develop an accounting , Payrol System. in my old system, i Create one table for users and all objects in db are in this table. by value of this fields i control the access of users in application.but in some application , some users,roles defined in Database and users that define from application mapped to this users. 1) how to do this work. 2) hand-rolled security is better than SQL Server Security?thanks.Sun, 15 Nov 2009 21:53:43 GMTHamid-Sadeghianhttp://www.sqlservercentral.com/Forums/Topic819185-359-1.aspxHi.I wanna develop an accounting , Payrol System. in my old system, i Create one table for users and all objects in db are in this table. by value of this fields i control the access of users in application.but in some application , some users,roles defined in Database and users that define from application mapped to this users. 1) how to do this work. 2) hand-rolled security is better than SQL Server Security?thanks.Sun, 15 Nov 2009 22:04:22 GMTHamid-Sadeghianhttp://www.sqlservercentral.com/Forums/Topic815148-359-1.aspxHi All,I had a question regarding disabling extended stored procedures for security purposes in SQL Server 2005. For example, if I want to disable "xp_regread" I tried the following command:sp_configure 'show advanced options', 1;GORECONFIGURE;GOexec sp_configure 'xp_regread', 0;GORECONFIGURE;GOHowever, I get the following error message: "Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51The configuration option 'xp_regread' does not exist, or it may be an advanced option."Does anyone know why this is happening and is there a different method I should use to disable extended stored procedures? This is the only method that I've been able to find online. Is there even a way to disable XP's or do you have to delete them?Thanks for any insight in advance,GreggFri, 06 Nov 2009 12:40:25 GMTgreggkellyhttp://www.sqlservercentral.com/Forums/Topic818267-359-1.aspxHi ,Application people trying to connect from application server(au07qap06mtel32) to SQL server(au07qap06ntel32).but they tried to connect to SQL server(au07qap06ntel32) directly they were able to connect to SQL.they had problem when they were trying to connect from application server.They got below error:- -------------------------------------------------------General Information *********************************************Additional Info:ExceptionManager.MachineName: AU07QAP06MTEL32ExceptionManager.TimeStamp: 12/11/2009 5:37:46 PMExceptionManager.FullName: Microsoft.ApplicationBlocks.ExceptionManagement, Version=5.4.744.0, Culture=neutral, PublicKeyToken=nullExceptionManager.AppDomainName: Msg1Admin.exeExceptionManager.ThreadIdentity: ExceptionManager.WindowsIdentity: TEST-ACCOUNT-01\d3646191) Exception Information*********************************************Exception Type: System.Net.Sockets.SocketExceptionErrorCode: 10054Message: An existing connection was forcibly closed by the remote hostSocketErrorCode: ConnectionResetNativeErrorCode: 10054Data: System.Collections.ListDictionaryInternalTargetSite: Void HandleReturnMessage(System.Runtime.Remoting.Messaging.IMessage, System.Runtime.Remoting.Messaging.IMessage)HelpLink: NULLSource: mscorlibStackTrace Information*********************************************Server stack trace: at System.Net.Security.NegoState.ProcessAuthentication(LazyAsyncResult lazyResult) at System.Net.Security.NegotiateStream.AuthenticateAsClient(NetworkCredential credential, String targetName, ProtectionLevel requiredProtectionLevel, TokenImpersonationLevel allowedImpersonationLevel) at System.Runtime.Remoting.Channels.Tcp.TcpClientTransportSink.CreateAuthenticatedStream(Stream netStream, String machinePortAndSid) at System.Runtime.Remoting.Channels.Tcp.TcpClientTransportSink.CreateSocketHandler(Socket socket, SocketCache socketCache, String machinePortAndSid) at System.Runtime.Remoting.Channels.SocketCache.CreateSocketHandler(Socket socket, String machineAndPort) at System.Runtime.Remoting.Channels.RemoteConnection.CreateNewSocket(EndPoint ipEndPoint) at System.Runtime.Remoting.Channels.RemoteConnection.CreateNewSocket() at System.Runtime.Remoting.Channels.RemoteConnection.GetSocket() at System.Runtime.Remoting.Channels.SocketCache.GetSocket(String machinePortAndSid, Boolean openNew) at System.Runtime.Remoting.Channels.Tcp.TcpClientTransportSink.SendRequestWithRetry(IMessage msg, ITransportHeaders requestHeaders, Stream requestStream) at System.Runtime.Remoting.Channels.Tcp.TcpClientTransportSink.ProcessMessage(IMessage msg, ITransportHeaders requestHeaders, Stream requestStream, ITransportHeaders& responseHeaders, Stream& responseStream) at System.Runtime.Remoting.Channels.BinaryClientFormatterSink.SyncProcessMessage(IMessage msg)Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at ResourceAccessService.IResourceAccessService.RasiGetFontId(Guid& id, Guid clientId, RsiFontType& type, Int32 locale, String sTypeface, Boolean bold, Boolean italic, RsiFontCharset charSet, UInt16 usPointSize) at Msg1Admin.EditFontsCtrl.OnFontEditOK(Object sender, EventArgs e)For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp._------------------------------------------------------------------------------------------------------------pls help me how to troubleshoot this client connectivity issue and how can i make sucessfull conection form application server(au07qap06mtel32)to SQL server(au07qap06ntel32).Thanks in advance.Fri, 13 Nov 2009 00:17:13 GMTmohinidbahttp://www.sqlservercentral.com/Forums/Topic628499-359-1.aspxis DecryptByPassphrase secured to use in order to encrypt a column value..I'm able to encrypt and Decrypt a column value using encryptByPassphrase & DecryptByPassphrase easily.But i dont know how secured these methods are? Is it possible to break the password and know the columna value..Please help me on this.Thu, 01 Jan 2009 13:29:25 GMTvenkatesh.kuppilihttp://www.sqlservercentral.com/Forums/Topic812320-359-1.aspxHi,we want to enable our users to query some information about their databases which usually can only admins. The plan is to provide some functions/procedures, and let them be run as a priveleged "Admin" User.So, we have an admin_login on the server and an admin_user on the database mapped to the admin_login.The following Function only works if it is run as the admin_login:[code]CREATE FUNCTION [admin].[fn_db_get_sqltext]( @spid SMALLINT)RETURNS NVARCHAR(4000)---- admin.fn_db_get_sqltext---- Retrieves the SQL text for the given sessions id, only for the current-- database.---- Paremeters:-- @spid The session id the retrieve the current SQL text for.---- Return value:-- The SQL text for the given session_id.--BEGIN DECLARE @SqlHandle VARBINARY(64) DECLARE @SqlText NVARCHAR(4000) -- Get sql_handle for the given spid. SELECT @SqlHandle = sql_handle FROM sys.dm_exec_requests WITH (nolock) WHERE database_id = DB_ID() AND session_id = @spid -- Get the SQL text for the given sql_handle. SELECT @SqlText = [text] FROM sys.dm_exec_sql_text(@SqlHandle) RETURN @SqlTextEND[/code]This works:[code]execute as LOGIN = 'xxx_admin' select [admin].[fn_db_get_sqltext] (1) --exec [admin].[sp_list_blocking_sessions]revert[/code]But I have to get it to work with the Admin_USER on the Users database. The execute as clause in the function works with a user, not with a login.So, what can do?Thanks in advance,TobeMon, 02 Nov 2009 05:33:22 GMTtobe_hahttp://www.sqlservercentral.com/Forums/Topic816129-359-1.aspxGood day everyone,Iam trying to limit the access for SQL 2005; I created a role designed to select tables and views only; i added table and view securables, and chose it one by one; what do i need to choose under the list of explicit permissions. I tried using just the select and still not working; they still dont have access on the specific table; need your help. what other permissions do i need to grant?thanks in advance,Mon, 09 Nov 2009 14:17:32 GMTcute_lhen05http://www.sqlservercentral.com/Forums/Topic813231-359-1.aspxHello,I have a database user that has their own schema, which is used by their application to connect to the database.I have given the user dbo rights to the database.Initially the user's default schema was their own user name, however, when they created db objects it would create it with their schema instead of dbo. I tried making dbo the owner of the users schema to have new objects created by the user save as dbo schema but this did not work.I would appreciate any advise one could provide!Thanks!!HawkeyeDBATue, 03 Nov 2009 14:23:30 GMTHawkeye_DBAhttp://www.sqlservercentral.com/Forums/Topic815232-359-1.aspxHi Everyone,In short I am trying to create a view in my application db that joins to msdb.sysmail_allitems so that I can include the status of sent emails(sent/failed). This will be displayed to my user. I have a sql agent job that generates the emails and I capture the sql email id and some additional data to my application table so I can make the necessary join.My view works correctly when I run it as myself with a windows login and that makes sense because I'm an administrator.When the standard security login for my application tries to run the view the column from msdb.sysmail_allitems is blank, because I left join to it. When I run select * from sysmail_allitems under that login no rows are returned even though there is data in the view.I suspect that I have not correctly granted access to the msdb sysmail_allitems view, or some related and/or underlying objects.Has anybody done this or know how to do it correctly ? I do not get any permission errors when I query the view, just no data comes back.What I have done so far:- Under server security I added the login to the msdb database.- In UserMappings I checked DatabaseMailUserRole, db_datareader, and public- I also ran grant select on sysmail_allitems to public but it did not change anythingIs this even possible ?Thanks for any help.Bill,Charlotte NCFri, 06 Nov 2009 15:55:35 GMTWilliam Plourdehttp://www.sqlservercentral.com/Forums/Topic814102-359-1.aspxHi, how to prevent injection with trigger ? it's possible ? many thanks,Thu, 05 Nov 2009 03:46:23 GMTm_belhocinehttp://www.sqlservercentral.com/Forums/Topic813192-359-1.aspxIs there a way to deny a right for all tables in a database without explicitly defining each table in the command? My problem is that I have a user who is assigned to the public role, and should only be able to read the tables. But if I look at the table permissions this user has update granted to him as well. I am able to deny update to the tables by typing them out using the command: DENY UPDATE ON <table_name> TO <username>What I would like to have is something that would allow me to deny update on all tables, but I don't know if this is possible using the above approach, or if this is scriptable. Any suggestions are appreciated.Kurt KapfererTue, 03 Nov 2009 13:29:46 GMTkkapfererhttp://www.sqlservercentral.com/Forums/Topic729106-359-1.aspxWe run a report on the first of the month to list the status of SQL Server logins on all of our servers. Part of the code for this report uses the LOGINPROPERTY function to show the date the login passwords were last changed and, from this date, to calculate when the password is due to expire. We recently changed the sa password on all servers a week before the report was run. Some of the servers, however, do not show the password last change date for sa as having been changed.Does anyone know how I would begin to investigate this problem?Where is this date stored?Is there any reason that this date would not be updated when the password is changed?Thu, 04 Jun 2009 10:15:12 GMTBrian Brown-204626http://www.sqlservercentral.com/Forums/Topic812826-359-1.aspxI've identified a number of logins on our live servers that have sysadmin rights. I suspect most of these are not actually in use but before disabling them wanted to check when they were last logged in.Is there any way of doing this ? I can't find anything in the DMVs or sys views. This is for SQL Server 2005 and 2008.ThanksDTue, 03 Nov 2009 03:10:32 GMTDG 9336http://www.sqlservercentral.com/Forums/Topic814333-359-1.aspxHi all,In one database, there are 4 users having "DB_Owner" role in SQL server 2005. I need to trace who issues "truncate table" and save the login name and datetime to an audit table.Either DML or DDL can't implement it (I might be wrong). What else can I do?ThanksThu, 05 Nov 2009 09:01:58 GMTJudy-363345http://www.sqlservercentral.com/Forums/Topic814627-359-1.aspxHiI have a problem where I cannot create/edit maintenance plans on an existing install of SQLServer 2005 SP1. I delete exisitng maint'plans manually to stop them erroring in logs and replaced with normal Agent jobs.I cannot create any new plans; result in mscorlib exception.I've been told this machine was set as a domain controller it isn't now.If I try and run any of the SQL services e.g. SQLAgent as LocalSystem I get error "No mapping between account names and security IDs was done". I THINK this is because in SQL Server logins I have the 3 service logins in this format:DOMAIN_NAME\SQLServer2005MSFTEUser$COMP_NAME$MSSQLSERVER.If I try and get an SID value for this user it understandbly fails - this domain doesn't exist..The guys have tried putting SP3 on - but it failed (don't know error) - but I guess it comes from the fact that DOMAIN_NAME\SQLServer2005MSFTEUser$COMP_NAME$MSSQLSERVER isn't valid.see http://support.microsoft.com/kb/925976.What can I do to get it to run as local system account? Is it worth running reg fix in article afterall I do not have a user COMP_NAME\SQLServer2005MSFTEUser$COMP_NAME$MSSQLSERVER.Or am I barking up the wrong tree.Thanks Thu, 05 Nov 2009 17:22:48 GMTterryshamirhttp://www.sqlservercentral.com/Forums/Topic814540-359-1.aspxWe have a new project which is scheduled to go to production soon and it has sensitive data in it which needs to be protected. I have put a certificate on the database server and the security folks want to verify that it indeed works and the traffic has been encrypted between servers. They have tried to use Wireshark but apparently this works for older versions of the database and we're running 2005. We're investigating whether or not we have a license for Microsoft's Network Monitor (I'm guessing that we do and appears like it's freely downloadable on their website) but in the meantime I wanted to see if anyone has any experience with other tools that have worked for them when testing encryption of SQL 2005 databases.Thanks!Thu, 05 Nov 2009 13:38:39 GMTVanessaDBAhttp://www.sqlservercentral.com/Forums/Topic811271-359-1.aspxHi guys, I'm guessing this won't be possible, but figured I'd check if it was.Let's say that some users are assigned to a User Group using Active Directory (in addition to having their own Active Directory Accounts) where all appear in SQL Server Management Studio 2005's Security - Logins.Is there any way to see which users are mapped to that Group Account in SSMS? I take the blame if an end-user can't run a Stored Procedure etc when something goes live, but I can't see myself if they have the Execute Permission without mapping a Login to their inividual Windows account.As you can imagine, I'm Sysadmin on SSMS, but don't know much about and don't believe I can access Active Directory myself, which is managed by a different group.Thu, 29 Oct 2009 16:20:54 GMTNathanB-1014493http://www.sqlservercentral.com/Forums/Topic813043-359-1.aspxHi all, A user and its associated certificates are deleted from the database 15 days ago. Now we realized it and tried to create those stuff again. But is there a way to find out when the user and the certificate got deleted. We don't have the backup of that particluar day but we have backups from 2 days after the incident took place. Any input is greatly appreciated. Thanks,VenuTue, 03 Nov 2009 09:49:54 GMTvenuchaituhttp://www.sqlservercentral.com/Forums/Topic813820-359-1.aspxIs this possible? Background:High profile, high availability system, 2005 Enterprise Edition. The application uses SS authentication. Few individuals have NT access to the DB. Those limited individuals who have NT Authentication, have been given strict instructions to limit queries to off-peak hours, because certain queries have brought down the database during peak hours, causing the DBAs to be burned in effigy. Is there a way to put a time constraint around SQL Server security, where we could allow the users read-only rights during the hours of 8pm - 4am? or similar?Wed, 04 Nov 2009 12:37:02 GMTlbradyhttp://www.sqlservercentral.com/Forums/Topic813353-359-1.aspxDears Sirs....I have the following issue...I backed up a database from a Production Environment and restore it in a Development environment and also in a Test environment...Some Developers and Programmers had acess (db_ddladmin, db_datawriter, db_datareader and others...), and after the restore, some of them can't acess this database anymore... 'cause the permissions are lost.. I tryed to use the "sp_change_users_logins " but it didn't work...The question is: Is there any way to export the users from a especific database and them import them in another database exactly equal to the one where they were exported from ???Thank you very much... Tue, 03 Nov 2009 17:52:38 GMTedvaldocastrohttp://www.sqlservercentral.com/Forums/Topic498813-359-1.aspxDuring a 1 hr period over the weekend, SQL Agent logged a group of three errors over and over again:[i]Message[298] SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you. [SQLSTATE HY000][/i]followed by:[i]Message[298] SQLServer Error: 848, Cannot generate SSPI context [SQLSTATE HY000][/i]and then:[i]Message[382] Logon to server '(local)' failed (SaveAllSchedules)[/i]I haven't been able to find much information about these online. Anybody have any ideas? I'm working with the networking team to determine if there was anything network-wise going on but haven't heard back from them. These problems happened for an hour and then stopped. Everything seems ok now.Thanks,RobMon, 12 May 2008 08:21:39 GMTRob Symondshttp://www.sqlservercentral.com/Forums/Topic813990-359-1.aspxWe call "Open symmetric key" from a stored proc, passing in the password like this:...OPEN SYMMETRIC KEY DECRYPTION BY PASSWORD = @PassWord...@Password as declared as varchar (100) in the stored proc input argument section. This statement does not pass t-sql syntax check. The complaint is that @Password is not a Stirng. (Really?) What is the correct syntax then?TIA,barkingdogWed, 04 Nov 2009 22:03:15 GMTBarkingdoghttp://www.sqlservercentral.com/Forums/Topic812651-359-1.aspxHi,I have recently migrated a database from 2000 to 2005. In 2000, the user had db_owner on the database, and could execute DBCC commands. In 2005, the same user has db_owner access, but is unable to execute the commands without being sysadmin.Can someone please advise?thanks.Mon, 02 Nov 2009 16:28:15 GMTtwmhttp://www.sqlservercentral.com/Forums/Topic812639-359-1.aspxI have a developer who has an app that uses a generic account that runs a stored proc which utilizes a execute as. However I am getting the error not able to access the database "Plandata" under the current security context. I thought I had everything set with the execute as but not working, any good place to startMon, 02 Nov 2009 15:45:59 GMTtimscroninhttp://www.sqlservercentral.com/Forums/Topic812303-359-1.aspxTriggers not always working. I have a table, which is accessed via a View. The view has 1 Instead of trigger and the table has 1 Update Trigger to populate an Audit Table. Both the triggers work fine, if an administrator does an update. However, for a normal user, the Instead of Trigger on View fires, but the Audit trigger on table does not fire. I have even given the user access to insert rows in the Audit table. I am not sure where I am doing something wrong. If anyone has any ideas on what could be wrong or suggestions on what to check or change, then Kindly advise.Thanks in advance. The audit trigger code is as follows: create trigger [Tr_Foreign_Currency_Grants_Data_Update] on [dbo].[Foreign_Currency_Grants_Data] for UPDATEasdeclare @bit int , @field int , @maxfield int , @char int , @fieldname varchar(128) , @TableName varchar(128) , @PKCols varchar(1000) , @sql varchar(2000), @UpdateDate varchar(21) , @UserName varchar(128) , @Type char(1) , @PKSelect varchar(1000) select @TableName = 'Foreign_Currency_Grants_Data' -- Get date and user select @UserName = system_user , @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114) -- Record Action if exists (select * from inserted) if exists (select * from deleted) select @Type = 'U' else select @Type = 'I' else select @Type = 'D' -- get list of columns select * into #ins from inserted select * into #del from deleted -- Get primary key columns for full outer join select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME -- Get primary key select for insert select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>''' from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName while @field < @maxfield begin select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field select @bit = (@field - 1 )% 8 + 1 select @bit = power(2,@bit - 1) select @char = ((@field - 1) / 8) + 1 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D') begin select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field select @sql = 'insert Audit (Type, TableName, PrimaryKey, FieldName, OldValue, NewValue, UpdateDate, UserName)' select @sql = @sql + ' select ''' + @Type + '''' select @sql = @sql + ',''' + @TableName + '''' select @sql = @sql + ',' + @PKSelect select @sql = @sql + ',''' + @fieldname + '''' select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')' select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')' select @sql = @sql + ',''' + @UpdateDate + '''' select @sql = @sql + ',''' + @UserName + '''' select @sql = @sql + ' from #ins i full outer join #del d' select @sql = @sql + @PKCols select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname select @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)' select @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)' exec (@sql) end endMon, 02 Nov 2009 04:35:37 GMTn.bhojwanihttp://www.sqlservercentral.com/Forums/Topic811974-359-1.aspxHi,Need help on security.We have a developed dts package which is created by "sa" user account and he is able to execute the package which is stored at OS level.Now, question is , i need to create a login which whom i need to give permissions.At object level i think he to be given EXECUTE permision on stored procedure.Apart from that, do i need to turn on xp_cmdshell at instance level?Or else, i have to login as a normal login with minimal permissions. But i need to change the context of execution inside my stored procedure in such a way it is executed under "sa" user account. This is what i exactly needed, how to implement this???????????Again, i need this to be a scheduled job. To run this, does the new login needs any SQLAgentReader Role to be granted ????????Any help would be greatly appreciated.ThanksSample Code for stored procedure ================================CREATE PROC P1ASBEGIN--- Specify DTS to be executeddeclare @dts varchar(128)set @dts = 'dts_Migrate_data_20072008'-- Initialize commanddeclare @cmd varchar(4000)set @cmd = '"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /F "D:\DTS_Repository\DTS_Jun_07.dts" /N "' + @dts + '" /W "0" /U "esp_srm" /P "hunger8u" '-- Specify global variables values to be passed to DTS trough DTSRUNset @cmd = @cmd + '/A Trial_ID:8="' + @g_Trial_ID + '" 'set @cmd = @cmd + '/A CollVer_StuCore:8="' + @g_CollVer_StuCore + '" 'set @cmd = @cmd + '/A CollVer_EnrlAttnd:8="' + @g_CollVer_EnrlAttnd + '" 'set @cmd = @cmd + '/A Reporting_District:8="' + @g_Reporting_District + '" 'set @cmd = @cmd + '/A Current_Year:8="' + @g_Year + '" 'set @cmd = @cmd + '/A UserID:8="' + @g_UserID + '" '-- Create jobexec msdb.dbo.sp_add_job @job_name = @jname, @enabled = 1, @category_name = 'DATA LOADS', @delete_level = 1, @job_id = @jid OUTPUTexec msdb.dbo.sp_add_jobserver @job_id = @jid, @server_name = '(local)'exec msdb.dbo.sp_add_jobstep @job_id = @jid, --@job_name = 'Test', @step_name = 'Execute DTS', @subsystem = 'CMDEXEC', @command = @cmd-- Start jobexec msdb.dbo.sp_start_job @job_id = @jidEND Fri, 30 Oct 2009 23:48:35 GMTmahesh.vsphttp://www.sqlservercentral.com/Forums/Topic803726-359-1.aspxHi,I am trying to create a new login like this: CREATE LOGIN [AAA\Sales] FROM WINDOWS WITH DEFAULT_DATABASE=[SalesINt], DEFAULT_LANGUAGE=[us_english]and I am getting this error:The server principal 'AAA\Sales' already exists.This login doesn't currently exist. AAA\Sales is a active directory distribution group. When I try to do the same thing on a different server, it works.How I can create this user on my production database?Thanks,Thu, 15 Oct 2009 13:43:52 GMTRem-487422http://www.sqlservercentral.com/Forums/Topic808889-359-1.aspxHi all,Is it possible to give access to the DDL of the functions, procedures, tables, etc, but don't give the right to change any of that?I've seen I can create a database role where I can go one by one and select "view definition" but I really don't want to handle all the changes on these procedures, I got over a thousand to check.Is there a role, or any way I can do this in bulk?Thanks in advance,Mon, 26 Oct 2009 11:58:25 GMTJ-F Bergeronhttp://www.sqlservercentral.com/Forums/Topic811665-359-1.aspxWhen I transferred my logins from one Sql 2005 to another, I failed to change a setting in the script & now all my logins have "Enforce Password Policy" on. Any way to Alter all Logins to change this setting to off/unchecked.Fri, 30 Oct 2009 09:09:27 GMTjude-32296http://www.sqlservercentral.com/Forums/Topic361464-359-1.aspxim trying to drop all certificates on my database, and then the master key, but cannot do this as there are objects encrypted by one particular cert that i called fcert. I done this ages ago and cannot remember what i encrypted with this cert. to drop the master key i have to drop the cert and to drop the cert i have to make sure no objects are encrypted by it. how is this achieved?Fri, 27 Apr 2007 04:24:00 GMTwinston Smithhttp://www.sqlservercentral.com/Forums/Topic809926-359-1.aspxHi,I have an application which connects to a SQL Server database using ADO/oledb with a connection string set to either Integrated Security or username/password. A client wants all users to connect with a single windows user account rather than granting each user access, and does not want to use the SQL Server username/password (don't ask).Anyone know whether this is possible?I thought using keymgr would work (after seeing post http://www.sqlservercentral.com/Forums/Topic754464-359-1.aspx), but it continues to connect as me rather then an alternative that I specify.Thanks,DavidWed, 28 Oct 2009 07:37:45 GMTdcain-863955http://www.sqlservercentral.com/Forums/Topic809309-359-1.aspxHi all,Maybe a stupid question for you experts, but I'm not much into security, so ... Is it possible, into SQL2005, to grant read access rights to a given user to one view only in a given DB?Thanks for your answerppTue, 27 Oct 2009 07:48:01 GMTrot-717018