Showing posts with label creation. Show all posts

Thursday, March 29, 2012

Automating the creation of a database

Hi;
We have a program (ASP.NET) that requires a database as it's back end. We
are trying to create an install that requires as little expertise as
possible. In other words, no DBA required.
For creating the database itself we are at that point. We use the registry
to find the location of osql.exe and use that to run the schema that creates
the database. I'ld prefer an API we could call so we can more cleanly handle
errors but this works fine 98% of the time.
The remaining problem is ownership of the created database.
1) Is there a way (in .NET 2.0) to query if the database is mixed mode
authentication.
2) And if it is a way to both get a list of all users
3) And to create a user?
We can already enum all domain users. So with the above we could give them a
list of all users they can choose from as the owner and also let then create
a new one - without the user ever having to run any SqlServer tool or even
have any installed.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
It is SERVER property not a databases
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly
') AS
[IsIntegratedSecurityOnly]

> 2) And if it is a way to both get a list of all users
EXEC northwind..sp_helpuser

> 3) And to create a user?
Create Database mydb
go
use mydb
go
sp_addlogin 'mydbuser','monitor','mydb'
go
sp_adduser 'mydbuser'
go
sp_Addrolemember 'db_datawriter','mydbuser'
go
sp_Addrolemember 'db_datareader','mydbuser'
go
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi David,
I am afraid that the database may have some synchronous problem now. Our
yesterday's replies cannot be seen from Web and we also cannot see your
replies.
So I post it again from Outlook Express and hope you could see it now. Sorry
for bringing you any inconvenience.
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi Dave,
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual
data from the old db to the new one, but i would like to take the table
structure. in DB2, i could run through a ddl file that contained CREATE
DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
tables, databases, and the like. how exactly do i do this using MS SQL
server? im thinking along the lines of writing my own file, and then
using something in SQL server to basically read the file and execute
the SQL commands. any ideas? thanks.
qin_23
qin_23
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1835350.html
quin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1835350.html
>
sql

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual
data from the old db to the new one, but i would like to take the table
structure. in DB2, i could run through a ddl file that contained CREATE
DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
tables, databases, and the like. how exactly do i do this using MS SQL
server? im thinking along the lines of writing my own file, and then
using something in SQL server to basically read the file and execute
the SQL commands. any ideas? thanks.
qin_23
--
qin_23
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message1835350.htmlquin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1835350.html
>

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual data fr
om the old db to the new one, but i would like to take the table structure.
in DB2, i could run through a ddl file that contained CREATE DATABASE, CREAT
E TABLE, etc. kinds of commands to automatically create tables, databases, a
nd the like. how exactly do i do this using MS SQL server? im thinking along
the lines of writing my own file, and then using something in SQL server to
basically read the file and execute the SQL commands. any ideas? thanks.
qin_23quin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1835350.html
>|||alright. thanks it worked.
qin_23

Tuesday, March 27, 2012

Automating Creation of Reporting Services Reports (Excel format)

Hello,

Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?

Thanks for sharing your thoughts and ideas!

donnie100 wrote:

Hello,

Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?

Thanks for sharing your thoughts and ideas!

You mean you want to use SSIS to build the RDL? I'm not really sure why you would want to do this but heigh-ho.

Is there a .Net API for SSRS? if there is then you could call it from a SSIS script task.

-Jamie

Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?Hi
"Kristen" wrote:
> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John|||No....I will look into that. Thanks!
"John Bell" wrote:
> Hi
> "Kristen" wrote:
> > For DR purposes, I need to have a job that automatically scripts out the
> > creation of the logins and users for a database.
> > Looked at using sp_helprevlogin, but that is for different versions of SQL.
> > Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> > only users.
> > Anyone have any ideas?
> Have you looked at DMO and the logins collection?
> John|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:
> Hi
> "Kristen" wrote:
> > For DR purposes, I need to have a job that automatically scripts out the
> > creation of the logins and users for a database.
> > Looked at using sp_helprevlogin, but that is for different versions of SQL.
> > Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> > only users.
> > Anyone have any ideas?
> Have you looked at DMO and the logins collection?
> John|||Hi
"Kristen" wrote:
> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure you verify this. Based on
for what purpose you want this script, it might be very important for the logins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DMO does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>> Can you think of another way that does not entail alot of programming?
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQL
> Server version!
> Why not just backup the system databases?
> John
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!
Recovery model is inherited from the model database. So you can set model to simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on SQL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!
|||awesome! Thanks!
"Tibor Karaszi" wrote:

> Recovery model is inherited from the model database. So you can set model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!Recovery model is inherited from the model database. So you can set model to simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on SQL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!|||awesome! Thanks!
"Tibor Karaszi" wrote:
> Recovery model is inherited from the model database. So you can set model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> > Is there a way to set the recovery model of any new databases created on SQL
> > Server 2005 to Simple instead of Full? I would like the recovery model to
> > default to Simple for any new databases created on the server.
> >
> > Thanks for your help!
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!Recovery model is inherited from the model database. So you can set model to
simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on S
QL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!|||awesome! Thanks!
"Tibor Karaszi" wrote:

