Create username and password for database dynamically

Question

Post reply

Create username and password for database dynamically

ashok-831066

Right there with Babe

Points: 778
More actions
February 26, 2009 at 4:39 am

#135117

Hi,
I am using Asp.Net 2.0 application, First i have created database dynamically in my codebehind. well the db created successfully. Now i want to give username and password dynamically for my dynamically created database. Is it possible? If yes, please tell me how can we do?
Try
Dim strQuery As String
strName = txtName.Value
strQuery = "create database db" & strName & ""
createDataBase(strQuery)
Catch ex As Exception
End Try
End Sub
'Function for create database
Function createDataBase(ByVal strQuery)
Try
Dim blnResults As Boolean = False
Dim intRowsAffected As Integer
ConOpen()
Try
objCmd = New SqlCommand
objCmd.Connection = objCnn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = strQuery
intRowsAffected = objCmd.ExecuteNonQuery()
If intRowsAffected > 0 Then blnResults = True
Catch ex As Exception
createDataBase = ex.Message
'MsgBox(ex.Message)
Exit Try
Finally
ConClose()
End Try
Exit_Function:
createDataBase = blnResults
Catch ex As Exception
End Try
Return Nothing
End Function
The above one is created database dynamically in code behind. I need to create username and password dynamically in code behind.
Hope yours reply.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

sure it's fairly easy; adding a user has three things you need to do:

1. Add a login.

2. Add a user to that login

3.decide what rights they get.

Here is a script i think is a decent example....two roles, one for a developer, so he can create tables, and another role that can read/write.

Homefully it is obvious where to parameterize the names:

[font="Courier New"]

CREATE DATABASE Whatever

GO

USE Whatever

--create the Role for my Dev guys

CREATE ROLE [WhateverDEVAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverDEVAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]

--create role for my normal users

--create the Role for my Dev guys

CREATE ROLE [WhateverDEVUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVUsers]

--now add specific users to nearly-Admins

IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

BEGIN

EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

--add this user to permit read and write

END

IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'jeff')

BEGIN

EXEC MASTER.dbo.sp_addlogin @loginame = N'jeff', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

END

USE [WHATEVER]

--make a user in the db for the matching login

CREATE USER [bob] FOR LOGIN [bob]

CREATE USER [jeff] FOR LOGIN [jeff]

--add these logs to the role

EXEC sp_addrolemember N'WhateverDEVAdmins', N'bob'

EXEC sp_addrolemember N'WhateverDEVAdmins', N'jeff'

CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER]

CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]

EXEC sp_addrolemember N'WhateverDEVUsers', N'NT AUTHORITY\Authenticated Users'

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ashok-831066 Right there with Babe Points: 778 More actions · Answer 2

hi ,

thanks for yours reply. I implemented this query in master database.

I got an exception that

The CREATE DATABASE process is allocating 0.63 MB on disk 'Whatever'.

The CREATE DATABASE process is allocating 0.49 MB on disk 'Whatever_log'.

Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near 'ROLE'.

Msg 156, Level 15, State 1, Line 26

Incorrect syntax near the keyword 'USER'.

Msg 170, Level 15, State 1, Line 31

Line 31: Incorrect syntax near 'LOGIN'.