SQLServerCentral / SQL Server 7,2000 / Security

Anonymous access in SQL RS 2008

vasamsetti_prasad — Tue, 20 Oct 2009 00:43:16 GMT

Hi In SSRS 2008 their is no IIS required. So anonymous access is not possible by configuring IIS.I find the way to do that in SSRS 2008 .I am sharing this so that others can use it.I am attaching the word document having steps to be followed and one more file namemicrosoft.samples.reportingservices.anonymoussecurity.txt . Please rename the extension to .dll file and use this dll file as per the steps given in word document.Thanks and Regards,Prasad.

Lock time out errors jdbc was 7 on Windows 2008 Server

iamdjm — Fri, 24 Feb 2012 06:12:25 GMT

Experiencing lock timeout error when accessing a SQL server 2005 running on 2008 server. J2ee application running on Websphere 7 also on Windows Server 2008.Any cautions for 2008? Or connection pool settings? Java process on Websphere spikes and needs to be restarted. application has been running for years without similar issues.ThanksDan

AD and SQL 2K

schleep — Mon, 19 Oct 2009 14:04:46 GMT

Hello,I am seeing some strange behaviour. We use AD to manage users, groups, and SQL Server access.We have various levels of access, supervisor and administrator, among others.(These are in-house privs, not OS/SQL).We use the following SELECT Name AS cName FROM OPENROWSET(NetGroupGetMembers, @cGroupNameArg)) to get the list of members for a given AD group.Now, in each of the AD console, SQL QA xp_enumgroups, and a seperate app, I can see with my own eyes the groups CIPS\Imp_Prod_TCNW_Administrator_6, CIPS\Imp_Prod_TCNW_Supervisor_6, and the members of each group. In SQL QA, NetGroupGetMembers returns the members of the Admin group, but none from the Sup group.This is not a case of slow AD replication, it's been days since the groups were populated. Although if there's a way to force AD replication, can you let me know how?Other suggestions? The SQL Servers are SP2. The DCs may be Win2K or Win2K3. However, if it works for 99% of the AD groups on our domain, it sholdn't be a SP thing, right?Thanks,PaulP

Add new user in SQL server 2000/2005 through script

ssa2010 — Mon, 06 Feb 2012 21:37:02 GMT

Hello friends,Is there any way to add a new db user in sql 2000/2005 through script ?If yes please provide this sample script..

How to determine SQL Security Login group for windows login when user is member of active directory security group.

Jamie Johnson-406551 — Wed, 23 May 2007 00:29:00 GMT

My users login to SQLServer through Active Directory security group memberships. These groups are security groups in SQLServer and used to assign permissions to database objects.

My problem is that when I query system_user, current_user, suser_sname and user I get the individual user, not the group they are a member of.

So where they login in through 'DOMAIN\SecurityGroup' I get back 'DOMAIN\UserName'

When I try User_ID and user_name I get back the SQLServer role like 'dbo' and 'public'.

How can I detemine which 'DOMAIN\SecurityGroup' is being used by 'DOMAIN\UserName' to access the database.

What would cause DSN file to change database to master?

WayneS — Wed, 29 Apr 2009 08:22:29 GMT

Users access sql server via MS Access 2003, with DSN connections. In sql, there is a domain group where the default db is set to their db. Yesterday users started having problems, where they weren't seeing the tables in their database. It ends up that somehow, [u]all[/u] users have had their dsn's changes from their db to master. The group login was still pointing to their db.What would cause this?

PCI auditing/Tracing with RSA Envision.

Tom Goltl — Wed, 20 Jan 2010 08:48:30 GMT

Hello all,We are going through some auditing for PCI compliance, even though there is no credit card information in our sql databases, they are still requiring us to turn on tracing. This is with the RSA envision product. They provided us with scripts that would turn on tracing to all events except a select event, and a sql query would return the information to the envision server to be stored. On sql 2000 this functionality would require sa privileges and we are working with them to make it so we run the traces to files and sftp the files to the envision server. These events are producing 1 gig trace files every minute. We asked them to narrow the parameters of the tracing and they told us this would not be a problem if we let there process work to pull the data via odbc every 5 sec. Our thoughts are, 1 gig is 1 gig no matter how often you pull it. Anyone else work with this product and have any luck narrowing the trace need for pci.Thanks in advance.Tom

