I am trying to write a query to determine if public role has access to both of these. I need this to satisfy auditing requirements. If anybody knows how to write the t-sql to turn these on, I should be able to use the sql to find out if public role has access to these. Or if you just have a script already or a sp that would be fantastic also.
Thanks
Kyle
I need an example of how to grant access to a SQL user to a DB. For the life of me I can't seem to get my syntax correct. My database name is TEST and my username is LEMME_IN and I want to grant the user "Public" access to the db with db_datareader, db_datawriter database role membership.
Thanks
Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks
-Kyle
Greetings,
I have a set of reports that should be accessible to any user on our network. Those reports are located in a single folder in SSRS. I would like to configure security to allow anyone to see the reports in that folder. Is there a way to do this?
I do have other folders where I am using security so I do not want to open up system-wide access to all reports.
Thank you for your ideas.
Rob
Does anyone have the extended stored procedure xp_instance_regread? I have been unable to find it on the web.
View 1 Replies View RelatedI'm having a problem making a call that still works in SQL 2000.
When I call xp_regread, I get the following error:
Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, 'Access is denied.'
My calls work fine when I'm connected normally (either as myself with NT authentication or SQL authentication), but my application is using an application role to provide access to all of the tables and stored procedures. When I set the application role (using sp_setapprole), my call fails. I call a stored procedure in my database, which, in turn, calls master.dbo.xp_regread.
SQL Server is running as Local System.
I don't know what registry permissions I do or don't need in order to get this to work.
Given the error isn't regarding EXECUTE permissions on the procedure, I assume that it is a registry permission issue, not an issue with running the SP.
I found http://support.microsoft.com/kb/887165 related to a similar issue in SQL 2000, but there is no such registry setting when SQL 2005 is installed.
The problem is present in both Vista and WinXP, so operating system doesn't seem to be playing a factor.
I'm creating the Application Role in a SQL2000 compliant manner (EXEC sp_addapprole N'MattAppRole', N'Password123'), and activating it in a similar manner (exec sp_setapprole N'MattAppRole', {ENCRYPT N'Password123'}, 'odbc'). I'm not sure if that matters or not.
If anyone has any ideas what might be causing this problem, I'd really appreciate the help.
Matt
Hi!!
I removed 'xp_regread' procedure for security
after that I can't make JOB in Enterprise Manager
How can I make JOB without 'xp_regread' procedure
thanks~
Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out. I need to know this to pick the correct upgrade version.
-Kyle
Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out.
-Kyle
I'm trying to connect to a sql database, but I don't know what myserver is in the following code.Dim strConn As String = "server=myserver;database=Northwind"I can't get the code to link up with my Northwind database.I'm running everything locally if that helps.Thanks!Jon
View 2 Replies View RelatedIs there a system stored procedure that I can execute that will return the actual size of the database you are working with? Any information is appreciated.
View 1 Replies View RelatedHow do I find out if a temporary table named '##test' exists? I have a stored
procedure that creates this table and if it exists another stored procedure
should do one thing, if it does not exist I want the SP to do something else.
Any help as to how I can determine if this table exists at the current time
would be greatly appreciated.
Thanks in advance for you help,
Jon
Can anyone tell me what command/utility i can use to determine the database type (non-unicode or unicode) as well as the supported character set?
Any help will be greatly appreciated. Thanks a lot!!
Hi,
Is it possible to determine when last a DBCC statement was run on a database & what that DBCC was.
TIA
Shaun Tinline
Database Administrator
Alcatel Altech Telecoms
Hi,
I need to be able to identify if a parent has the same child names as another parent.
Have a look at my data (in pic)
Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?
So you would get something like:
Bob | 2
Bill | 1
Glen | 2
thanks,
david.
Forgive the easy question but I'm afraid it might be also a trick question and I'd like to hear the experts' opinion. I am using SQL Server 2005 Express edition and I know the limitation is 4GB per database. So far none of my users is anywhere near the limit but I have to be prepared for when that day finally comes. As it stands, they use a single database through a program so I have full control over it. There are no fancy backup programs on the system so no fancy recovery models and automatic shrinking can be done - data is only inserted in that database.
My question is simply how can I determine programmatically (I use ADO.Net but it can execute SQL commands just fine) the size of the database as it relates to the limitation? That is, I don't know whether it is the amount of data stored - with or without overhead, or it is simply the size of the *.mdf file (maybe together with the *.ldf file), or whether the 4GB is 4 billion bytes or 2^32 bytes - I just want the same method that the SQL Server is using so that, for example, I can bring up a warning at 90% full and lock out the user at 99% full.
I'd appreciate any insights.
Kamen
suppose you have a large table with 2 columns
create table tick
(
ID bigint identity (1,1) primary key not null
, price money not null
)
and I want to know 3 things
Starting with ID = 1 through ID = (last)
give me the low and high price (that satisfies the below WHERE clause), and the last ID
WHERE high price - low price = 0.10
and the last ID (last) is the minimum ID to satisfy: high price - low price = 0.10
So the last ID will coincide with the record containing either the low or high price, the problem is you don't know which record in that range has the corresponding high/low price, it could be the first record or the 10,000th record.
I am thinking I need to create two summary tables, maybe calculate the min(ID) that goes down 0.01 then the min(ID) that goes down 0.02, etc...
Then calculate the min(ID) that goes up 0.01 then up 0.02, etc..finally join against these two summary tables to figure out which combination of downSummary and upSummary have a difference of 0.10.
Hi,
I have a C# server application which clients can send arbitrary SQL statements to. These can be absolutely anything - creating tables/views, selecting from tables/views, inserts, updates, deletes, you name it.
There are two return parameters from the server method which executes the SQL - a results set containing the data, and a count of the rows that were updated - (either one or the other should be populated depending on the type of command sent to it). To deal with this, what I planned on doing was (pseudocode follows..):
try
{
reader = OpenReaderCursor(statement)
}
catch
{
updatedCount = ExecuteNonQueryCommand(statement)
}
Unfortunately this doesn't really work, as OpenReaderCursor is able to execute Non Queries (eg. UPDATE/INSERT/DELETEs etc) but doesn't give me a row count, and trying the other way round, ExecuteNonQueryCommand is happy to execute SELECT statements, but I can't then of course return a results set as I don't have access to it.
My question then, if you will excuse the waffle above, is "Is there a simple way of determining if a string containing an SQL statement is a query?" - or will I have to come up with some way of dealing with this in my application code?
Please don't slate the design (ideally I would have two methods on the server, one for queries which returns results and the other for nonqueries which returns an updatecount) but there's nothing I can do, this is how it must be done (the interface was defined long long ago)
Thanks for your time, all ideas appreciated
Jim
Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean
View 5 Replies View RelatedNot sure if this is the right place to post this but hopefully someone can help me. I would like to determine what the last automatically incremented ID is in a table I need to return it as a variable to a VB.NET program but I'm not sure what the SELECT statement would look like for this. Any help would be greatly appreciated.
View 4 Replies View RelatedHi,
can anyone tell me if there is a way to determine with SMO or RMO if a database is a subscriber when using merge replication. If only have the Server and database at this point too!!
I want to provide a small app that creates a merge publication but only if the database isn't a subscriber.
Thanks for your help
Graham
hi peter... i have a question about, how i can see if my sql server is the version 2005 sp2 and, what is the diference with server and server agent... i've checked the updates and the machine says i have up to date... but i dont know witch is.
thank you for your help
I am writing a client application that offers an UI that allows an administrator to remotely add/delete/update user accounts accross many different SQL Servers running on XP and up.
When the operating system is W2K3 or higher I want to take advantage of the "check_expiration, check_policy, must_change' arguments to create login and exclude those features when the host OS does not support them.
Is there an easy way to determine if those arguments are supported?
Thanks
Mark
How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example:
int count = (int)row["Count"];
and
int count = row["Count"] as int;
the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?
Does anyone have a script that will roll through the tables in a database and identify tables without primary keys defined? I did not see any in the online script database.
Thanks,
Rick
I am trying to find a global way of when the last time a row in one of my tables was updated or data inserted. I say global because I don't want to drill down through each table looking for modified rows.
I am a DBA of several hundred databases and want to retire those no longer being used.
Is there a column of a system table that has this info.
Is there a way to determine if a local variable exists or not?
There's a parameter I often use in code called @guid_batch that is usually declared in the parameter of a stored proc, but when in debugging it would be nice to have it available without having to change code.
Is there something that I could do similar to the following
Code:
IF VARIABLE_ID('@guid_batch') IS NULL
BEGIN
DECLARE @guid_batch UNIQUEIDENTIFIER
SELECT @guid_batch = NEWID()
END
Thanks in advance,
-MBirchmeier
I am new to MS SQL 2000 and have a couple of questions. First, how do you check the version of MDAC that Windows 2003 server is running and second, how do you determine what service pack that SQL is at.
I am having issues of extreme slowness using ODBC. I checked to see that tracing was not enabled and found that it wasn't. I am using TCP/IP for the connection.
Thanks
I have a table that contains spatial points and the name of the polygon they belong to (geography and varchar columns). I need to write a function to accept a point and determine which polygon contains that point. How can I write the query so that it will search through each polygon (derived from the geography points found in the table) and return the name of the polygon that contains that point?
View 2 Replies View RelatedHi everybody,
I am trying to determine the optimum amount of server memory that would be required for my SQL Server application. According to a book, it is calculated as follows:
Minimum memory required = System memory + User memory + Database process memory
I have thought about ways to determine each component of memory.
1) I could determine the System memory utilized from the Task manager (Total - Available), when the SQL Server service has been started, and no applications are running.
2) I could determine User memory by ascertaining the number of concurrent users during peak usage time of the application and then multiplying it with 0.5 MB.
3) I could determine Database process memory by summing up the "memusage" column in the SysProcesses table, at the time of
peak usage and multiplying it with 8.
Please let me know whether they are correct.
I am using vb.net and the myoledb provider.I've run into a problem where what I need to do is open a databaseconnection to a mysql server, easy. The problem arrises in that I wantto, if the server cannot be found, open from a locally storedencrypted XML file.Works fine when the sever is present, but when I go to open theconnection and the database cannot be found, a dialog box pops up as tofill in the connection string information. I do not want this, is thereany way I can intercept this dialog or skip past it so my try/catchhandles it?Thanks.--Posted via http://dbforums.com
View 2 Replies View RelatedI am trying to use MDX to dynamically determine which hierarchy of thetime dimension is selected. I have a calculated member which is usingthe last non-empty descendant of the [Time].[Calendar] dimension, butI want it to work even if Fiscal is selected instead of Calendar.Here is the codeSUM(Tail(Filter(Descendants([Time].[Calendar].CurrentMember, [CalendarDate]), isEmpty([Measures].[Employee Count]) = False), 1),[Measures].[Employee Count])What can I put in place of [Time].[Calendar].CurrentMember to take thecurrent hierarchy of the time dimension instead of the Calendarhierarchy? Thanks!
View 1 Replies View RelatedHello,I'm working with a number of databases. I need a way to determine if aforeign key with given name exists and determine what tables andfields are linked to it. Any ideas?I know I can issue the following statement to get a list of the fieldsthat are constrained by a foreign key constraint.SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGEHow can I get a list of fields pointed to by the constraints?Thanks,WillJoin Bytes!
View 1 Replies View Related