Simple scripted actions?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 18 Mar 2008 15:01 GMT

Hi I am completely new to scripted actions on Windows machines, I have been
doing these sorts of things for years on Linux/Unix machines. We have a SQL
2005 server which we share with a few departments, all of the logins and
databases are setup the same we create a login called name (where name is
the department name) and the database is called the same as the login, we
assign the login as the dbo as the database and set the password on the
login, and thats it.

Is there a way to script this somehow so that we do not have to login to the
server and do this each time? is there a way via PHP to create
logins/databases or is there another way to do it?

thanks,
-Drew

Reply to this Message

Sean - 18 Mar 2008 15:35 GMT

Yes there is a way to do this all via scripts.

Things you'll want to look at are CREATE DATABASE, CREATE LOGIN, and
CREATE USER.

In BOL (Books Online) here are the links:

Create Database: http://msdn2.microsoft.com/en-us/library/ms176061.aspx

Create Login: http://msdn2.microsoft.com/en-us/library/ms189751.aspx

Create User: http://msdn2.microsoft.com/en-us/library/ms173463.aspx

PHP can connect to SQLSERVER through ODBC. See this link:
http://msdn2.microsoft.com/en-us/library/bb264561.aspx

> Hi I am completely new to scripted actions on Windows machines, I have been
> doing these sorts of things for years on Linux/Unix machines. We have a SQL
[quoted text clipped - 10 lines]
> thanks,
> -Drew

Reply to this Message

Sean - 18 Mar 2008 16:01 GMT

Also I forgot to point out that if you want to see what TSQL is going
on when you're creating these things, you can actually step through a
manual creation of the objects and altering of properties and choose
Scripts XXXX As -> Create To and it will give you the TSQL.

This may be an easier way of doing it for you, however you still need
to know what the functions do.

> Yes there is a way to do this all via scripts.
>
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

Andy - 18 Mar 2008 16:05 GMT

There is actually a pretty nice script right here:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/SQL2005DGWHE.mspx

under the heading "Appendix: Provisioning Script"

which does exactly what i want from the SQL console, but when i use it to
create the login/database it doesn't allow the new login to see the new
database in SQL Server Management Studio.. The user can actually connect to
the database in scripts/what not but for whatever reason he is not able to
'browse' it in the console..

Is it because that script does not make the new login the dbo and I have
this setting? DENY VIEW SERVER STATE TO public

I dont believe that it.

Any advice?
Yes there is a way to do this all via scripts.

Things you'll want to look at are CREATE DATABASE, CREATE LOGIN, and
CREATE USER.

In BOL (Books Online) here are the links:

Create Database: http://msdn2.microsoft.com/en-us/library/ms176061.aspx

Create Login: http://msdn2.microsoft.com/en-us/library/ms189751.aspx

Create User: http://msdn2.microsoft.com/en-us/library/ms173463.aspx

PHP can connect to SQLSERVER through ODBC. See this link:
http://msdn2.microsoft.com/en-us/library/bb264561.aspx

On Mar 18, 10:01 am, "Andy" <awea...@ee.net> wrote:

> Hi I am completely new to scripted actions on Windows machines, I have
> been
[quoted text clipped - 13 lines]
> thanks,
> -Drew

Reply to this Message

Sean - 18 Mar 2008 16:18 GMT

So you are saying you can connect to the database using the newly
created user and you can run your queries against it, but not browse
it using SSMS? That is what should happen, however if you can't
execute queries against it we need to look further.

> There is actually a pretty nice script right here:
>
[quoted text clipped - 49 lines]
>
> - Show quoted text -

Reply to this Message

Andy - 18 Mar 2008 16:35 GMT

Right, but the users would like to be able to connect to it in the
management studio to create tables, etc.
is there no way to do this?
-Drew

So you are saying you can connect to the database using the newly
created user and you can run your queries against it, but not browse
it using SSMS? That is what should happen, however if you can't
execute queries against it we need to look further.

On Mar 18, 11:05 am, "Andy" <awea...@ee.net> wrote:

> There is actually a pretty nice script right here:
>
[quoted text clipped - 54 lines]
>
> - Show quoted text -

Reply to this Message

Sean - 18 Mar 2008 17:28 GMT

Andy,

From BOL : About 'DENY VIEW SERVER STATE TO Name'

"This setting also prevents this database from being displayed in the
Object Explorer window in SQL Server Management Studio for users who
are not the db_owner of their own database. If there are users who use
SQL Server Management Studio and need to access databases where the
account is not db_owner, this setting is not recommended. For more
information, see VIEW ANY DATABASE Permission. "

You can have them see it by adding them to the role of db_owner.

USE DATABASE_NAME
GO
EXEC sp_addrolemember N'db_owner', N'NAME'
GO

After adding them to the role, try accessing it in SSMS - they should
now be able to see the objects.

Reply to this Message

Andy - 18 Mar 2008 20:24 GMT

Can you think of any server wide issues this could cause?

We dont really have a preference about whether or not they blitz their
database as this is a design server, but we would prefer it if they didnt
blitz other dept's design databases.

-Drew

> Andy,
>
[quoted text clipped - 16 lines]
> After adding them to the role, try accessing it in SSMS - they should
> now be able to see the objects.

Reply to this Message

Andy - 18 Mar 2008 20:41 GMT

Sean,

I tried adding:

set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
'exec sp_addrolemember ''db_owner'', ''' +
REPLACE(@login_name, '''', '''''') + ''''
execute( @sql )

to that script on Microsoft's website, it said command executed successfully
but no joy in the studio, then i tried:

USE testing
GO
EXEC sp_addrolemember N'db_owner', N'testuser'
GO

it said successful as well and also did not allow exploration in object
browser..

hrm.. ;-)

> Andy,
>
[quoted text clipped - 16 lines]
> After adding them to the role, try accessing it in SSMS - they should
> now be able to see the objects.

Reply to this Message