Linked Server Creation - Connect sql sever 2008 from 2000

Ramkumar.K — Thu, 23 Sep 2010 22:44:54 GMT

Hi,Since this may be concerned as a kiddish question. But this is required.I need to connect the sql server 2008 database from sql server 2000. Since the client has been using the 2000 version database and they want to pull some data from sql server 2008 database. I have tried to create the linked server for this with below T-SQL statements.[b]EXEC sp_addlinkedserver @server = 'LS',@datasrc = 'ServerName', @provider = 'SQLOLEDB', @srvproduct = '', @catalog = 'databaseName' EXEC sp_addlinkedsrvlogin 'LS','false',NULL, 'Username', 'password'EXEC sp_serveroption 'LS','data access','on'EXEC sp_serveroption 'LS','rpc out','on'EXEC sp_serveroption 'LS','rpc','on'[/b]The linked server object is created. But when I am trying to fetch the objects with below query[b]select top 5 * from [CCM7Dev].OEDB70_intermediary.dbo.company[/b]I have received the below error.[color=#red][DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.[/color]I am suspecting whether it is possible or not for below reasons.>Version compatiability problem>Provider mismatch or updagradation required>Connection problem.Note: I have used the valid credential to access the server. [b]Openrowset method may not suit [/b]to my requirement.Kindly advise me on this.Thanks in advance.Ramkumar.K

How to protect the database when restore by GUI

ramkpbrs — Mon, 02 Jan 2012 04:37:16 GMT

i want to protect my database backup with Password ,when other should try to restore.

Is accessing MDF and LDF file enough to steal data?

amirhh2000 — Tue, 29 Nov 2011 16:57:41 GMT

Hi all,My question is if someone have access to mdf and ldf file of a DB, is it enough for him to access everything? for example can he then create a SQL or Access database and access the data?? Regards,Amir

Ghost SID returned by SUSER_SID causing Login to fail with Token-based server access validation failed with an infrastructure error

datagate 5993 — Thu, 17 Nov 2011 04:14:37 GMT

Hi,I have a problem creating a particular SQL login from a windows login. SQL is getting a ghost SID that was associated with the windows login before renaming in the AD. Running SQL2008 Enterprise edition + SP2 on Windows 2008 DatacentreBackground An Active Directory (AD) account was created for a user [Domain\UserA] A SQL login was created for the account above and then granted access to a number of databases The AD account was renamed/modified to [Domain\UserB] At this stage the user would encounter an error when connecting to the server The sql log show this error message Error: 18456, Severity: 14, State: 11. Message Login failed for user 'domain\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx]Action on Server 1 SQL (the one with the problem) Dropped the user from the databases Re-Created the login from the windows account [Domain\UserB] Created the user in the respective databases But the user still unable to connect to the server Investigation On server 1, the SID of the user in SYSUSERS was Matching SYSLOGINS and matches with result of SUSER_SID(Domain\UserA) But it does not match the SID in the AD The rest of the servers all have the correct SIDs When I use SUSER_SNAME(Incorrect-Sid) and SUSER_SNAME(Correct-Sid) on this server they both return [Domain\UserB] The problematic server is always returning the incorrect SID when recreating the user login and when using SUSER_SID(Domain\UserA) as if it is cached somewhere. Another 4 SQL servers (of different editions 2000 and 2008 standard) are fine and recognise the correct SID.I can't specify the SID when creating the SQL login because it is using the Windows account Any idea on how to fix this problem is highly appreciatedKind Regards,

Certificate on SQL 2008 R2 not showing up

balcock — Mon, 14 Feb 2011 08:29:09 GMT

I am running SQL 2008 R2 Enterprise edition on a Windows 2008 R2 server. I have logged in under the SQL service account (the account used by SQL server process) and installed a certificate issued by ipsCA. I installed the certificate under the account. When I go into SQL Server Configuration Manager, right click on Network Configuration, Protocols for Server properties and go to the certficate tab, nothing shows up. Is there a SQL 2008 R2 issue, or are there special requirements for the certificate?Any help would be greatly appreciated.

compare two logins

kranthi.nagulapalli — Wed, 26 Oct 2011 15:51:53 GMT

Hi all,We have recently migrated our servers from one domain to the other. we have created logins according to the old domain.I would like to cross verify the two logins(server level and database level permissions).say... 'domain1\abc' and 'domain2\abc'.your help is greatly appreciatedregards,Kranthi

Login failed for user with token-based server access validation error

Rayven — Tue, 27 Jan 2009 03:09:53 GMT

Can anyone help as I am at a loss with this one.I am running SQL Server 2000 Standard Edition on a Windows Server 2003 standard edition machine.The way our in-house developed .NET applications and SQL Server work is simply as follows.Each application has an Active Directory group created for it, and users that are permitted to access the application are then added to the group.This AD group is then added into SQL Server, mapped to the appropriate databases, and then either granted permissions on the required objects, or are assigned to a database role that carries the required permissions.Up until today this has worked like a charm. That was until two users requested access to one of the applications. Both were set up identically and we've double checked everything, however when one of the users attempt to run the application reports that they do not have permissions and the following log is recorded in the SQL Server log.Login failed for users 'xxx\xxx'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]Error: 18456, Severity: 14, State: 11I've tried Googling the problems but what I'm reading makes no sense at all. :crazy:

Creating the ODBC connection - permissions

Iryna Roy — Fri, 26 Oct 2007 14:42:41 GMT

Hello All,I am trying to revoke some permissions (actually revoked) as it was recommended by AppDetective after PCI Audit from public account. And now I have a problem with the regular account creating the ODBC connection from any new computer. All existing are working fine and I could create the ODBC connection if logged in as sql administrator.Now I returned all permissions that were revoked to our specific group (not to public) and I can create the ODBC connection. But it is not resolving the probem if I need to revoke all dangerous permissions from regular accounts. Question: Which store proc/table/database should have EXECUTE/SELECT permissions to create ODBC connection to the database that the user has the permissions to connect?I could list all permissions that I revoked but it is more than 1000. instead of trying to turn on and off each of them, maybe you could help me if you know which permissions should be enabled??Thank you for any info :)

