InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 22 Mar 2010 04:55:07 GMT20http://www.sqlservercentral.com/Forums/Topic887188-359-1.aspxCan anyone post links to an implementation method for SSL and SQL Server 2005 that worked?Does anyone know how much effort a developer would need to put in to modify their application to use SSL?ThanksMon, 22 Mar 2010 01:20:59 GMTGraham@SQLServerhttp://www.sqlservercentral.com/Forums/Topic884831-359-1.aspxHiI trying to transfer a login my development database to production database.I want to actually copy all the securables to production database.Wed, 17 Mar 2010 09:54:25 GMTsrikantht.devhttp://www.sqlservercentral.com/Forums/Topic886424-359-1.aspxI need T-SQL script to list all users and their permission for all databases of a server?Fri, 19 Mar 2010 08:35:39 GMTrpatil22http://www.sqlservercentral.com/Forums/Topic886096-359-1.aspxHi,I am currently logged into the SQL Server 2005 (Integrated Windows Authentication) via the SQL 2005 Management Studio. I would like to find out what is the command using which I can find out what Role / permissions have been granted to my Windows NT login. I want to find it out through SQL Query commands (programmatically) and not via GUI like expanding the Security Section, expanding Logins, expanding the Server Roles section etc. Thanks in AdvanceFri, 19 Mar 2010 00:24:11 GMTmsforumposthttp://www.sqlservercentral.com/Forums/Topic884534-359-1.aspxmy stored procedure was work then suddenly the following error appeared *** LoadFromSQLServer failedOLE Automation Error Information HRESULT: 0x80040e4d sp_OAGetErrorInfo failed.the code I use is [code="sql"]create proc sp_displayoaerrorinfo@object as int asDeclare @hr intDECLARE @output varchar(255)DECLARE @source varchar(255)DECLARE @description varchar(255)PRINT 'OLE Automation Error Information'EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUTIF @hr = 0BEGINSELECT @output = ' Source: ' + @sourcePRINT @outputSELECT @output = ' Description: ' + @descriptionPRINT @outputENDELSEBEGINPRINT ' sp_OAGetErrorInfo failed.'RETURNENDGOcreate proc sp_displaypkgerrors@pkg as int asdeclare @numsteps intdeclare @steps intdeclare @step intdeclare @stepresult intdeclare @pkgresult intdeclare @hr intselect @pkgresult = 0EXEC @hr = sp_OAGetProperty @pkg, 'Steps', @steps OUTPUTIF @hr <> 0BEGINprint 'Unable to get steps'EXEC sp_displayoaerrorinfo @pkg --, @hrRETURNENDEXEC @hr = sp_OAGetProperty @steps, 'Count', @numsteps OUTPUTIF @hr <> 0BEGINprint 'Unable to get number of steps'EXEC sp_displayoaerrorinfo @steps --, @hrRETURNENDwhile @numsteps > 0 Begin EXEC @hr = sp_OAGetProperty @steps, 'Item', @step OUTPUT, @numstepsIF @hr <> 0BEGINprint 'Unable to get step'EXEC sp_displayoaerrorinfo @steps --, @hrRETURNENDEXEC @hr = sp_OAGetProperty @step, 'ExecutionResult', @stepresult OUTPUTIF @hr <> 0BEGINprint 'Unable to get ExecutionResult'EXEC sp_displayoaerrorinfo @step --, @hrRETURNENDselect @numsteps = @numsteps - 1select @pkgresult = @pkgresult + @stepresultendif @pkgresult > 0print 'Package had ' + cast(@pkgresult as varchar) + ' failed step(s)'else print 'Packge Succeeded'GOcreate proc sp_executepackage@packagename varchar(255), --package name, gets most recent version@userpwd varchar(255) = Null, --login pwd@intsecurity bit = 0, --use non-zero to indicate integrated security@pkgPwd varchar(255) = '' --package passwordasdeclare @hr intdeclare @object int--create a package object EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT if @hr <> 0BeginEXEC sp_displayoaerrorinfo @object --, @hrRETURNend --load the package (ADD integrated security support)declare @svr varchar(15)declare @login varchar(15)select @login = SUSER_NAME()select @svr = HOST_NAME()declare @flag intselect @flag = 0if @intsecurity = 0 if @userpwd = NullEXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd-- ServerName As String, [ServerUserName As String], [ServerPassword As String], [Flags As DTSSQLServerStorageFlags = DTSSQLStgFlag_Default], [PackagePassword As String], [PackageGuid As String], [PackageVersionGuid As String], [PackageName As String], [pVarPersistStgOfHost])elseEXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwd, @ServerPassword = @userpwdelsebeginselect @flag = 256EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @PackageName=@packagename, @Flags=@flag, @PackagePassword = @pkgPwdendIF @hr <> 0BEGINprint 'LoadFromSQLServer failed'EXEC sp_displayoaerrorinfo @object --, @hrRETURNEND--execute itEXEC @hr = sp_OAMethod @object, 'Execute'IF @hr <> 0BEGINprint 'Execute failed'EXEC sp_displayoaerrorinfo @object --, @hrRETURNEND--return the step errors as a recordsetexec sp_displaypkgerrors @object-- unitialize the packageEXEC @hr = sp_OAMethod @object, 'UnInitialize'IF @hr <> 0BEGINprint 'UnInitialize failed'EXEC sp_displayoaerrorinfo @object --, @hrRETURNEND-- release the package objectEXEC @hr = sp_OADestroy @objectif @hr <> 0BeginEXEC sp_displayoaerrorinfo @object --, @hrRETURNend GO [/code]any suggestio pleaseWed, 17 Mar 2010 04:47:01 GMTali.mahmoud.habibhttp://www.sqlservercentral.com/Forums/Topic884910-359-1.aspxMy situation is a funny one in that I release develoeprs scripts to the test and prod servers, but those developers still do have access to those servers for fault finding purposes.I now need to give developers specific permissions based on an audit requirement rather than the blanket "ok for most things" I have given them.What is the best practise for me to provide such a security model? I can't be going in and setting permissions on a role per object at this stage in the servers life as there are thousands of objects.Any advice?ThanksShark :hehe:Wed, 17 Mar 2010 11:23:55 GMTShark Energyhttp://www.sqlservercentral.com/Forums/Topic884060-359-1.aspxI am a greenhorn in the ways of encryption. I have need to encrypt a column in a table on database1 and have it be able to be decrypted and read on database2. I have managed to set up the MASTER KEY, CERTIFICATE and SYMMETRIC KEY on database1 and the encryption seems to query just fine ON the database. Unfortunately, I need to be able to read this encrypted column from another database on the same server. Would someone be so kind as to tell me how I go about achieving this decryption. All help would be greatly appreciated.Thanks!DaveTue, 16 Mar 2010 12:01:23 GMTdaveFromIllhttp://www.sqlservercentral.com/Forums/Topic880918-359-1.aspxHi,For stored procedures that access several databases, I can sign the proc with a certificate and grant execute permissions on the proc and it works. Is there a way to do this for procs that use linked servers?Thanks.Thu, 11 Mar 2010 06:00:35 GMTJWOLhttp://www.sqlservercentral.com/Forums/Topic879885-359-1.aspxHi, I'm posting this for a colleague who is struggling with a trigger that won't fire off an insert statement.The DML trigger is fired from a specific database in server A. The trigger selects data from a table in a linked server, Server B, in order to get some of its information, so we know that the servers are successfully linked and Server A is able to select data from Server B.However, later in the trigger, Server A sends an Insert statement to Server B to insert data into one of its tables, and this insert fails.My best guess is that whatever permissions the trigger is executing with from the one server are not allowing inserts into the table on the other server.I am assuming triggers are fired off under the table owner or some pre-defined principal role, but I can't find any documentation on what this might be. Can anyone point me in a direction on where to look this info up, or give me any advice on how to troubleshoot the permissions actioning an insert from a trigger on one server into a table sitting on another server?We are using Windows Authentication, and I wouldn't doubt that this is also playing some role in the problem. Even if we add an "execute as" to the trigger, we still have no joy.Tue, 09 Mar 2010 20:17:11 GMTsharon.benderhttp://www.sqlservercentral.com/Forums/Topic876899-359-1.aspxWe have Server 1 - 3 databases db1,db2,db3 - having same table , say table1,table2,table3,table4,table5,table6....table100Server 2 - 4 databases db1,db2,db3,db4 - having same table , say table1,table2,table3,table4,table5,table6.....table50We want to give select,update permission for db1, db2 on table table1,table2....table50 on server1We want to give select,delete permission for db1, db4 on table table1,table2....table30 on server2Does anyone has script?Thu, 04 Mar 2010 09:09:06 GMTrpatil22http://www.sqlservercentral.com/Forums/Topic882418-359-1.aspxHi folks. I'm facing this problem in a 2-node production SQL cluster from one of the customers of the company I work for.It all began when the SSIS packages started failing, I checked the job history and the error message was "Logon failure: unknown user name or bad password".After we checked the account was fine, with the right permissions, we tried to recreate the credential based on that login to run the SSIS packages. But when we would work with proxy accounts, credentials and stuff like that, we were getting "Decryption error has occurred" errors.I figured there was issues with the Service Master Key, so I moved SQL group to the other node and all the jobs started working again!.In node 1 the jobs failed, and in node 2 the jobs worked. So I figured that the Service Master Key in node 1 was different from node 2.I read some posts in the internet and they all said to backup the SMK in the working node and restore it in the bad node.We scheduled a change to do that. I backed up the SMK in node 2 (good one) normally. But when I'd restore it in node 1, I got this error "The current service master key could not be decrypted...". I thought, it's okay, the SMK is wrong, I'll have to use forced restore to overrride the current SMK and replace it with the good one. I did that, restarted SQL Server, and the jobs started working again on node 1! (the one that was bad). I thought "issue solved, let's go to bed".We then moved SQL to node 2 (the one that was working), to see if both nodes were good, and for our surprise, the jobs started failing in node 2.I tried to restore the SMK on node 2 (where the backup came from in the first place), and I got the "current service master key could not be decrypted"! I thought how could this happen? I can't restore the SMK in node where I took the backup from?! WTH?So it seems when I did the forced restore in node 1, it created a SMK that was no longer valid in node 2?So to finish I did a forced restore on node 2 and we were back at the begininng (node 2 working and node 1 failing) and finished the change (not successfull).How can I get the credentials working on both nodes without any decryption errors? That is to say, how can I synchronize the SMK between both nodes? Any help would be greatly appreciated. Thanks.Sat, 13 Mar 2010 13:28:52 GMTrafavethttp://www.sqlservercentral.com/Forums/Topic881888-359-1.aspxI am in the middle of a project that is going to require us to use encryption for the first time to secure some sensitive data. I am having trouble finding articles/blogs/etc illustrating the pros and cons of using database encryption vs. application encryption. I guess with my limited knowledge (still in the process of reading a number of documents) it almost seems as though application level encryption would be more secure because the data would still be secured if the database server was compromised. Any info is much appreciated.Thanks,SamFri, 12 Mar 2010 09:11:12 GMTssb-402814http://www.sqlservercentral.com/Forums/Topic851153-359-1.aspxI would like the ability to give new contractors read-only access to an SQl Server so that they can look at all the configurations/settings, databases, tables, diagrams, data, etc., but cannot change anything.It would be nice if there were a fixed server role of "sysguest" or something that did this, but I don't think there is.The way I plan to do it is to grant the contractor's login access to all databases on the server and assign the associated user the "db_datareader" role in all databases. Do you think this is the best way of accomplishing this?Cheers,SteveThu, 21 Jan 2010 04:57:13 GMTxnl28-574517http://www.sqlservercentral.com/Forums/Topic878753-359-1.aspxWe are currently renaming all of our domain users (they are still in the same domain)The problem is that after renaming a user SYSTEM_USER still returns the old name. It will give the new (proper) name after restarting the sql-service.Are there any less drastic ways of forcing sql server to update it's usernames? ;-)Many thanks in advance!Mon, 08 Mar 2010 10:20:10 GMTbjorn.hildehttp://www.sqlservercentral.com/Forums/Topic879430-359-1.aspxSSMS, Query Analyzer, Object Explorer - we use multiple SQL logins but would always like it to default to one specific one.So each time the developer would open a new session in SSMS or Query, it would default to say 'DevSU' as the SQL login even if the user last used a 'DevUser' login.The two logins are specific to what the developer is doing at the time, but I would always like it to default to 'DevUser'.Can't find where I can put that in the SSMS options or the registry and if the registry would I have to have that done per user installation.Inquiring minds want to know.Tue, 09 Mar 2010 08:40:38 GMTMelissa.Fischerhttp://www.sqlservercentral.com/Forums/Topic876557-359-1.aspxnewbie to 2005. converting fairly generic, open db from 2000.have a user sp that generates a filename (based on a passed in string and date/time (eg 'bc20100303114512') Asi recall 2000 needed an owner before the table could be created. Enter 'theSQLUser'. that user is a standard/public sql user with no more rights than public and is a user of the db.the following statement:select pid,last from clients into theSQLUser.bc20100303114512 where pid='000001'worked fine in 2000but yields: The specified schema name "theSQLUser" either does not exist or you do not have permission to use it. Now i never created a schema for the user, but because other efforts I've deleted the user and recreated it. There was at least one time that, when i dropped the user via vs2005 and the delete key, it asked if i wanted to lose two objects: the schema & user. Not knowing any better I did both. It no longer asks me about a schema when i want to drop the user.Do I need that schema? CAn it be recreated? what was in it - i didn't create it? Is its absence part of my problem? 2000 never complained.thanks for any insightsWed, 03 Mar 2010 22:41:15 GMTmountcrumpithttp://www.sqlservercentral.com/Forums/Topic877185-359-1.aspxI've installed an SSL certificate on a SQL Server and am trying to encrypt traffic from a web server to it.After the certificate was installed, we changed the connection setting on the web end to ForceEncryption but we get these messages:Microsoft SQL Server ODBC Driver Version 06.00.6002Running connectivity tests...Attempting connection[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security errorDisconnecting from serverTESTS FAILED!I'm stumped... does anyone have any ideas?Thank you.RogerThu, 04 Mar 2010 14:33:10 GMTRoger Abramhttp://www.sqlservercentral.com/Forums/Topic425871-359-1.aspxWhat could be the best solution for data level encryptionMon, 26 Nov 2007 10:03:45 GMTRamana-561712http://www.sqlservercentral.com/Forums/Topic871950-359-1.aspxFor many years various people would copy a backup of a production database to a development server for restore/testing. For many reasons there is talk of either ending that practice or requiring the dev sql servers to meet production security standards. This will start a big fight, but I'm wondering how the community sees this and how you handle it.Making the data in the prod copy anonymous would be a huge effort. There is a method for creating an empty copy of a prod database but that is useless for performance testing.Wed, 24 Feb 2010 08:30:30 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic876376-359-1.aspxHi. I thought I would tighten security by removing BUILTIN\ADMINISTRATORS from the sysadmin role. I connected to SQL using Windows Authentictication, the account used is a domain account and is a member of sysadmins.After removing BUILTIN\ADMINISTRATORS from sysadmins I was unable to select other logins. I disconnected and reconnected. The only logins visible were BUILTIN\ADMINISTRATORS and sa?Any ideas?Thanks,Phil.Wed, 03 Mar 2010 14:44:26 GMT2Tallhttp://www.sqlservercentral.com/Forums/Topic874416-359-1.aspxHi guys, Apologies if this has been posted before, however my serarch did not come up with anything.Simple issue really, we have a lady who was recently married and needs her SQL login changed from *DOMAIN_NAME*\gallan to *DOMAIN_NAME*\gshepherdI have looked into the ALTER LOGIN command however, it does not seem to like me renaming the login if there is a backslash in the name.I have also tried when i leave out the *DOMAIN_NAME*\ part with no luck.Obviously i have missed something obvious here! Would someone be able to advise??Cheers, SamMon, 01 Mar 2010 07:41:29 GMTSam -480699http://www.sqlservercentral.com/Forums/Topic875247-359-1.aspxI am planning to implement SQL Server 2005 Encryption in our environment for the first time. I did lot of research on all the methodoligies. But I still have some questions that are unanswered.I am planning to use Symmetric Key Encryption to protect data. I have read that we can again protect symmetric key with either another symmetric key, asymmetric key, certificate or password. If I choose to encrypt it with another symmetric key or password, I am deviating from the encyption hierarchy, right? or can I still protect them from Service Master Key and Database Master Key?If I plan to protect underlying keys with Service Master Key and Database Master Key, I should definitely go with Symmetric key encrypted by certificate or asymmetric key, right?ThanksTue, 02 Mar 2010 09:04:06 GMTsan43http://www.sqlservercentral.com/Forums/Topic874780-359-1.aspxhi, i need to give a User access to just VIEW the properties of a Linked Server. I've tried giving running "GRANT ALTER ANY LINKED SERVER TO [USERID]" but that didn't work. It's fine if they can view/alter the Linked Servers, but I don't want them to touch any of our Databases. I've had to give the UserID, SYSADMIN privileges in order to do so, but now, they've got all the power in the world. Is there a way, to grant a user access to just view/alter Linked Servers and not affect databases? thanks!Mon, 01 Mar 2010 16:14:55 GMTkaotic504http://www.sqlservercentral.com/Forums/Topic874352-359-1.aspxHi,I've written stored procedures to retrieve data from a MySql database on our hosted website to a SQL Server 2005 db on our lan. I plan to run the data retrieval probably daily using SQL Server's scheduler. Can anyone tell me what I need to do to make the transfer of data as secure as possible? Both the webserver and the corporate server have firewalls, so I'm concerned mainly with how to secure the data as it's passed across the public domain between the two firewalled machines.Thanks.LouiseMon, 01 Mar 2010 03:23:53 GMTLouise Boultonhttp://www.sqlservercentral.com/Forums/Topic873332-359-1.aspxHi, Today one the server ,i logged in with DB\xxx login.but when i execute select suser_name() , i got different result ( DB\yyy).how it can happened ?IS it some thing to do with Active Directory ?Fri, 26 Feb 2010 05:06:34 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic745531-359-1.aspxFor auditing purposes, we need to produce a monthly report showing any logins on our production database, along with permissions to any objects they have been granted.We've used sp_helplogins to produce this information, but we wondered if there is a more comprehensive system stored procedure for this purpose, or if anyone has a custom script they use for retrieving login information?Thanks,KayWed, 01 Jul 2009 09:34:29 GMTkwilthttp://www.sqlservercentral.com/Forums/Topic872399-359-1.aspxI have successfully encrypted the data on few columns in one table for testing. I have used Symmetric Key with Certificate..Create Master Key Encryption By Password = 'XXXX'Create Certificate MyCertificate Encryption by password = 'zzzz'with subject = 'yyyy'Create Symmetric Key MySymmetricKeyWith Algorithm = Triple_des Encryption By Certificate MyCertificate-- Open Symmetric Key Open Symmetric Key MySymmetricKeyDecryption By Certificate MyCertificate with password = 'zzzz'Then I created a view for users to see the data by decrypting it asConvert( nvarchar(60), DecryptByKey(encrypt_col)) decrypt_colIn order to view the rows, users must open the symmetric key.. What I want is, user just query the view without opening the symmetric key.. How can I avoid opening the key by creating a view, that include the open as well?Or How can I run open command for the users every time when they log in, so that view works just fine..?Without opening the symmetric key, results are just null.Any help is highly appreciated.-ShihabWed, 24 Feb 2010 17:46:50 GMTsihaabhttp://www.sqlservercentral.com/Forums/Topic871822-359-1.aspxI want to create domain logins for almost 10 users but i want to use script for this task. All users should get admins rights on all the databases. Could anyone please provide me this script.Thanks a ton in advance.NitinWed, 24 Feb 2010 04:31:34 GMTnitinuniyalhttp://www.sqlservercentral.com/Forums/Topic872129-359-1.aspxwe have a requirement to connect to a custom SQL 2000 instance on a server that is running in workgroup mode and doesn't support mixed mode authentication. is there any way to send the credentials through SSIS to connect?Wed, 24 Feb 2010 11:44:26 GMTSQL Noobhttp://www.sqlservercentral.com/Forums/Topic871204-359-1.aspxI'm looking for any kind for any kind of guidelines in designing a SSRS 2005 deployment to be made available outside the LAN/WAN/FIREWALL, thanks...Tue, 23 Feb 2010 09:01:48 GMTRRIOS42http://www.sqlservercentral.com/Forums/Topic872052-359-1.aspxHi,We are having connection failures with:-The Security System detected an authentication error for theserver cifs/servername. The failure code from authentication protocol Kerberoswas "The time at the Primary Domain Controller is different than the time at the Backup Domain Controller or member server by too large an amount.I thought that Kerberos was meant to allow up to 5 minutes time difference - the difference is less than 100 milliseconds.We also get:-Event Type: WarningEvent Source: W32TimeEvent Category: NoneEvent ID: 50Date: 24/02/2010Time: 08:51:49User: N/AComputer: ServerNameDescription:The time service detected a time difference of greater than 128 milliseconds for 90 seconds. The time difference might be caused by synchronization with low-accuracy time sources or by suboptimal network conditions. The time service is no longer synchronized and cannot provide the time to other clients or update the system clock. When a valid time stamp is received from a time service provider, the time service will correct itself. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.Anybody got any ideas please.Thanks.Wed, 24 Feb 2010 10:29:49 GMTjasonmorrishttp://www.sqlservercentral.com/Forums/Topic871547-359-1.aspxDoes anyone know of a SQL 2005 version of the SQL Audit project Microsoft put together?[url]http://sqlcat.com/whitepapers/archive/2008/11/15/reaching-compliance-sql-server-2008-compliance-guide.aspx[/url][url]http://download.microsoft.com/download/e/d/3/ed3cdde1-0ffc-4abc-8bc2-d62f206d1a3d/SQLComplianceLab.exe[/url]Thanks in advanced,ScottTue, 23 Feb 2010 16:09:49 GMTscoinvahttp://www.sqlservercentral.com/Forums/Topic428893-359-1.aspxHi, I have a couple of questions regarding permissions and security.First, I have read many times, that a wise practice is to give your permissions through stored procedures, in other words, give execute permissions on stored procedures, and limit or do not allow direct access to tables and other objects.How would I give a user or a role(and then add the user to the role) permission to execute all stored procedures, preferably a blanket statement(GRANT EXECUTE ANY... not a script I have to run everytime I create a new stored procedure)?Second,And here is the rub, shouldn't that user/role be able to drop a table/delete records/ perform any functionality if it is within the stored procedure they have execute rights on? With the exception of dynamic sql, which I understand.Thanks for anyone's input in advance.Mon, 03 Dec 2007 11:31:13 GMTJonathan Leahyhttp://www.sqlservercentral.com/Forums/Topic870600-359-1.aspxI have a situation in which a SQL login needs read permission on dozens of databases. Ideally, I could create a server role. Then I could add the login to that role, and give the role all the database permissions. This is similar to how I handle Windows AD groups & users. I can create a database role in each database and add the sql login to each db role, but hoping for a better idea.Thoughts ?Mon, 22 Feb 2010 11:56:24 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic868949-359-1.aspxi have a sql authenticated login "event_test". i transfer this login from one Sql server to another.but when i tried to login in to destination server . i got login failed errorscript used to migrate [code="sql"]CREATE LOGIN [event_test] WITH PASSWORD=N'´ÂjPOjß³SÂàæ´L}\J€GÞ)=Û&¥Rڒ', DEFAULT_DATABASE=[tempdb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOEXEC sys.sp_addsrvrolemember @loginame = N'event_test', @rolename = N'sysadmin'GOALTER LOGIN [event_test] DISABLE[/code]why its happening ?And i dont want to reset it on dest. server then what should i do ? Fri, 19 Feb 2010 05:02:36 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic868435-359-1.aspx[font="Comic Sans MS"]Dear All,I am working on an audit requirement across an enterprise system – this consist of SQL Server versions 2000/2005/2008. The general practice is to grant access to user through fixed database role. However - there are some deviations like:1) A user-defined role is created on one of the database and specific grant has been given to selected objects. Example would be execute permission on 5 stored procedures + select permission on 10 tables + delete permission on 3 tables.2) A user has been granted direct access to few objects (like above) but not through a user defined role.Is there a way to list down the details of (1) and (2)That is:(1) User Defined Rolename -> Object Name (s) -> what permission on the object(2) Userid -> Object Name (s) -> what permission on the objectThe idea is to filter the user who has some ‘special’ access and review the user defined roles.Any help on this would be highly appreciated.[/font]Thu, 18 Feb 2010 12:44:43 GMTsabyasmhttp://www.sqlservercentral.com/Forums/Topic868966-359-1.aspxHow can i create a link server from sql 2005 to sql 2005 by mapping a Windows login from serverA to SQL Login from ServerB ?when i tried i get an error[quote]Error 7416 - Access to the remote server is denied because no login-mapping exists[/quote]Here is what i did...1. create link server with network name of sql server2.server type = SQL SERVER3. Security tab > Local Login = Windows Login, Remote Login = remote SQL Login, Remote sql pswd given4.selecting the check box " Not To be Made "Fri, 19 Feb 2010 05:23:11 GMTTara-1044200http://www.sqlservercentral.com/Forums/Topic867720-359-1.aspxHi, I have SQL server 2005 installed on my machine. I am able to log on to server using windows authentication, but when I try to use SQL server authentication, using user as 'sa' and the default paswrd, It gives me error as "Login failed for user 'Sa'. This user is not associated with a trusted SQL connection.How do I know passwrd for user sa.Thanks in advance.Wed, 17 Feb 2010 22:08:21 GMTruchi-761283http://www.sqlservercentral.com/Forums/Topic641210-359-1.aspxI've inherited a number of SQL servers. There appear to be a number of users who are no longer with the company and therefore should never be logging in. However, I'm never sure if there's an app on our network with embedded credentials. Is there any way to know that a user account has not been used for a long time?ThanksWed, 21 Jan 2009 15:32:16 GMTDoubleExhttp://www.sqlservercentral.com/Forums/Topic867900-359-1.aspxHi All,how to identify whether my sa account is disabled in sql server. Is there any system sp or anything?Thanks,DRThu, 18 Feb 2010 04:45:13 GMTdraj108