Scripting GRANTS
Aug 7, 2000I know there must be a way to script GRANTS for all users for specific objects(tables, sp's, etc). Can't find much in BOL, etc. Does anyone know how?
Any comments would be appreciated.
ADVERTISEMENT
Data Scripting Tool ( NOT DATABASE Scripting)
Mar 16, 2004Hi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that....
if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.
Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..
Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.
by to all
Grants
May 27, 2004Hi,
I'm trying to grant certain statements to a user, and for some reason i keep getting a syntax error but I just can't point it out.
GRANT SELECT, INSERT, UPDATE, DELETE
TO User1
Thanks!
Smitty
System Grants Without Admin Rsp.
Oct 31, 2006What kind of permissions I need, when I want to see the system views, perfstat, ect...but I don't need admin responsibility.
View 1 Replies View RelatedESSENTIAL PERMISSION GRANTS For SQL Database
Jan 17, 2008I am developing a web site with asp.net 2.0 and c#, using Visual Studio 2005 and Microsoft SQL Server 2005 ( I am still learning about these technologies and languages ).
I transferred my web site files to my NEW computer.
The web site worked fine when running inside Visual Studio, however when I tried to run it in its PUBLISHED format it was giving this error message:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Server Error in '/MA' Application.
Cannot open database "SiteData" requested by the login. The login failed.
Login failed for user 'CENTAURUSASPNET'.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I solved the problem by running SQL Server Management Stdio, right clicking “SiteData� database -> properties -> permissions->view server permissions then I chose “CENTAURUSASPNET� and since I DID NOT KNOW what grants are ESSENTIAL for the functioning of the database, I GRANTED EVERYTHING ( I ticked ALL the permissions).
But since, probably, a lot of GRANTS are not only unnecessary but also may compromise the security of the web site, I would like to know what are the ESSENTIAL OR MINIMUM GRANTS NECESSARY for making the database functional to the web site and what GRANTS may compromise security.
Permissions Grants, Denies Don't Take Effect
Sep 11, 2006Hi,
I'm trying to grant/deny object permissions in a user database using Enterprise Manager and query tool without success (for €˜public€™ role and individual sql logins). I€™m not getting any error messages. Permission changes just don€™t take effect. Although, there are few objects, which already have permissions granted and I'm able to change permissions for these ones.
How can I change the permissions?
What Are The Essential PERMISSION GRANTS For Making A FUNCTIONAL SQL Database ?
Jan 17, 2008I am developing a web site with asp.net 2.0 and c#, using Visual Studio 2005 and Microsoft SQL Server 2005 ( I am still learning about these technologies and languages ).I transferred my web site files to my NEW computer. The web site worked fine when running inside Visual Studio, however when I tried to run it in its PUBLISHED format it was giving this error message:Server Error in '/MA' Application. Cannot open database "SiteData" requested by the login. The login failed.Login failed for user 'CENTAURUSASPNET'.I solved the problem by running SQL Server Management Stdio, right clicking “SiteData� database -> properties -> permissions->view server permissions then I chose “CENTAURUSASPNET� and since I DID NOT KNOW what grants are ESSENTIAL for the functioning of the database, I GRANTED EVERYTHING ( I ticked ALL the permissions).
But since, probably, a lot of GRANTS are not only unnecessary but also may compromise the security of the web site, I would like to know what are the ESSENTIAL OR MINIMUM GRANTS NECESSARY for making the database functional to the web site and what grants may represent a security risk.
How To Generate All Grants To A User Role? (SQL Server 2000)
Apr 25, 2007I had thought that I had posted this yesterday but since I cannot findit here we go again.I have searched the archives but either the code I want is not postedor I missed it.I have a user create role in a database that I need to reproduce inanother version of the database. Does anyone out there have the SQLor T-SQL necessary to generate all the grants to a role?-- Mark D Powell --
View 2 Replies View RelatedCreate A Grants Rights Script Fails During Configuration
Jan 31, 2007I am in the reporting services configuration tool. When I am on database setup I get Create a Grants Scripts Right with a red X. Everything else passes. I am using sql server credentials and the user is sa on the database server. Any help would be appreciated.
View 1 Replies View RelatedCreate A Grants Rights Script Error In Rs Db Configaration
Feb 8, 2007Hi,
While I'm trying to configure rs db configuration, I€™m getting "Create a Grants Rights Script" error when I click Apply button in rs configuration UI.
For this setup; I€™m using 32-bit rs on 64-bit windows 2003 server and trying to connect 64-bit sql db instance on a different 64-bit box . both boxes are in same windows domain. I could also able to create rs db from rs configuration UI successfully. I'm using sql sa credentials and installed SQL SP1 on both boxes. Pl let me know how to fix this error.
Complate error message is,
System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(Object o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementObjectSearcher.Initialize()
at System.Management.ManagementObjectSearcher.Get()
at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)
at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)
at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)
Thanks in advance
Ram
SQL Security :: Create Database User And Give Grants To Few System Tables
Aug 12, 2015Need to create a user in sql server provide grants to few system tables to the above user.
View 10 Replies View RelatedScripting Job - ??? ARG !!! Help Please ...
Jun 29, 2001SQL 7
I am trying to run a script written for an asp page (pure vbscript) in a active script job. I am getting errors on:
Server.CreateObject("ADODB.Connection")
Yes, ADO is up to date and loaded. The error says
Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Object required: 'Server' Error on Line 14. The step failed.
Thoughts ..
Craig
Scripting
Sep 14, 2000I know it's a simple question, just can't figure it out:
How do I script a new column into an existing table in SQL? I am using MS SQL 7.0 and need to create several new columns in existing tables.
thanks all.
Scripting SPs In EM
Oct 28, 2002Does anyone know how to prevent or change the
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
that appear between each Create? I can't see any options for changing then or eliminating them when generating the SQL Script for my stored procedures.
Thanks,
Scott
Scripting Help
May 3, 2006I have to come up a script that will be going across 60 different servers that will be looking for document type that are not being used across all servers and then deleting them.
To start with:
Select Enabled from MHGROUP.CUSTOM5
where Enabled ='N'
then delete records
and where would I run this type of script or should I say can this be done?
Thanks
SQL 6.5 Scripting
Oct 7, 2005Anyone know how to write a script for sql 6.5 that identifies what column is a primary key on a table? (INFORMATION_SCHEMA doesn't exist) Thanks for the help in advance.
View 10 Replies View RelatedI Just Need To Know - Is This Even Possible To Do W/o Scripting???
Mar 21, 2007What I need to do - can it be done without scripting?
1.) I need to get a list of sp's from a table (EXEC SQL task)
2.) Next, loop through the list of sp's and execute (FOREACHLOOP)
3.) Then, write the output of each sp to a uniquely named file. (DATA FLOW task)
So far, I have had success with 1 and 2. But getting the DATA FLOW task to work has been imposssible thus far.
I tried everything I can think of. I even created a child package that gets called from the FOREACHLOOP, using a Parent Variable Configuration to pass down the name of the stored procedure variable to the child package. No dice.
I am ready to throw in the towel here.
So, my question is, can what I want to do even be DONE without scripting? I was trying to avoid the "manual" approach to this project. It seems like what I want to do "should" be so simple.
In sum, I am simply trying to read a list of sp's from a table, execute the sp's, then write the output of the sp's to a uniquely named file.
Can this be done without scripting? I am beginning to think NOT.
Sigh. This is driving me crazy.
Please, any advice!!
Thanks
SQL CMD And Scripting
May 15, 2006Hi Gurus/MVPS:
Can you please share or show me the code for scripting out stored procedures in SQL Server 2005 using SQL Cmd?. I need to perform the following:
1). Script out the text of the stored procedure
2). Output to a sql file (text file) under some directory like C:sql
3). Import the file into the destination db and run it.
The above three need to be automated in a DTS Package. I tried to use the DMO but my source server is a SQL2k5 and so DTS desginer does not let me use the ActiveX with DMO against SQL 2k5. I need to run the first step against a SQL2k5 machine and the second and third on a SQL2k machine.
Please help!!!.
Thanks
Ankith
Scripting A Database
Nov 18, 2003I just starting working for a new client this week. They have a custom install program written in VB.NET which installs the database to SQL Server 2000. Normally, when I want to create a SQL script to create a database, I would go into Enterprise Manager, right-click on a database, and select All Tasks | Generate SQL Script. However, that's not how this client works. Much to my horror, the CREATE DATABASE, CREATE TABLE, etc. statements are both hard-coded and hand-coded within the install program itself. When I asked them why they weren't using SQL Server's scripting capabilities to automatically generate the script, they said that SQL Server scripting is buggy and that it doesn't work properly. It was hard getting specific details but they said it would leave out indexes, for example. Now, I'm no SQL Server expert, but I've never had a problem using SQL Server's scripting capabilities. The database they use isn't that big - maybe 20-30 tables. So, before I make an issue out of it, I was wondering what everyone else's experience with SQL Server 2000's scripting features? If you've used SQL Servers scripting to recreate a database, does it work? Did you have issues?
View 6 Replies View RelatedScripting Database Changes
Nov 4, 2004Can you write a script that compares table changes. I have a testing database with a bunch of table changes that i need to move to production and instead of handtypeing the changes in I was wondering if there was a method of creating a script.
View 1 Replies View RelatedScripting Constraints
Nov 30, 2001Hi,
Just getting into scripting sql. I have a little script that adds and drops a default value for a column. But I cannot get it to run when the column already has a default value specified. If I didn't script the contraint and specify a name I have no name to specify when trying to drop it.
ALTER TABLE users ADD CONSTRAINT dev1 DEFAULT 1 FOR ns_email_flag
ALTER TABLE users DROP CONSTRAINT dev1
How do I remove any existing default values using script?
TIA,
Seoras.
Scripting DTS Packages?
Mar 26, 2001I am currently running SQL7 sp3 and will be moving the database to SQL2k. I have restored the database without any problems, but have not moved the dts packages. What would be the best solution: script the packages, save the packages to the 2k db, or just build from scratch? Any feedback would be appreciated. Thanks! -J-
View 2 Replies View RelatedScripting Jobs
Apr 10, 2001I would like to move over all of my DTS packages and corresponding jobs onto another MS SQL server machine. The problem that I have is that I haven't been able to find any straight-forward info on how to script up these jobs, move them onto another machine and execute them. I have scanned through previous posts written by people with the same problem, but every response is simply "script up the jobs and move them onto another server." Maybe I'm missing something, but how "exactly" do I get a job to show up on this destination machine in EM, in the management/sql server agent/jobs folder. I did the jobs/all tasks/script job... but then what? I saved it on this destination machine, but am unclear as what to do next. The MS SQL Server Administrators companion book is of no help on this subject, and I do not understand how this simple task can be so confusing and is not documented. Any help would be greatly appreciated.
Thanks in advance!
TB
Scripting DTS Packages
Jun 5, 2001Many times here I have read about scripting DTS packages to copy them to another server. How is this done? Under "All tasks/Generate SQL Scripts" there is no option for DTS. Sould I be looking elsewhere??
View 2 Replies View RelatedScripting Triggers
Jul 16, 2002How can I get a script of triggers (drop & create them) without scripting the tables with them ?
Thanks!
DTS Package Scripting
Jun 28, 2004Is there any way to script DTS Packages? :confused:
View 1 Replies View RelatedSSMS Scripting
Jan 29, 2008Are you kidding me?
I can't create 1 object per file anymore AND I can't have the drop created along with the create
Are they forcing you to use ALTER?
What a piece of junk
Scripting Triggers
May 7, 2004Hi all,
Is there any way to script all the triggers in a database just the way we script tables and stored procedures?
currently I am using the system tables.
regards,
Harshal.
DCS Scripting Language
May 19, 2008Does anyone here know anything about the DCS Scripting Language.
A contract vacancy has arisen using this language but I've never heard of it and there does seem to be anything on the internet about it. Can anyone help?
Scripting PK+FK+Index
Oct 28, 2004Hi,
Does anyone have scripts that generate the following:
1. All PK
2. All FK
3. All Indexes
Separate from the table DDL.
( EM can generate but with table DDL as well )
rockmoose
Scripting Tables
Apr 11, 2006Dear friends,
I have alot of tables to create in sql server 2005. They are all ready to copy and paste. Is there a window where I can just script all in at once?
Thankyou very much
Chopsmum
Scripting New Tables
Mar 9, 2007Hi all.
I am looking to write a script to create several new tables. What i would like to know is how do i check if they already exist before i create them and if they already exist dont create them? If they dont exist, create them.
Cheers people.
Scripting Issue
Mar 17, 2007Does anyone have any insight as to what is wrong with my SQL statement? All tables exist in this DB however, I get the messages at the bottom.
CREATE TABLE animal_colors(row_id INT NOT NULL IDENTITY(1,1) ,color VARCHAR(50))
INSERT INTO animal_colors (color)
SELECT
[description]
FROM
color
DECLARE @i INT
DECLARE @row_count INT
SET @i = 1
SET @row_count = (SELECT MAX(row_id) FROM animal_colors)
WHILE @i < @row_count
BEGIN
UPDATE animal
SET color =
(SELECT TOP 1 color
FROM color
WHERE color.[description] = animal_colors.color
and animal_colors.row_id = @i
)
FROM color
INNER JOIN animal ON animal.color = color.color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i
DELETE FROM color WHERE color NOT IN (SELECT TOP 1 color FROM color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i)
AND color.[description] = animal_colors.color AND animal_colors.row_id = @i
SET @i = @i + 1
END
DROP TABLE animal_colors
Result:
Server: Msg 107, Level 16, State 2, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.