Droping the user SQL Server 2000

pratapgajjala — Wed, 31 Aug 2011 01:30:52 GMT

Hi If i dropthe user from Sql Server , will i able to open , execute,edit , save as the Stored Procedures, Packages , views created by that user.... if not , what i have to do. pls help ....thanks ,pratap

Security Benefits in Running SS2K8 in SS2K Compatibility Mode?

Tom Carnahan — Mon, 08 Aug 2011 07:38:21 GMT

The Background:-------------------I inherited a SS2K COTS Web application for which my organization bought the source code but did not get any documentation. We need to transition to SS2K8 for general purposes but specifically for the security features.The back-end has 500+ stored procedures, views, and functions. I have run the SS2K "Best Practices Advisor" tool on it and came up with a slew of discrepancies. I also ran the "SS2K8 Upgrade Advisor" tool and got a smaller list of discrepancies. I have read the Microsoft document "SQL Server 2008 Upgrade Technical Reference Guide" (486 pages) but realize that I will not be able to do the entire transition on my own. The biggest problem will be problems with the front-end. I know there is inline SQL in it and the volume of source code will mandate having a web developer (I am a DB analyst) on the team.I have been tasked with coming up with a transition strategy and making a schedule for how we will accomplish this task. --------------------------------------------------------The problem:My efforts are exploratory. I have no resources besides myself. One suggestion for determining the complexity of this task was to make a copy of the database in Development, transition it to SS2K8, point a test front-end to it, BUT, run SS2K8 in "[u]compatibility mode 80 [/u](SS2K)". Then see if we have any major problems. Then, switch the compatibility back and forth between compatibility mode for SS2K and SS2K8 to test for problems. I realize this is not optimum, but it would give my management an initial idea of what we are up against.----------------------------------------------------------[b]Question: [/b] [u]are any of the SS2K8 security features available if the db is running in SS2K compatibility mode[/u]? They are especially interested in the data encryption.

Connection to Blade server

sqlquest2575 — Wed, 27 Jul 2011 12:56:28 GMT

Hello Friends.......I need to connect to blade server but not sure how to connect.. When I tried to connect through mstsc(RDP) i got an error that computer cant connect to remote computer... Please help me here...

