Create Procedure Permission ONLY

I have a requirement in SQL 2005 in Development database

1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema

How to achieve this using GRANT SCHEMA statement

Thanks

M A Srinivas(masri999@.gmail.com) writes:

Quote:

Originally Posted by

I have a requirement in SQL 2005 in Development database
>
1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema
>
How to achieve this using GRANT SCHEMA statement

The users need ALTER, SELECT, UPDATE, INSERT and DELETE permissions on
the schema and CREATE PROCEDURE and CREATE FUNCTION permissions on
the database. This script demonstrates:

CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev

GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev

CREATE TABLE mysig (a int NOT NULL)

EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you Erland

Thanks
Srinivas
Erland Sommarskog wrote:

Quote:

Originally Posted by

(masri999@.gmail.com) writes:

Quote:

Originally Posted by

I have a requirement in SQL 2005 in Development database

1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema

How to achieve this using GRANT SCHEMA statement

>
The users need ALTER, SELECT, UPDATE, INSERT and DELETE permissions on
the schema and CREATE PROCEDURE and CREATE FUNCTION permissions on
the database. This script demonstrates:
>
CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev
>
GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev
>
CREATE TABLE mysig (a int NOT NULL)
>
EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha

Create DBF file from ADO.Net

Hi Guys,

I have a requirement from a client to take some data from their SQL Server and create a Dbase 5 file with it. Scoured the forums looking for the answer but no luck. Any ideas?

Iain

SQL Server has Data Transformation Services (DTS) that allows to export data into different formats depending on providers and drives installed on that PC. I believe this is what you need|||Yeah - DTS is the obvious answer for this but I don't actually have access to their server to create packages. That's why I was looking to do it in code with a dataset/reader and maybe some kind of OLEDB driver. Any ideas?|||

Hi Iain,

Yes, you'd need an OLE DB data provider for Dbase 5. A web search brings up several third-party vendors whose products can access Dbase files, but I don't know which versions.

|||

i think foxpro can read from sql server

and can write to dbf files.

foxpro is not using dataset technology but it has extensive

support for different databases

|||I think you should look at a solution other than ADO.Net, I suspect Jet can do this from Native Code.|||

Hi Joey,

Yes, Visual FoxPro can integrate successfully with SQL Server, both FoxPro accessing SQL Server and SQL Server accessing FoxPro data.

Visual FoxPro can read all versions of FoxPro DBFs. However, the DBFs Ian was talking about were Dbase 5 DBFs and may not be compatible with Visual FoxPro's native data handling or ODBC drivers or OLE DB data provider/

|||

Thanks for the input guys. Looks like I'll have to try and find some 3rd party components. This is a little more difficult than I expected it to be!

|||

Hello IainH,

I am having a similar problem of creating a DBF file from a ADO.net DataSet, Please let me know if you find any solution.

Thanks In Advance,
Swetha

Create Database models from XML Schema (.xsd)

Hi .Net Guru's,

I have an urgent requirement for my project; the issue is mentioned below;
Using .Net(C#/VB.Net) I need to generate/created Database objects from XML schemas.

I don't have any sample xml schema file to give you.
You just imagine you have a sample .xsd file and this .xsd file will be used to create database tables.

Please let me know if you have any queries.

Thanks,
nick

Hi

You could create a DateSet from visual studio and add some TableAdapters to it VS will generate XSD for you.

And there is a free tool XSD2DB Utilitylets you manage database schemas using XSD files.

Hope this helps.