InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 01:21:57 GMT20http://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/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/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/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/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/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/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/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-717018http://www.sqlservercentral.com/Forums/Topic810072-359-1.aspxHi Sorry, I need to post this as well 'cause I can't find how to relate a login to a user.What I know ...A person can log into the SQL Server thru a login with "SQL Server authentication".He then can query the databases without a problem.I can create a user whom I grant permissions on some databases, tables but how the hall can I tell SQL Server to use this user when somebody logs in with a specific user.If anybody out there knows the answer please tell me ... the few hairs remaining on my head will be grateful.:-)Wed, 28 Oct 2009 09:46:03 GMTrot-717018http://www.sqlservercentral.com/Forums/Topic810178-359-1.aspxI was just wondering if anyone had any helpful information about how i wouldmigrate all databases in a database instance(sql2005) on one server to an instance on another server(sql2005).How would i go about migrating security, jobs and stored procedures ?The machine build and database settings are the same except for more memory and processing speed.If anyone has good info or links on this topic, i would appreciate it alot.ThanksWed, 28 Oct 2009 11:48:04 GMTsickjinnhttp://www.sqlservercentral.com/Forums/Topic577377-359-1.aspxI have two instances of SQL Server 2005 version 9.00.3042 on a windows 2003 Enterprise Edition server service pack 1The security Update detect both of the instances wherein one instance gets done but other gets failed.Any idea!!Sun, 28 Sep 2008 02:27:48 GMTsantosh_chandelhttp://www.sqlservercentral.com/Forums/Topic810074-359-1.aspxI have a request from my Windows Security department. We do use windows domain accounts to start the SQL services. After the SQL 2005 Installation is done, these accounts are set up with file/folder permissions and various User rights in the policies. This is performed by the SQL installation by creating local SQL groups and then adding the startup account to the groups.The domain accounts that start the sql service are set up in domain groups by our security department. On a non-clustered install the domain groups are not used. SQL creates its own local groups and sets up the permissions on the local groups.What I am being asked to do it to go through the places where the SQL installer set up the service account and to delete the service account and replace it with the Domain Group name which contains the account. So the local groups on the server will contain domain groups rather than the service accounts and the local rights will only contain the domain groups wherever a service account would have been. I am very hesitant to do this.Does anyone have any opinions on this pro or con? Has anyone ever successfully tried this.Wed, 28 Oct 2009 09:46:34 GMTtavery_scigajhttp://www.sqlservercentral.com/Forums/Topic809928-359-1.aspxI have SQL server 2008, I try to change the default security setting, more exactely the "roles assignements" when a new user is settingmay be there is special script or something like that to do, so can you help me pleaseIlyasWed, 28 Oct 2009 07:38:47 GMTlyeslinkhttp://www.sqlservercentral.com/Forums/Topic808978-359-1.aspxHello everyone,I've been a subscriber of the SQL Server Central newsletters for years, but this is my first post to this forum. I'm stuck on this one and Google is less than helpful. My sincere apologies if this is in the wrong forum.I have been given the awesome task of providing information via ODBC to an outside organization. We are running SQL Server 2k5 on Windows Server 2k3. We decided that rather than give the external user access to the main database server that we should set up another instance (call it "Instance2") of SQL 2k5 and copy only the necessary data to a database on the new instance, then give the external organization access to the new instance. This gives us the added flexibility of being able to encrypt the data going over the wire, as well, without taking a performance hit on our main SQL server.I successfully set up the new instance on SQL1, but our network admin decided that the load was high on SQL1 and since we have another server, SQL2 that is underutilized we should put the new DB over there. I have the new Instance2 installed on SQL2, however, I have hit a snag that has me completely confused. On SQL1, under SQL Server 2005 Network Configuration->Protocols for Instance2->Properties, on the Flags tab, I have two flag names and two values: ForceEncryption is set to "Yes" and "Hide Instance" is set to "No." On SQL2, at the same point, I have two values for the flags, but the flag names are blank! Since the flag names are blank, I cannot get encryption to work. The data must be encrypted to go over the wire, we don't have a choice in this matter. How do I get the flag names back into this tab?Jerome GrimmerIllinois workNetMon, 26 Oct 2009 14:31:13 GMTJerome Grimmerhttp://www.sqlservercentral.com/Forums/Topic808580-359-1.aspxHi ,When try to access or create a file in shared UNC path with full rights, I m getting access denied message.I supposed to give a command in Query analyser like EXEC xp_cmdshell '@ECHO hi! > \\myservername\sharedlocation\hi.txt'But I m getting the access denied message. I have enabled Xp_Cmdshell in server area configuration too.Kindly guide me how to proceed.ThanksRangaMon, 26 Oct 2009 04:57:53 GMTRanga_CDShttp://www.sqlservercentral.com/Forums/Topic808694-359-1.aspxHi,I have a SQL Server 2005 Express Edition installed on a PC, with our homemade production control software. We need a very secure environment, no one should ever access the database except us. How can I denied the current Windows user to load up SSMS and view the database? The PC will of course be login, for our software to run the production line, but the only connection to the database should be the software or an administrator from our company.How can I do that?thanks a lot for your time and helpMon, 26 Oct 2009 07:45:54 GMTDominic Gagnéhttp://www.sqlservercentral.com/Forums/Topic808563-359-1.aspxHi Guys,I am sitting with a situation. How do you limit a user on a database to only select/update/insert etc from a few tables within on the SQL instance. I have 2 schemas on my SQL instance with tables belonging to them and would like to have db_owner rights only on certain tables. I have db_owner rights on both logins but would not want the logins to see other see tables on select from it. Can anyone perhaps help.RegardsICMon, 26 Oct 2009 04:03:42 GMTImke Cronjehttp://www.sqlservercentral.com/Forums/Topic808805-359-1.aspxUnder a database a user has permissions: read permissions (checked and grayed out. I cannot uncheck read).I tried dropping the user only to get an error message that they own a schema. I found under db_datareader that user is the owner. I reset it to dbo and deleted the user. I added the user back in and immediately they take control of that schema again.What's going on?Mon, 26 Oct 2009 09:53:43 GMTSailorhttp://www.sqlservercentral.com/Forums/Topic808442-359-1.aspxHi,I have this sql server 2005 and I started encountering some permission issues e.g.for a stored procedure the execute permission is set explictly, it is in that list but not showeing in the effective permissions. And that user it cannot execute the procedure ...Noticed that there is no public server role showing in the server!! How can that be possible?Thank youAdrian Sun, 25 Oct 2009 20:38:21 GMTinetusehttp://www.sqlservercentral.com/Forums/Topic754464-359-1.aspxWe are experiencing something very bizarre with one of our developers. They connect to one of our servers through windows authentication (domain\username) with SSMS. They open a new query window. In the status bar at the bottom of the window, the connected user is displayed as the administrator account on the database server (server\adminacct). Sysprocesses show them as connecting as the wrong account as well, so it's not just a display issue.When connecting to other database servers, there are no issues.There are also no issues when they Remote Desktop into the server with windows authentication and run Management Studio with windows authentication.We had the same issue several months ago with a different developer. Unfortunately no one can remember how it was fixed.Any help is appreciated.Thu, 16 Jul 2009 13:40:20 GMTrubeshttp://www.sqlservercentral.com/Forums/Topic806518-359-1.aspxIf 2 users are in a group and the group was given ddl_admin previlages on the database, can a user1 modify table created by user2 ?Wed, 21 Oct 2009 08:07:55 GMTTara-1044200