I would like to compare two databases objects permission setting in SQL6.5. One is in production box and another one is in backup server. What is better and quick way to do this? Any system table is available for this information?
Thanks in advance.
Stella
Hi All,
Can someone tell me where the information about the permissions granted to a user are stored? For eg, user xyz in db1 has SELECT permissions on Col1, Col2, Col3 of Tab1 and UPDATE permissions on COL2 of the same table. Which system table(s) will hold this data?
I appreciate help in this regard.
Thanks !
I need to understand the permissions that a CLR stored procedure needs when it accesses tables. In a dbo TSQL stored procedure it has owner permissions on all dbo tables, so there is no need to grant permissions on tables to the database user.
Some developers recently implemented a CLR stored procedure that returned an error with update permission denied on table name. Once I granted the user account update permission on that table, it was able to execute OK.
I have been looking for a good explanation for the way the permissions to database objects need to be setup for CLR stored procedures. For example, could I have said to modify the procedure to use the EXECUTE AS clause, instead of granting the user account direct permission on the table? Does anyone have any links to good articles on this subject?
Edit:
I have a feeling I'm on my own trying to figure out how this works. I've been searching the web for hours, and I haven't found anything that directly addresses this.
CODO ERGO SUM
Hello,I am trying to write a script using SQL Server 2000 to list all of theroles that have any permissions on a specified object (view, table,sp, etc.). Essentially I am trying to script what is displayed whenone selects the 'list only users/user-defined database roles/publicwith permissions to this object' option under 'manage permissions' inEM but without showing individual users, only roles. I've looked atthe system sp's and the information_schema views but none of thoseseem to give this information. Am I going to have to look directly atthe system tables? If anyone has a script that does this for aspecified object or can point me to more specific information on howto do this I'd appreciate it. Thanks!Bruce
View 2 Replies View RelatedI'm trying to identify the objects in master that the role public has select permissions on, but when I run this query, I get 4 results where the default schema is null and the major_id column does not correspond to any records in the sys.all_objects table. Where else can I look to find what objects these are. DBO is listed as the grantor.
I appreciate your help.
SELECT *
FROM SYS.DATABASE_PERMISSIONS P,
SYS.DATABASE_PRINCIPALS R
WHERE P.GRANTEE_PRINCIPAL_ID=R.PRINCIPAL_ID and
permission_name='SELECT' and class_desc='OBJECT_OR_COLUMN' and
r.name='public'
order by r.name desc
If I want to easily and quickly grant all permissions to a group of objects
for one user (or group), how can I do this with the tools provided?
In 6.5, I could right-click the user or group and click the "Grant All" button.
In 7.0 this is either missing or has been moved.
Thanks!
Jim
Hi,
SQL Server Security is not my strong point so forgive me for asking stupid questions.
I have a bunch of tables and sprocs within a schema 'MySchema'. I have a user 'MyUser' defined in the database.
I would like to give MyUser permission to SELECT from tables and EXECUTE all sprocs in MySchema. What is the simplest way of doing that? Will the following:
GRANT EXECUTE ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION AS [db_owner]
GRANT SELECT ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION
accomplish that? (I can't test it out at the moment because our DBA isn't around and I don't have permission)
With best practices in mind - is what I am doing here considered "ok". Any suggestions/comments are welcome.
-Jamie
P.S. Can anyone recommend any documentation that talks about what best practices should be in the use of schemas. BOL is a bit sparse. Thanks.
While trying to execute a stored procedure I am getting error that 'SELECT permission denied on table .......' The DBA has given execute permission for the sp and still the same error. What needs to be done. When permissions are given through the SP it implies that the objects are given permissions ?
Putting in db_datareader group will give permission to read from all tables across all the databases in the server. We want that the user should be able to read data from only those tables called in the sp. Normally in SQL 2000 we used to give EXECUTE permissions to the sp only. This in turn would be enough for that user to get data while executing the sp.
How would I, using a sql script, copy permissions assigned to a useror a role in one or more databases to another user or a role in theirrespective databases?Help appreciated
View 2 Replies View Related
Using Management Studio how do you script only user and object permissions? I don't want to script the corresponding "Create" statements for each object, only their permissions. This was possible in 2000.
Thanks, Dave
We are having problems with the response times from UPS WorldShip after switching from SQL Server 2000 to 2005.
I think that the problem can be fixed from the database end by setting the permissions correctly for the user/role/schema that is being used by WorldShip to connect to the server but, I'm not sure how to do it.
The Setup
Client
UPS WorldShip 8.0 running on XP Pro SP2
Connecting via Sql Native Client via SQL Server Login
Connection is over a T1 via VPN
Server -
SQL Server Standard Edition on Windows Server 2003
2x3ghz Xeon processors w/ 4gb ram
The user that is being used to connect runs under it's own schema and role and only needs access to two tables in a specific database on the server.
What UPS WorldShip seems to be doing is on a continual basis retrieving information about the layout of the database via calls such as the following
exec [sys].sp_tables NULL,NULL,NULL,N'''VIEW''',@fUsePattern=1
exec [webservices].[sys].sp_columns_90 N'CHECK_CONSTRAINTS',N'INFORMATION_SCHEMA',N'webservices',NULL,@fUsePattern=1
exec [webservices].[sys].sp_columns_90 N'COLUMN_DOMAIN_USAGE',N'INFORMATION_SCHEMA',N'webservices',NULL,@fUsePattern=1
This seems to happen whenever WorldShip contacts the database to find out information in order to be able to create a mapping to the database as well as exporting information to it. Because of the VPN connection these calls take anywhere from 20 seconds to 3 minutes.
I am fairly confident that the problem lies with these calls to the database which I was able to capture using the SQL Server Profiler. We have experimented with the following setups.
1. Connecting to SQL 2000 over VPN with SQL Native Client - No noticeable lag
2. Connecting to SQL 2000 over VPN with SQL Server 2000 driver - No Noticable lag
3. Connecting to SQL 2005 locally with SQL Native Client - No Noticable lag
4. Connectiong to SQL 2005 over VPN with SQL Native Client - Lots of lag
Our network admin has been testing the network connections over the VPN and it is very responsive with none of the long wait times found when using UPS WorldShip.
Now for a possible solution other than getting UPS to fix their software. I think that by limiting the tables and views that the login is able to see will cut down significantly on the lag times that are being experienced. The problem is that there were 264 items that were being returned by sp_tables. I was able to cut that down to 154. I am unable to disable access to any of the rest of the items because they are server scoped.
Take for example the INFORMATION_SCHEMA.CHECK_CONSTRAINTS view. When I try to deny access to it in any way I get the following error:
Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master (Microsoft SQL Server, Error: 4629)
Am I able to deny access to these types of object and if so how? Also, what objects should be accessable such as sys.database_mirroring, sys.database_recovery_status, etc?
I'm making a copy of some tanles between 2 servers.
Server 1 requires a sql login
Server 2 is using Windows Auth.
I have a user on server 1 named "odbc" able to log in.
however my copy task fails, when I drill the error, it's lists the first user in server 1 alphabetically as the failed login???? but in my dts I am specifying the "odbc" user and password.
I think I have a permissions problem on server 1. So my Question, what minimum permissions does user "odbc" need to copy a table?
On server 1 I can copy from northwind to server 2 just fine..but any other db on server 1 causes the weird failure with the wrong username.
Any Ideas? I am not a DBA obviously :)
Thanks,
Carl
When bcp'ing in data into an sql 6.5 table that is NOT nullable, records in the text file that contain nulls are ommited, but valid records without nulls are inserted.
However, using exactly the same table structure and files on a newly installed sql 2000 server (sp2), as soon as the first invalid record in the text file (i.e. with a null value)is encountered, bcp'ing is terminated and NO records are inserted.
I can't find any option in bcp/sql 2000 to allow me to carry out a load as before.
The help files state that the default no. of invalid records allowed is 10 (both versions sql), and explicitly setting the -m option of bcp to 10 still doesn't work.
Most of the data i receive has at least one duff record
Is there an option in sql 2000 that allows me to have x number of duff records inserted before a full rollsback occurs?
I can't find anything in technet, msdn or books online.
Hi all
I have a major problem on a Alpha machine running NT4 and SQL6.5. There is 4 databases on the server all being accessed at the same time. I seems that the tempdb is only allowing processing on one database at a time. When that thread is done it allows processing on another database. I have reinstalled SQL and recreated all the databases, but still get the same problem.
Any ideas.
Tks
De Waal
my problem is : I want to export syslogins from database SQL6.5 to syslogins SQL7.0.
I use BCP (command : mssqlBCP master..syslogins out d:empsyslogins.dat -c -Sservername -Usa -P -ed:emplogins.err)
It runs well.
AFTER to export to SQL7 database :
(command : mssql7BCP master..syslogins in d:empsyslogins.dat -c -SNEWservername -Usa -P -ed:emplogins.err)
I always have DR WATSON.
If someone has encountered this problem and solved it.
Your help will be strongly appreciate.
Patrick
We have customers who use SQL6.5 and tempdb in RAM.(Our bank's application uses a lot of temporary tables by enquiry). For performance, customer uses tempdb in RAM instead of hard disk( test has been done and performance is better with tempdb in RAM. )
Customer wants to upgrade to SQL7. But, we can't put tempdb in RAM in SQL7.
My question is about SQL7 tempdb. - performance is better, the same, or bad - someone has an idea how can we put tempdb in RAM ? for instance RAMDRIVE or others programs and how is the performance ??
There is a good idea to put tempdb in RAM on SQL7 ?????? Your help will be appreciate a lot. thanks
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
ddave
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies View Relatedpassing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies View RelatedHi all,
I've inherited the administration of two SQL Servers one 7.0 and the other 2000. We are looking into upgrading to 2005 so I'm gathering all the info I can on the DBs and applications using them.
Now, how can I check if a DB is currently in use? Is there a way to find out when a DB was last accessed?
By the way, I've never done DB administration so you can figure out how desperate I'm getting.
Thanks all for your help,
GS
pratap writes " Dear respected sir,
i need small information regarding how the picture image data can be stored in the database and how it can be made possible. is there for the need for any conversion.
thank you
awaiting for your reply"
Hi, I hope someone can help me with this.
I want to get a query from a database
from between '3' AND '5' (NO PROBLEM)
but I want to add that I want info from 7 and up
how would I write that ? (if I do it in seperate lines the query returns nothing) which is not true.
Thank you.
Well i need to document what tables i have in my stored procedures. Im really new to sql as well. And the problem is if i try code i have or sp_depends for instance it will only tell me the tables that are in that DB so if I have multiple tables from different DB's they are left out. I was told that sp_MSForEachDb for go through each DB and I could try it that way. Any help or example code dumbed down for me would be awesome. This was posted in a previous thread but i though this would be a better explanation
View 3 Replies View RelatedHere's my string. I know it's way wrong right now SELECT binbox_receipt.partner_code ,binbox_receipt.link_id ,binbox_receipt_archive.partner_code,binbox_receipt_archive.link_id FROM binbox_receipt, binbox_receipt_archivewhere binbox_receipt_archive.link_id = binbox_receipt.link_idand binbox_receipt.partner_code = '1154' and binbox_receipt.link_id = '2684'and (binbox_receipt_archive.partner_code = '1154' and binbox_receipt_archive.link_id = '2684')I need to check 2 tables if in the first table the link_id and partner_code exist or the second table link_id and partner_code existany help would be greatly appreciated I'm a little new at this but having fun trying
View 11 Replies View RelatedHi
Iam not sure if the Question is approriate for this forum but here goes....
I have just started playing around with ASP .NET and since i do not have a lot of money i downloaded and installed MSDE as my database. So far i havent been able to find any free tools to access MSDE. Iam looking for GUI tools from where i can create Databases , tables etc... I have experience with MySQL database and am looking for something like MySQLAdmin for MSDE..
any help in this regard is appreciated
Thanks
Punit
Hi All,
I'm using a SQL selection to fill a DataGrid. One of the fields I have is called diagnosis. This field in the database can contain multiple diagnosis. But I use a set of characters to divide each diagnosis.
Example : Sick!@#$%Hurt!@#$%Ill!@#$%
My problem is this is how it looks in my Data Grid. Can someone tell me how to parse out each diagnosis.
Thanks
Hi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing ''''tempdb.dbo.##lockinfo'''
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u
kavira
Hello!
I'm new DBA.
I have triggers on my table in the database have been created one individual a long time ago. Sp_helptrigger stored procedure gave me just a little info about triggers. I would like to see the real code for each trigger, but I don't have idea where to look for it.
Please help me to find it.
Thank you
Anny.
After Sql server has been installed. Is there a way to find out for the mode that it is installed in, ie per processor or per seat?
Thanks
Can anyone point me in the direction of some online resource that explains, in detail, the statistics returned by DBCC SQLPERF?
Thanks
Phill
To all:
I have installed a new Compaq 7000 with NT4.0 sp3 and Sql Server 6.5 sp4 into production about two weeks ago.
Everything went fine until yesterday. Around noon I received 2 Exception_Access_Violation about a second apart
with two differend spids. About 20 mins later I got the Out of connections message and sql server stopped
reponding. I Could not stop sql server and had to reboot the server. I have configured the connections at 600
and generally we average a little over 400. Everything seems to be running fine now but I would like to get some
information from all the great minds out there!
What exectly is Exception_Access_Violation? Can I figure out what caused it or can I debug it? Do you think that the
EAV caused the out of connections error? Any help, insight would help greatly!
Thanks in advanced,
Glen Whitling
Respected Sir,
I am from India. My office has 15 location all over India. I am in corporate office.We have Ms Sql server 6.5. Also all other centers have
have same Ms Sql Server 6.5. We develop a Intranet based application.
In which Ms Sql Server 6.5 is the back end and Netscape browser is the front end.
The centers have their independent database. all also have different data structurre.
But the Head Office needs data from them. How the replication technology help us. and How we we will conect through internet or we haveto have seprate dedicated
leased lines back to centers.
What will be data structure .
Himansu