How to set xp_logininfo permissions

pdanes2 — Thu, 28 Jul 2011 20:19:53 GMT

I have a routine in my front-end that calls the following stored procedure and examines the resulting recordset to see to which groups the current user belongs. A few controls in the app are then enabled or not, depending on group membership.Ths procedure used to work just fine, until our IT department decided to move the entire museum into a domain (previously it was just workgroups). They're pretty understanding about it all and willing to work with me, so I asked them to move just the server into the domain first. They did, and I still have admin privileges on it, but this routine quit working when they did that.If I log in as sa and execute the procedure as is, it works fine, but the app can't run it - get an error message that execute permission was denied on xp_logininfo.If I uncomment the line 'WITH EXECUTE AS OWNER', I don't get an error, but I also don't get any results. Both as sa, running directly on the server via remote desktop, and from the app, executing it as an ODBC-accessible stored procedure, I get back an empty recordset.What happens when going to a domain and what do I need to do to make this work?Pete[code="sql"]CREATE PROCEDURE [dbo].[spClenSkupiny] @Skupina as varchar(100)--WITH EXECUTE AS OWNER ASBEGIN SET NOCOUNT ON; declare @Server_Skupina varchar(100) set @Server_Skupina = 'PALEO-SERVER\' + @Skupina EXEC master.dbo.xp_logininfo @Server_Skupina, 'members'END[/code]

Scurity issue....

bala2 — Fri, 22 Jul 2011 09:29:32 GMT

I am having a dbo owner permission on a the database even though i am unable to access some of the tables in that database.i am using sqlserver 2000 version.My question is if i am having dbo owner permission then i have to access every table but why i am unable to acccess the some of the tables.can any one do a need ful.............

Run Com based process outside SQL server (sp_OACreate 'CDO.Message')

David Paskiet — Fri, 01 Jul 2011 13:49:22 GMT

I have run into an issue in my SQL server (SQL 2K5 64Bit). After a while, the memory just tanks and the system becomes unusable. This is usually without warning until it is to late. I opened a case with Microsoft and sent them all the information they asked for and they came bakc with a few suggestions. One of them may possibly to do with sp_OACreate... [b]I do see xpSLS and SQLDMO as well. Try if we can move SQLDMO out of process too. most likely coming in because of sp_oacreate.[/b]I read an [url=http://support.microsoft.com/kb/198891]artcle on this and tried to run this process outside the SQL space by specifying the parameter rather than using the default value. When I do so, the whole process fails. In short, [b]EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT[/b] works fine. however[b] EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT,4 does not.[/b] Help?

Can't Access deployed Cubes on SQL 2008 R2 SSAS via Excell 2010 if not on the same Windows Domain

dfritt — Tue, 14 Jun 2011 12:44:55 GMT

I am able to connect to make a connection to the analysis services database via excel 2010, however I cannot see any of my cubes that i have deployed when connect from a different windows domain. When I am on the same domain there is no problem.I am using a defined instance for my SQL database and Analysis Services and opened the required ports.when i connect from a different domain . I get access to SSAS database , but can't see any of my cubes. I then uncheck the option to connect to a specific table and cube. It then come us with a message saying that there are no visibles tables !I have also tried to copy an existing connection that works on the current domain to see what happens , but i get a message - Error in OLED - check that the SQL browser is up and running . I have opened UDP 1434

how to connect sql 2000 enterprise manager using SQL Authentication.

srivivek84 — Thu, 03 Mar 2011 07:25:07 GMT

Hi, Iam new to SQL 2000I tried to connect using run as and enter my id and password but i am unable to connect to enterprise manager ( id & password which are working for to connect query analyzer).where do we need to supply the id and password if i want to connect using SQL Authentication.

certifcate issue

Oracle_91 — Sat, 21 May 2011 10:13:38 GMT

Hi,Am using sql server 2000 sp4. I have installed the ssl certificate on sql server machine.I logged in as sqk server service and installed the ssl certificate.Now my question is, can we install the certificate into sql server using a different account other than sql service account.If so, how to do that.Can anyone provide the any links for steps to do that?Thanks in Advance.

Insert without permissions from within a trigger

jalvarocrespo — Fri, 20 May 2011 09:45:42 GMT

Supose you have tables [TBL A] and [TBL B]CREATE TABLE [TBL A] ( [FIELD_A] [char] (10) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY]GOCREATE TABLE [TBL B] ( [FIELD_B] [char] (10) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY]GO[TBL A] has the following trigger:CREATE TRIGGER ITRIGGER ON [dbo].[TBL A] FOR INSERTASDECLARE @F_A AS CHAR(10)SELECT @F_A = (SELECT FIELD_A FROM INSERTED)INSERT INTO [TBL B] (FIELD_B) VALUES (@F_A)User USR_A has public database role permissions and select, insert, update, delete permissions on [TBL A] and no permissions on [TBL B].For what reason when USR_A inserts a record on [TBL A] a record is inserted also on [TBL B] even he has no permissions at all on [TBL B]?Thanks for your helpJoão Crespo

Explicit GRANT permission against fixed db roles

mail2sethu — Mon, 09 May 2011 23:52:22 GMT

Even though we work extensively on security and permissions, at times we will be in a situation to confuse ourself with a simple but tricky question. here is one of that kind(not to all but atleast to me). Particular User has been given db_datareader access to a specific database. The same user has been given an Explicit GRANT EXECUTE permission that is assigned to pubilc. Now my doubt is that, if in any stored procedure there is insert, update and delete statements and when this particular user try to execute.. wat will happen internally. Whether the user can execute that stored procedure without any issues or he will not be able to execute as he has got just db_datareader permission. Will be grateful if any our members can explian in detail.Thanks Sethu

Error in the Application LOG

Dominique DUCHEMIN — Mon, 09 May 2011 18:58:52 GMT

Hello,After migrating from test to production the OperationsManager Database it seems the data Warehouse is still using the RunAs Account from test somewhere ...Login failed for user 'AD\svcTMOMDWA'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx]Where should I change it?Thanks,Dom