> Recovery model is inherited from the model database. So you can set model
to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
>

Sunday, March 25, 2012

Automatically increasing field definition by SQL

How can I create/define a field so it'll be of the automatically increasing type with a SQL sentence? If it must be done during table creation, that's cool too.
ThanksCreate table a
(
name varchar2(100)
);

Alter table a
modify name varchar2(200);|||Are you asking how to create a column that will increase in value, or increase in size? If you are looking to create something analagous to Oracle's rowid, the syntax is different for each database engine, so you'll have to tell us which engine you are using for us to give you one answer.

-PatP|||It's on ACCESS.|||Originally posted by anat_sher
It's on ACCESS. That's helpful, but are you looking for an MS-Access AUTONUMBER (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acconWhichTypeAutoNumberFieldCreate.asp) column, or a TEXT column that will increase in length each time you do something?

-PatP|||AUTONUMBER please..

It's not on ACCESS really, it's on a SQL server. But I figured it's about he same. No?|||create table tableA
(
id INTEGER IDENTITY(1, 1)
...
)

Sunday, March 11, 2012

Automatic Creation of LDF file

I was wondering how the automatic creation of a LDF (log)
file worked in MSSQL Server 2000. Is there a special
utility to use? I only have an MDF file and I try
to "Attach" it as a database. I get the following error
message:
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 1813: Could not open new database 'DBName'. CREATE
DATABASE is aborted.
Device activation error. The physical file name 'C:\SQL
Data\DBName_log.ldf' may be incorrect.
--
OK
--
It's looking for the ldf file and it won't create it. How
do I get it to create a new log file? I tried creating a
new blank database with the same name and using it's ldf,
but somehow it knows they're not from the same database.
ThanksDanny,
May be there were some open transactions and its searching the ldf file for
matching.Was this database properly detached before?A prerequisite for
sp_attach* is that the database should be properly detached using
sp_detach_db.Either you can RESTORE from a valid and latest database backup
or try the workaround mentioned in :
Restoring databases when only data file available
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
The archived newsgroup thread
http://tinyurl.com/m071
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Danny Forbes" <dforbes@.salessimplicity.net> wrote in message
news:105901c3724a$14f89e50$a601280a@.phx.gbl...
> I was wondering how the automatic creation of a LDF (log)
> file worked in MSSQL Server 2000. Is there a special
> utility to use? I only have an MDF file and I try
> to "Attach" it as a database. I get the following error
> message:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1813: Could not open new database 'DBName'. CREATE
> DATABASE is aborted.
> Device activation error. The physical file name 'C:\SQL
> Data\DBName_log.ldf' may be incorrect.
> --
> OK
> --
> It's looking for the ldf file and it won't create it. How
> do I get it to create a new log file? I tried creating a
> new blank database with the same name and using it's ldf,
> but somehow it knows they're not from the same database.
> Thanks|||Hi,
- Change your DB Status to Emergency Mode (32768).
- Stop SQL Server / Start SQL
- In query analyser, execute dbcc rebuild_log. REMEMBER: Rename old File Log
before.
- Change Your DB Status to Normal (0)
- Stop SQL Server / Start SQL
This error is because your GUID of file Log not match with definition of
your DB.
Regards,
Fabiano Maciel
"Danny Forbes" <dforbes@.salessimplicity.net> wrote in message
news:105901c3724a$14f89e50$a601280a@.phx.gbl...
> I was wondering how the automatic creation of a LDF (log)
> file worked in MSSQL Server 2000. Is there a special
> utility to use? I only have an MDF file and I try
> to "Attach" it as a database. I get the following error
> message:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1813: Could not open new database 'DBName'. CREATE
> DATABASE is aborted.
> Device activation error. The physical file name 'C:\SQL
> Data\DBName_log.ldf' may be incorrect.
> --
> OK
> --
> It's looking for the ldf file and it won't create it. How
> do I get it to create a new log file? I tried creating a
> new blank database with the same name and using it's ldf,
> but somehow it knows they're not from the same database.
> Thanks

Automatic Creation and Modification log

Hi there,

I need some help in order to reduce the amount of code needed to
implement the logging of INSERT and UPDATE in my application's
database.

In every table of my database I have four fields (CreatedBy,
CreatedTime, ModifiedBy, ModificatedTime), these fields allow me to
trace the users who create and modify the registers in my application.
I know that I can add in every stored procedure a piece of code who
uses the app user as a parameter, and insert these information in the
register. But I would like to go beyond these method. I imagine there
is a better way of doing it, and I don't like to use triggers. Is
there any method, within the SQLServer functionality, that allows me
to automate this process?

So, any help would be very appreciated!!
Thank you very much in advance!Phino wrote:
> I imagine there is a better way of doing it, and I don't like to use
> triggers. Is
> there any method, within the SQLServer functionality, that allows me
> to automate this process?

Yeah, triggers.

--
Steve Troxell

Thursday, March 8, 2012

automated role creation

Is there some other command that is used to add a role to the
database's permissions list? Seems there must be since all the other
parts of the permissions chain work quite well:
* sp_addrole
* sp_addrolemember
* grant <permissionon role::@.rolename
etc.

