Add users to SQL Server

[Dale Fye]

Not exactly sure where I am likely to get an answer to this question, so
have cross-posted to a couple of Access and SQL Server newsgroups.

I've got an Access database that I need to migrate to SQL Server (too many
users over a wireless network).

I've upsized the tables to SQL Server, and linked the tables to the Access
application using a ODBC connection. Tomorrow, I intend to make these
DSN-less connections.

I'm relatively new to SQL Server, but I would assume that the next step
would be to add my users to the SQL Server, assign them to my database, and
assign them to a particular role. I would like to do all of this via code
(when the user opens the application), but have no idea how to accomplish
these tasks. I assume that there are SQL Server stored procedures that I
can call to accomplish my goal, but have no idea which ones.

Would greatly appreciate any help.

 

[SSM]

In SQL Server 2005, you can use:

CREATE LOGIN statement to create SQL Server logins
CREATE USER to give that login permission to access a specific database


Example from BOL:
CREATE LOGIN AbolrousHazem
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
USE AdventureWorks;
CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;
GO


Hope that gets you on the path.

 

[Stefan Hoffmann]

hi Dale,

I've got an Access database that I need to migrate to SQL Server (too many
users over a wireless network).

Are you running your application in a Windows domain?

btw, are you converting it to an .adp?

I'm relatively new to SQL Server, but I would assume that the next step
would be to add my users to the SQL Server, assign them to my database, and
assign them to a particular role.

I strongly recommend the use of Windows integrated security as this
makes development and managment of your users a lot easier.

Create groups in your Windows domain. Add your users to the groups.
Create your roles and assign the groups to it.

I would like to do all of this via code
(when the user opens the application), but have no idea how to accomplish
these tasks.

You need administrative priveleges to do that, so your database needs
these to. This is not a nice setup.

mfG
--> stefan <--

 

[Dan Guzman]

I'm relatively new to SQL Server, but I would assume that the next step

would be to add my users to the SQL Server, assign them to my database,
and assign them to a particular role. I would like to do all of this via
code (when the user opens the application), but have no idea how to
accomplish these tasks. I assume that there are SQL Server stored
procedures that I can call to accomplish my goal, but have no idea which
ones.

To add on the other responses, you can add users to roles using
sp_addrolemember. For example:

EXEC sp_addrolemember' MyRole, 'SomeUser';

You will need to initially connect to SQL Server using a priviliged account
in order to create logins, users and manage roles. That login will need
ALTER ANY LOGIN, ALTER ANY USER permissions as well as ALTER permission on
the role.

 

[Dale Fye]

Had not planned on converting to an ADP. Have not received much positive
feedback regarding ADPs.

My thought was that I would create a DSN-less connection string that logs
them into the SQL server with admin permissions, adds them to the Users and
Roles (if they are not already there), then closes that connection and
immediately links all the tables using a trusted connection.

Intent was to distribute this revised application as an MDE to protect the
code and the admin userid and password.

Does that seem reasonable?

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Michel Walsh]

You use a real server OS? A non-server OS, such as XP (I haven't check on
Vista), have a limited number of external active shares of 10: Right click
on a share folder, the context menu should present "Sharing and
Security...", and if you "Share this folder", the User limit "Maximum
allowed" is 10. That may be the actual problem with your Jet based
application, if more than 10 users try to access your shared Access
database-file. I don't *think* that this limit has any effect for an MS SQL
Server database, though.

Vanderghast, Access MVP

Not exactly sure where I am likely to get an answer to this question, so
have cross-posted to a couple of Access and SQL Server newsgroups.

I've got an Access database that I need to migrate to SQL Server (too many
users over a wireless network).

(...)

 

[Haggis]

You use a real server OS? A non-server OS, such as XP (I haven't check
on Vista), have a limited number of external active shares of 10: Right
click on a share folder, the context menu should present "Sharing and
Security...", and if you "Share this folder", the User limit "Maximum
allowed" is 10. That may be the actual problem with your Jet based
application, if more than 10 users try to access your shared Access
database-file. I don't *think* that this limit has any effect for an MS
SQL Server database, though.

Vanderghast, Access MVP


(...)

just a note ...there 'is' a way to get around the 10 user limit using XP
(but I will not give details in this forum)

but the better solution Is using real server software of course :>