Possible new attack against SQL Server coming

K. Brian Kelley — Tue, 20 Apr 2004 14:40:00 GMT

The Internet Storm Center saw an increase in scans for tcp/1433, the port SQL Server listens on in recent days. The news has also made C|Net.

Internet Storm Center - Handler's Diary for April 18, 2004

Port Details for tcp/1433

There's not a lot of information out yet as to why the scans are up and it may end up being nothing. Only time will tell.

Encrypt SSN in the database

ChrisGorila — Wed, 02 Nov 2005 09:14:00 GMT

All,

We have a system whcih uses SSN as primary key and with no encryption.

Any ideas on how to encrypt SSN in the database???

Thanks in advance.

Chris

Password for Database level in sql server 2008

ShahPrem20 — Thu, 17 Mar 2011 02:02:19 GMT

hi everybody, i have one application using sql server 2008 . i want to set password at database level. not in server level or for user sa. because i distribute my application to client and they have information about sa password and if they want they can see my table structure and i want that in this database my application can only use . how is it possiblei want to give all the permission to client . but they don't able to see my structure.

Accessing a Linked Server with no catalog

Will1922 — Wed, 22 Jul 2009 09:40:08 GMT

I have a linked server setup in 2000 with no catalog defined. I can see the table list in Enterprise Manager when I expand tables, so I know I have a good connection. My question is, how do I reference the tables in SQL with no catalog defined? I have setup linked servers to other SQL Servers and had no problem in SQL, I just had to fully qualify the table. (server.database.dbo.tablename) I try the following and get the following error:select * from linkedserver...tablenameServer: Msg 7313, Level 16, State 1, Line 1Invalid schema or catalog specified for provider 'MSDASQL'.OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].The linked server I am trying to access is IEX and the odbc driver I'm using is Simba ODBC. I can access this server through DTS and everything works. I'm just looking for a way to get around setting up a DTS package everytime I want a job to pull data from an IEX server.Any help anyone can give one this would be greatly appreciated. Thanks in advance.

Permissions for IUSR - can it be in a group?

wodom — Fri, 11 Feb 2011 15:14:06 GMT