So the missing ingredient of something like sp_grantdbroleaccess is
what I need and can't seem to find it anywhere!

Thanks much for any helpYou create a role in the database then add users/roles to that role as
you say.

sp_grantdbaccess is used to allow users access to the database whereas a
role is a means of allowing permissions to be allocated to all users in
that role.

You can though administer via nt roles which may be the sort of thing
you are looking for.

www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***|||cybertoast@.gmail.com wrote:

Quote:

Originally Posted by

i seem to have some misunderstanding about how roles work in sql server
2005. i see that i can add a role to a database
(dbname->[right-click]->properties->permissions->[add ...]. THis allows
me to add either users or roles. Users can be added programmatically
using sp_grantdbaccess @.username, but this does not allow for addition
of roles to access the database (i.e., sp_grantdbaccess @.rolename does
not work).
>
Is there some other command that is used to add a role to the
database's permissions list? Seems there must be since all the other
parts of the permissions chain work quite well:
* sp_addrole
* sp_addrolemember
* grant <permissionon role::@.rolename
etc.
>
So the missing ingredient of something like sp_grantdbroleaccess is
what I need and can't seem to find it anywhere!
>
Thanks much for any help

You should use the T-SQL syntax instead of the SPs whenever possible,
as SPs might get obsolete at some point.

Basically, here is a simple process to handle the users and roles.

1) Create a server-wide login:
create login user1 with password = '123';

2) Create the user in your database:
use database1;
create user user1;

3) Create a role in your database:
use database1;
create role role1;

4) Add the user to the role:
use database1;
exec sp_addrolemember 'role1','user1';

5) Give some permissions to the role:
grant control to role1;

Whenever you need to fine-tune the permissions, you just use the
"grant" keyword on the role.

Note: "grant control" gives all permissions on the database objects,
most of the time you should be more restrictive.

Regards,
lucm|||This sequence is fine, but the problem is that the "grant control to
role1" portion does not add the role to the database permissions list.
Perhaps this is just a bug in the way SQL Server Mgmt Studio displays
roles and users. For instance, when I do a "sp_grantdbaccess
@.username", the user shows up in the database's permissions list. Doing
a "create role @.rolename" then "grant control to @.rolename" still does
not add the role to the "users or roles" permission list under Database
Properties.

I need to test out whether the permissions assigned to the role using
the steps above propagate down to the users irrespective of the
Database Properties issues I've described. However, it seems that there
could be synchronization issues if the "grant control" execution is not
reflected in the permissions gui (for example if someone decides to add
the same role again, what happens?).

Thanks much for the responses tho'.

On Oct 16, 12:32 pm, l...@.iqato.com wrote:

Quote:

Originally Posted by

cyberto...@.gmail.com wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

So the missing ingredient of something like sp_grantdbroleaccess is
what I need and can't seem to find it anywhere!

Quote:

Originally Posted by

Thanks much for any helpYou should use the T-SQL syntax instead of the SPs whenever possible,

as SPs might get obsolete at some point.
>
Basically, here is a simple process to handle the users and roles.
>
1) Create a server-wide login:
create login user1 with password = '123';
>
2) Create the user in your database:
use database1;
create user user1;
>
3) Create a role in your database:
use database1;
create role role1;
>
4) Add the user to the role:
use database1;
exec sp_addrolemember 'role1','user1';
>
5) Give some permissions to the role:
grant control to role1;
>
Whenever you need to fine-tune the permissions, you just use the
"grant" keyword on the role.
>
Note: "grant control" gives all permissions on the database objects,
most of the time you should be more restrictive.
>
Regards,
lucm

|||Never mind, I'm an idiot - adding the 'grant control' actually adds the
role to the database control set!!! My apologies. I was going under the
impression that just assigning "alter" permissions would be sufficient,
rather than "control" permissions.

Thanks much.

cybertoast wrote:

Quote:

Originally Posted by

This sequence is fine, but the problem is that the "grant control to
role1" portion does not add the role to the database permissions list.
Perhaps this is just a bug in the way SQL Server Mgmt Studio displays
roles and users. For instance, when I do a "sp_grantdbaccess
@.username", the user shows up in the database's permissions list. Doing
a "create role @.rolename" then "grant control to @.rolename" still does
not add the role to the "users or roles" permission list under Database
Properties.
>
I need to test out whether the permissions assigned to the role using
the steps above propagate down to the users irrespective of the
Database Properties issues I've described. However, it seems that there
could be synchronization issues if the "grant control" execution is not
reflected in the permissions gui (for example if someone decides to add
the same role again, what happens?).
>
Thanks much for the responses tho'.
>
On Oct 16, 12:32 pm, l...@.iqato.com wrote:

Quote:

Originally Posted by

cyberto...@.gmail.com wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

So the missing ingredient of something like sp_grantdbroleaccess is
what I need and can't seem to find it anywhere!

Quote:

Originally Posted by

Thanks much for any helpYou should use the T-SQL syntax instead of the SPs whenever possible,

as SPs might get obsolete at some point.