I've seen similar questions in the forum, but none answering the exact question this is asking.We are switching to using Windows authentication from an IIS server. IIS uses a username of IUSR_<server> for authentication. We have a Windows local group which contains all database users, and all the permissions are assigned to that group. But when I put IUSR in that group, it doesn't give permission to the IIS related processes. The question is, (a) why not, and (b) what's the best way to get around this?So here are the 3 options I'm looking at:1. Putting IUSR in the group. Again, this doesn't work.2. Adding the IUSR name to SQL Server as a separate login, not in a group. This DOES work. But I don't like this; I want all the permissions to key off the group.3. Changing the IIS properties to use a different username already in the group. This DOES work. I like this a lot better than #2, but it seems #1 would be even better. But then why doesn't #1 work?So is IUSR special in some way, that it doesn't inherit the permissions of a group it's in? Or do we just maybe need to wait for a reboot or something before #1 starts working? (Not easy since this is a 24x7 server.)Or, would solution #2 or #3 be considered more proper and a better idea than #1?Note 1: We'll be upgrading to SQL Server 2008 soon, so this question applies to both it as well as 2000.Note 2: We don't have Active Directory now but we're in the process of migrating to it.

How can I delete a user that has granted access to others?

laker_42 — Wed, 04 Aug 2004 10:53:00 GMT

I am trying to convert a login from SQL Server to Windows authentication. I am trying to delete the sql login so I can create the windows nt login but it won't let me. It keeps saying "The user has granted or revoked privileges to the following in the database and cannot be dropped." I did some more digging and he granted access to a table to the public role. What can I do to be able to delete this login? I have tried several things but have had no luck. Any help would be appreciated.

An update on what I have found: This login has entries in the sysprotects as the grantor. I have tried granting these same permissions as dbo hoping that it would override what the user did but that didn't work either. Do I need to login with this user's login and remove the grant permissions?

Thanks

John

How to ensure Security for MDF and LDF files

ANIL MAHADEV-467502 — Fri, 06 Feb 2009 02:22:16 GMT

Hi All,Use Case:Need to prevent a Windows/SQL Server Users from attaching a database and allow only the DBO of that Database to Login, view and modify the database.Any other user if found must not be able to view the database contents.This scenario is not a traditional one, the case is that if MSDE / SQL Express is installed on Client Machines, how can we prevent them from accessing the MDFs, and attaching them to a different server.I have managed to achieve granular permissions on SQL Server and Windows. For example:There is Instance 1 and it has a database Sales and the login is called Anil.Now I have given Anil the DBO permission for the database and removed the BUILTIN\Administrators Login from the Instance 1. Now here is the problem.When I try to stop INSTANCE 1 and copy the mdf and ldf to Server 2 and try to attach it to INSTANCE 2, where Builtin\Administrators and sa is enabled. Now how do I ensure that sa does not have access to the database?I am really confused... Am I missing something?Any help appreciated.Cheers!Kind RegardsAnil MahadevSQL Server DBA MISPLBengaluruINDIA

rights to tables controlled how?

mountcrumpit — Mon, 07 Mar 2011 20:23:58 GMT

Can someone help point me in the right direction on this. I'd very much appreciate it. Every once in a while I get out of my depth but usually puzzle it out. This one is stymying:-PThe error(s):Specified owner name 'webuser' either does not exist or you do not have permission to use itor :user does not have permission to perform this operation on table '[webuser].bc2011075'both errors report the same sql error#:'80040e14' The are a result of a stmt in the SP to "Drop..." a [webuser] owned table, and to create a [webuser] owned table via a "select ... into"There is a second (identical) environment where these errors don't occur.Obviously, there is _something_ not identical.;-)The database has two users: Standard: webuser and Windows: IUSR_server.Authent is via windows and sql. I've verified stored passwords for webuser in connection strings and EntMgr security setup. This not a busy or involved db setup. pretty generic stuff.The errors appear when stmts in a (user) SP are executed via a ADODB object in VBScript.I've double checked permissions for IUSR and webuser security and the server registration security. Both users are members of Public. The rights for both users to the "drop"able table are identical in both environments. Other SPs work fine for whatever their designed purposeIt seems to me that SQL will not allow IUSR to drop or create tables. However I can't find a permission that controls that. And yet the other (identical environment does allow IUSR to drop /create. I'm pretty sure this is the only spot in this app where a connectionstring is not used (and hence I'm not acting as webuser -- but as anon IUSR. Maybe there is just a better way to do this than using an SP. The drops and select intos do have to happen. The tables are for ad-hoc queries.Please a bone, anyone?thanks

standard user unable to drop/grant in stored proc

mountcrumpit — Fri, 04 Mar 2011 16:17:11 GMT

I'm grasping at straws at this point. I hope you can help at least a little.This an issue of IUSR authentication not being able to effect specific tasks in SQL. I'm at wits end because comparisons of working envs. show no differences from the failing env(s). I'll start at the top.classic ASP running vbscript.the cx string for the sql instance:"Provider=SQLOLEDB.1;data source=precision;User ID=webMAISuser;password=userMAISweb;trustedconnection=no;Persist Security Info=False;Initial Catalog=MAIS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MAIS"all access to the database is fine until:Set cmd = Server.CreateObject("ADODB.Command")cmd.Commandtext = "usp_QueryGetTable1Name"cmd.CommandType = adCmdStoredProccmd.ActiveConnection= cnn ' .connectionstring cmd.Parameters.Append cmd.CreateParameter("@theUser", adChar, adParamInput, 15)cmd.CommandTimeout=600cmd.Execute ln, , adExecuteNoRecordsthe usp fails on either: (where @name has been proved) set @dropstring = 'drop table [webMAISuser].['+ @name + ']' exec sp_executesql @dropstringor exec('select clientid into [webmaisuser].' + @name + ' from applieds')the errors are slightly different, but apparently mean the same thing (no rights/permission):Microsoft OLE DB Provider for SQL Server error '80040e14' (dropping)User does not have permission to perform this operation on table 'webMAISuser.bc201163'.or Microsoft OLE DB Provider for SQL Server error '80040e14' (intoing)Specified owner name 'webmaisuser' either does not exist or you do not have permission to use it. Like I said, there are other environments where this situation executes wihtout incident works fine. The envshave the following security logins:servername\IUSR_servername windows userwebMAISuser Standardthey are both part of public and are users of MAIS. Neither has any special server roles.My one question I can't answer is, who am I that I don't have this permission? And why not, if the environment are the same? Any ideas how i can force a display in the response. and have sql tell me who it thinks I am? IUSR? webMAISuser? dbo???All environments are fine with other SPs. It really seems to be something about webMAISuser. No number of times of deleting and recreating the users or deleting/restore the database is making a differenceanything ringing a bell with anyone out there. thanks.

how to change the default DB Name while transferring LOGINS

srivivek84 — Thu, 03 Mar 2011 12:05:55 GMT

Hi, I have transferred logins from one SQL server 2000 to other SQL 2000 using the REV_LOGIN script provided by the Microsoft. everything went good ,but when i checked the login properties in the new server i found the default database as MASTER where in the previous server it is different. how to change this Default database name same as it is in primary server. I can't do manually because i got more than 1500 logins. please help me out.....thanks in advance.

Find create date for user login

SQLJocky — Wed, 02 Feb 2011 11:01:42 GMT

Does anyone know if there is a way to query anything that will show when a user login was created? Trying to determine when a user was deleted and added back to a database.

Getting my own password in an SP

ronmoses — Thu, 16 Dec 2010 08:53:46 GMT

We develop an app that connects to a SQL back end. Users log into the app using various usernames and passwords. But the app always logs into SQL Server as a sysadmin account called "ConEst". The standard user doesn't need to know this password in normal usage.I'm working on an SP that will use xp_cmdshell to export a bunch of data via bcp. As per what I've described above, this SP is always being executed by the ConEst user. But I need the ConEst user's password for the command string, so I can do this:[code]exec xp_cmdshell @Query queryout @Filename -n -U ConEst -P @pwd, no_output[/code]So... is there some way for the logged in user to derive his own password?Thanks!Ron MosesConEst Software Systems

SQLServerAgent AD Account

timothy bates — Tue, 16 Nov 2010 18:56:02 GMT

Currently our SQL server is starting the sqlserveragent service as the local system account. Due to inadequate local disk space we are changing our backups to a folder on a separate server.I am planning on having this be a domain account with access to the target destination and local user rights (not admin) on the sql box. I want to have this be as restrictive (yet functional) as possible.Aside from the obvious upgrade to 2005/08/R2 or drop in another drive am I on the right track and is there something I missed?