According to SQL Server 2000 Books online:
"When you add a Windows NT 4.0 or Windows 2000 login without a user account
in the database to a SQL Server database role, SQL Server creates a user
account in the database automatically, even if that Windows NT 4.0 or Window
s
2000 login cannot otherwise access the database".
My Questions: How can you add a login to a role without a user account (with
that login) in the database? So, I don't understand the above comments. I wa
s
unable to create the above example since I had to create a win 2000 login as
a user in the database first before adding that login to a role. Can someone
please explain the above comments?
Thanks,
KevMaybe you were trying it just through Enterprise Manager?
You need to use the system stored procedure to do this. Try
the following:
Create a new user on the machine.
In Query Analyzer, execute the following:
use northwind
go
sp_addrolemember 'db_datareader', 'YourMachine\YourUser'
The windows account will show up a user in the database with
access via group membership. You don't have to first add the
login or add the user to the database.
-Sue
On Wed, 21 Sep 2005 08:06:04 -0700, Nam
<Nam@.discussions.microsoft.com> wrote:
>According to SQL Server 2000 Books online:
>"When you add a Windows NT 4.0 or Windows 2000 login without a user account
>in the database to a SQL Server database role, SQL Server creates a user
>account in the database automatically, even if that Windows NT 4.0 or Windo
ws
>2000 login cannot otherwise access the database".
>My Questions: How can you add a login to a role without a user account (wit
h
>that login) in the database? So, I don't understand the above comments. I w
as
>unable to create the above example since I had to create a win 2000 login a
s
>a user in the database first before adding that login to a role. Can someon
e
>please explain the above comments?
>Thanks,
>Kev
Showing posts with label login. Show all posts
Showing posts with label login. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Adding users from cursor
Hi. Perhaps I am missing something obvious, but I am confused. I can
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.
Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.
Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Adding users from cursor
Hi. Perhaps I am missing something obvious, but I am confused. I can
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This is a multi-part message in MIME format.
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You need to use sp_executesql (Dynamic SQL) to accomplish this task. =Something like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT @.User =3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD =3D ''' + =@.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html=20
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message =news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> > CREATE LOGIN 'AUser' WITH PASSWORD =3D 'hello', DEFAULT_DATABASE =3D =TEST1
> > However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> > CREATE LOGIN @.usr WITH PASSWORD =3D @.pwd
> > I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with =sp_addlogin
> in SQL 2000 without a problem.
> > I've tried declaring @.usr as a varchar or nvarchar without success.
> > Please offer any suggestions.
> > Thank you.
>
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You need to use sp_executesql (Dynamic =SQL) to accomplish this task. Something like this:
DECLARE @.User nvarchar(20), @.pwd nvarchar(20), @.Sql nvarchar(200)
SELECT @.User ==3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' =+ @.User + ''' WITH PASSWORD =3D ''' + @.pwd + ''''
EXECUTE sp_executesql =@.Sql
See Erland's excellent article about =using Dynamic SQL.
Dynamic SQL - The Curse =and Blessings of Dynamic SQLhttp://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.=se/dynamic_sql.html -- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Jim Johnson" wrote in message news:1166460727.281101.297550@.79g2000cws.googlegroups.com=...> =Hi. Perhaps I am missing something obvious, but I am confused. I can> =add a SQL Server 2005 user with this command:> > CREATE LOGIN ='AUser' WITH PASSWORD =3D 'hello', DEFAULT_DATABASE =3D TEST1> > =However, I have 100 users to add, and I want to use TRANSACT-SQL to> create the =users automatically and pull their initial password info> from a =table. I use a cursor to do this, replacing the 'AUser' and> 'hello' with variables, for example:> > CREATE LOGIN @.usr WITH PASSWORD ==3D @.pwd> > I get an Incorrect syntax near '@.usr' error. Somehow this is not the> correct datatype, =I would imagine. I used this method with sp_addlogin> in SQL 2000 =without a problem.> > I've tried declaring @.usr as a varchar or =nvarchar without success.> > Please offer any suggestions.> => Thank you.>
--=_NextPart_000_029F_01C72286.20FC34B0--
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This is a multi-part message in MIME format.
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You need to use sp_executesql (Dynamic SQL) to accomplish this task. =Something like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT @.User =3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD =3D ''' + =@.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html=20
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message =news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> > CREATE LOGIN 'AUser' WITH PASSWORD =3D 'hello', DEFAULT_DATABASE =3D =TEST1
> > However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> > CREATE LOGIN @.usr WITH PASSWORD =3D @.pwd
> > I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with =sp_addlogin
> in SQL 2000 without a problem.
> > I've tried declaring @.usr as a varchar or nvarchar without success.
> > Please offer any suggestions.
> > Thank you.
>
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You need to use sp_executesql (Dynamic =SQL) to accomplish this task. Something like this:
DECLARE @.User nvarchar(20), @.pwd nvarchar(20), @.Sql nvarchar(200)
SELECT @.User ==3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' =+ @.User + ''' WITH PASSWORD =3D ''' + @.pwd + ''''
EXECUTE sp_executesql =@.Sql
See Erland's excellent article about =using Dynamic SQL.
Dynamic SQL - The Curse =and Blessings of Dynamic SQLhttp://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.=se/dynamic_sql.html -- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Jim Johnson"
--=_NextPart_000_029F_01C72286.20FC34B0--
Adding users from cursor
Hi. Perhaps I am missing something obvious, but I am confused. I can
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameter
s. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those paramet
ers. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||You need to use sp_executesql (Dynamic SQL) to accomplish this task. Somethi
ng like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT
@.User = 'Mary',
@.pwd = 'test'
SET @.Sql = 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD = ''' + @.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message news:1166460727.281101
.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
>
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
>
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
>
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
>
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
>
> I've tried declaring @.usr as a varchar or nvarchar without success.
>
> Please offer any suggestions.
>
> Thank you.
>sql
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameter
s. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those paramet
ers. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||You need to use sp_executesql (Dynamic SQL) to accomplish this task. Somethi
ng like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT
@.User = 'Mary',
@.pwd = 'test'
SET @.Sql = 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD = ''' + @.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message news:1166460727.281101
.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
>
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
>
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
>
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
>
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
>
> I've tried declaring @.usr as a varchar or nvarchar without success.
>
> Please offer any suggestions.
>
> Thank you.
>sql
Monday, March 19, 2012
Adding rules,roles and administrators to my website
Hi ,
I am trying to add users,rules,roles and administrators to my website, of course on my local server everything was ok, but now I can't login with my users on the server.
Basicly I created users and rules on my comp, and copied the App_data DB file to the root\app_data that is on the server(Using godaddy).
I understand that if the data is on the server it is no longer uses the DB in the root\app_data , but only the asp tables which are on MSSQL database.
I need help in copying the data from the data base in the App_data folder to the hosting database. I couldn't find the answers in the godaddy website help nor how to use their stored procedures to insert all the above.
(if I need to use the stored procedures please explain how)
Thanks very much for your help
Hi yrcaesar,
From the description, I understand you want to copy the datas from an absolute database (mdf, ldf) to a SQL Server on the server. If I have misunderstood your concern, please feel free to let me know.
I think you can simply attach the database for this scenario. Copy the database files to the server. Attach the database by right click "Databases" node in SQL Server Management Studio and select "Attach…" in the content menu.
Thursday, March 8, 2012
Adding numerous login via script
Hi,
Can anyone point me in the direction of a script that incorporates sp_addlogin, which allows for adding 200 sql authentication logins from an excel spreadsheet, or a temporary sql table with the id info, containing the username, password, and def db?
I'm trying to avoid adding each new login one by one.
EXEC sp_addlogin 'username', 'password', 'default database'Thanks, BPHA simple approach is to create an expression in your Excel spreadsheet that builds the Exec sp_addlogin line. Copy the formula down to all the rows, then copy/paste the value into Query Analyzer.
If you have loaded the columns into a database table, you can execute a Select statement that builds the lines. Again, copy and paste the results and execute them. Assume you have built a table call logintemp with username, pwd, and dbase columns:
Select 'Exec sp_addlogin ''' + username + ''', ''' + pwd + ''', ''' + dbase + ''''From logintemp
You need to double the quote marks to yield a quote in the output string.
|||Thanks. I'll give that a shot.Adding NT login using T-SQL
Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!
Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!
doesn't seem to have this capability.
Thank you in advance for your help!
Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!
Adding NT login using T-SQL
Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogi
n
> doesn't seem to have this capability.
> Thank you in advance for your help!
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogi
n
> doesn't seem to have this capability.
> Thank you in advance for your help!
Adding NT login using T-SQL
Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!
adding NT AUTHORITY\NETWORK SERVICE problem
Hi,
I trying to add a login AUTHORITY\NETWORK SERVICE'. to sql 2005.
I got the error says the windows NT AUTHORITY\NETWORK SERVICE not found, pls
check the name again.
ANy ideas.Try to execute this statement
USE [master]
GO
CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=[master]
GO
Regards,
Manoj Raheja
MCDBA, MCITP
"mecn" wrote:
> Hi,
> I trying to add a login AUTHORITY\NETWORK SERVICE'. to sql 2005.
> I got the error says the windows NT AUTHORITY\NETWORK SERVICE not found, p
ls
> check the name again.
> ANy ideas.
>
>
I trying to add a login AUTHORITY\NETWORK SERVICE'. to sql 2005.
I got the error says the windows NT AUTHORITY\NETWORK SERVICE not found, pls
check the name again.
ANy ideas.Try to execute this statement
USE [master]
GO
CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=[master]
GO
Regards,
Manoj Raheja
MCDBA, MCITP
"mecn" wrote:
> Hi,
> I trying to add a login AUTHORITY\NETWORK SERVICE'. to sql 2005.
> I got the error says the windows NT AUTHORITY\NETWORK SERVICE not found, p
ls
> check the name again.
> ANy ideas.
>
>
adding new users
SQL 2005 Express;
Win XP/2003
Our app defines an Admin for the application.
He has a database login.
An Admin can add users, which then get created on
the server and added to the database.
This appears to require that he have sysadmin.
We don't want them to be able to log directly into
the database with sysadmin priviledges.
We thought about using Application roles, but we
are reluctant to hardcode the password in the
application, thus having the same password across
installations.
Any other ideas on how to let then create users on the
server without the Admin having sysadmin?
--
Thanks,
Brad.Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||Looks promising. I'll check it out.
Thanks,
Brad.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23i5oJYr$GHA.4740@.TK2MSFTNGP03.phx.gbl...
> Look at the fixed server and database roles. See 'Roles' in Books Online.
> For example:
> The securityadmin role can add Logins -but cannot provide any access or
> permissions. That is left up to the
> appropriate database role.
> Could that serve your purpose?
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23CL1EQq$GHA.4328@.TK2MSFTNGP03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
> Our app defines an Admin for the application.
> He has a database login.
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>|||In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||We have a database user that has SecurityAdmin on the
server and on the database.
It can add users to the Server, but
a) can't run sp_adduser: not enough permissions to run SPs.
b) can't add the user to the DB directly:
Create user 'username' for login 'loginname'
No permissions to add user to the DB.
Tried giving this user permission to exec the SP, and
that failed as well.
Making this user an owner of the DB, obviously solves
the problem, but we'd really rather not. 8:-)
What other minimum server roles or db roles can we add
to get this?
--
Thanks,
Brad.
"Brad White" <bwhite at inebraska . com> wrote in message news:OlwDcbt$GHA.4
992@.TK2MSFTNGP03.phx.gbl...
In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||As I indicated earlier, the securityadmin role can add logins -but cannot gi
ve permissions to the databases, or to the objects in the databases. I sugge
sted that you check in Books Online about the use of the fixed server roles
and the fixed database roles.
It sounds like you need both a server fixed role AND a database fixed role.
When you finally check in Books Online, check out the capabilities of the db
_accessadmin and the db_securityadmin database roles.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%237KjVe2$GHA
.1196@.TK2MSFTNGP02.phx.gbl...
We have a database user that has SecurityAdmin on the
server and on the database.
It can add users to the Server, but
a) can't run sp_adduser: not enough permissions to run SPs.
b) can't add the user to the DB directly:
Create user 'username' for login 'loginname'
No permissions to add user to the DB.
Tried giving this user permission to exec the SP, and
that failed as well.
Making this user an owner of the DB, obviously solves
the problem, but we'd really rather not. 8:-)
What other minimum server roles or db roles can we add
to get this?
--
Thanks,
Brad.
"Brad White" <bwhite at inebraska . com> wrote in message news:OlwDcbt$GHA.4
992@.TK2MSFTNGP03.phx.gbl...
In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>
Win XP/2003
Our app defines an Admin for the application.
He has a database login.
An Admin can add users, which then get created on
the server and added to the database.
This appears to require that he have sysadmin.
We don't want them to be able to log directly into
the database with sysadmin priviledges.
We thought about using Application roles, but we
are reluctant to hardcode the password in the
application, thus having the same password across
installations.
Any other ideas on how to let then create users on the
server without the Admin having sysadmin?
--
Thanks,
Brad.Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||Looks promising. I'll check it out.
Thanks,
Brad.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23i5oJYr$GHA.4740@.TK2MSFTNGP03.phx.gbl...
> Look at the fixed server and database roles. See 'Roles' in Books Online.
> For example:
> The securityadmin role can add Logins -but cannot provide any access or
> permissions. That is left up to the
> appropriate database role.
> Could that serve your purpose?
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23CL1EQq$GHA.4328@.TK2MSFTNGP03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
> Our app defines an Admin for the application.
> He has a database login.
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>|||In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||We have a database user that has SecurityAdmin on the
server and on the database.
It can add users to the Server, but
a) can't run sp_adduser: not enough permissions to run SPs.
b) can't add the user to the DB directly:
Create user 'username' for login 'loginname'
No permissions to add user to the DB.
Tried giving this user permission to exec the SP, and
that failed as well.
Making this user an owner of the DB, obviously solves
the problem, but we'd really rather not. 8:-)
What other minimum server roles or db roles can we add
to get this?
--
Thanks,
Brad.
"Brad White" <bwhite at inebraska . com> wrote in message news:OlwDcbt$GHA.4
992@.TK2MSFTNGP03.phx.gbl...
In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||As I indicated earlier, the securityadmin role can add logins -but cannot gi
ve permissions to the databases, or to the objects in the databases. I sugge
sted that you check in Books Online about the use of the fixed server roles
and the fixed database roles.
It sounds like you need both a server fixed role AND a database fixed role.
When you finally check in Books Online, check out the capabilities of the db
_accessadmin and the db_securityadmin database roles.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%237KjVe2$GHA
.1196@.TK2MSFTNGP02.phx.gbl...
We have a database user that has SecurityAdmin on the
server and on the database.
It can add users to the Server, but
a) can't run sp_adduser: not enough permissions to run SPs.
b) can't add the user to the DB directly:
Create user 'username' for login 'loginname'
No permissions to add user to the DB.
Tried giving this user permission to exec the SP, and
that failed as well.
Making this user an owner of the DB, obviously solves
the problem, but we'd really rather not. 8:-)
What other minimum server roles or db roles can we add
to get this?
--
Thanks,
Brad.
"Brad White" <bwhite at inebraska . com> wrote in message news:OlwDcbt$GHA.4
992@.TK2MSFTNGP03.phx.gbl...
In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>
Tuesday, March 6, 2012
Adding Logons
I have transfered databases from one server to another.
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
Tom
On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user from
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent
|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegr oups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>
|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegr oups.com...
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
Tom
On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user from
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent
|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegr oups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>
|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegr oups.com...
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>
Adding Logons
I have transfered databases from one server to another.
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
TomOn Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user from
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
>> I have transfered databases from one server to another.
>> All my users are showing up in my databases but there is nothing in the
>> "Login Name" column. I tried adding in the user in EM in Logins under
>> the
>> Security folder and that works also. But when I create the user and give
>> it
>> access to the database, I get an error that says the user already exists.
>> Now the user is in the Logins and Users section but still no Login Name
>> showing and you can't logon to the database. But when I drop the user
>> from
>> the database and add it to the Login User again, it works fine. Do I
>> have
>> to do this for each user? This is a problem as we have about 15
>> databases
>> and about 40 users.
>> Is there a better way to deal with this?
>> Thanks,
>> Tom
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
>> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
>> I have transfered databases from one server to another.
>> All my users are showing up in my databases but there is nothing in the
>> "Login Name" column. I tried adding in the user in EM in Logins under
>> the
>> Security folder and that works also. But when I create the user and
>> give it
>> access to the database, I get an error that says the user already
>> exists.
>> Now the user is in the Logins and Users section but still no Login Name
>> showing and you can't logon to the database. But when I drop the user
>> from
>> the database and add it to the Login User again, it works fine. Do I
>> have
>> to do this for each user? This is a problem as we have about 15
>> databases
>> and about 40 users.
>> Is there a better way to deal with this?
>> Thanks,
>> Tom
>> You must have restored the database to new server. The reason you're
>> having this problem is the SID for login and SID for the user in the
>> database are different. You have Orphaned Users. Since you have the
>> login in the server and user in the database you can keep doing what
>> you're doing remove the user and add the user again. Or you can use
>> sp_change_users_login. Check the books online for detailed usage of
>> the system stored proc or you can read the article for how to fix your
>> problem. Look into Chadmat's last post that has the tsql for
>> sp_fixusers procedure.
>> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>> Good day,
>> Bulent
>
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
TomOn Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user from
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
>> I have transfered databases from one server to another.
>> All my users are showing up in my databases but there is nothing in the
>> "Login Name" column. I tried adding in the user in EM in Logins under
>> the
>> Security folder and that works also. But when I create the user and give
>> it
>> access to the database, I get an error that says the user already exists.
>> Now the user is in the Logins and Users section but still no Login Name
>> showing and you can't logon to the database. But when I drop the user
>> from
>> the database and add it to the Login User again, it works fine. Do I
>> have
>> to do this for each user? This is a problem as we have about 15
>> databases
>> and about 40 users.
>> Is there a better way to deal with this?
>> Thanks,
>> Tom
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
>> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
>> I have transfered databases from one server to another.
>> All my users are showing up in my databases but there is nothing in the
>> "Login Name" column. I tried adding in the user in EM in Logins under
>> the
>> Security folder and that works also. But when I create the user and
>> give it
>> access to the database, I get an error that says the user already
>> exists.
>> Now the user is in the Logins and Users section but still no Login Name
>> showing and you can't logon to the database. But when I drop the user
>> from
>> the database and add it to the Login User again, it works fine. Do I
>> have
>> to do this for each user? This is a problem as we have about 15
>> databases
>> and about 40 users.
>> Is there a better way to deal with this?
>> Thanks,
>> Tom
>> You must have restored the database to new server. The reason you're
>> having this problem is the SID for login and SID for the user in the
>> database are different. You have Orphaned Users. Since you have the
>> login in the server and user in the database you can keep doing what
>> you're doing remove the user and add the user again. Or you can use
>> sp_change_users_login. Check the books online for detailed usage of
>> the system stored proc or you can read the article for how to fix your
>> problem. Look into Chadmat's last post that has the tsql for
>> sp_fixusers procedure.
>> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>> Good day,
>> Bulent
>
Saturday, February 25, 2012
Adding Logons
I have transfered databases from one server to another.
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
TomOn Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give
it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user fro
m
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom[vbcol=seagreen]
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
TomOn Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give
it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user fro
m
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom[vbcol=seagreen]
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>
Adding login to SQL SERVER
I am trying to add a new login using sp_addlogin but I
get message:
'User X does not have permission to perform this action'.
I executed
sp_addsrvrolemember 'User X', 'sysadmin' and
sp_grantlogin 'domain\user X '
but when I login as an user X IS_SRVROLEMEMBER tells me
that 'Current user's login is NOT a member of the
sysadmin role'.
I executed sp_helpsrvrolemember and I see that
domain/user X is a member of sysadmin but is not a member
of securityadmin.What do you get when you run the following command:
SELECT SUSER_SNAME()?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:082801c3b902$833d6d60$a001280a@.phx.gbl...
I am trying to add a new login using sp_addlogin but I
get message:
'User X does not have permission to perform this action'.
I executed
sp_addsrvrolemember 'User X', 'sysadmin' and
sp_grantlogin 'domain\user X '
but when I login as an user X IS_SRVROLEMEMBER tells me
that 'Current user's login is NOT a member of the
sysadmin role'.
I executed sp_helpsrvrolemember and I see that
domain/user X is a member of sysadmin but is not a member
of securityadmin.
get message:
'User X does not have permission to perform this action'.
I executed
sp_addsrvrolemember 'User X', 'sysadmin' and
sp_grantlogin 'domain\user X '
but when I login as an user X IS_SRVROLEMEMBER tells me
that 'Current user's login is NOT a member of the
sysadmin role'.
I executed sp_helpsrvrolemember and I see that
domain/user X is a member of sysadmin but is not a member
of securityadmin.What do you get when you run the following command:
SELECT SUSER_SNAME()?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:082801c3b902$833d6d60$a001280a@.phx.gbl...
I am trying to add a new login using sp_addlogin but I
get message:
'User X does not have permission to perform this action'.
I executed
sp_addsrvrolemember 'User X', 'sysadmin' and
sp_grantlogin 'domain\user X '
but when I login as an user X IS_SRVROLEMEMBER tells me
that 'Current user's login is NOT a member of the
sysadmin role'.
I executed sp_helpsrvrolemember and I see that
domain/user X is a member of sysadmin but is not a member
of securityadmin.
Thursday, February 9, 2012
Adding Active Directory login (ca vs. samaccount)
I am having some issues adding user logins to SQL Server. When I
search for users in Active Directory from Security->Logins->New Login,
I can search for and add users using the short name (samaccount, ex
domain\abcde). However, when I search for users using their CA name
(ex. domanin\firstname.lastname), the user is not found.
search for users in Active Directory from Security->Logins->New Login,
I can search for and add users using the short name (samaccount, ex
domain\abcde). However, when I search for users using their CA name
(ex. domanin\firstname.lastname), the user is not found.
Is there some setting that needs to be activated in SQL Server to see
the CA name as well as the samaccount name, or is this an issue that
must be resolved on the domain controller.
I would appreciate any insight.
Thanks,
MattHi
SQL Server 2000 only understands "domain\abcde" and not abcde@.domain or CA
names.
Sorry, but can't do much about it.
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mattsh" <matt_sh2001@.yahoo.com> wrote in message
news:1116503684.651798.63340@.g43g2000cwa.googlegro ups.com...
>I am having some issues adding user logins to SQL Server. When I
> search for users in Active Directory from Security->Logins->New Login,
> I can search for and add users using the short name (samaccount, ex
> domain\abcde). However, when I search for users using their CA name
> (ex. domanin\firstname.lastname), the user is not found.
> Is there some setting that needs to be activated in SQL Server to see
> the CA name as well as the samaccount name, or is this an issue that
> must be resolved on the domain controller.
> I would appreciate any insight.
> Thanks,
> Matt|||OK, but for some people (like myself) my "long name", say
domain\firstname.lastname (what I believe is our CA name) shows up. My
short name domain\ablastname (what I believe is the samaccount name)
can also be chosen as a login. But for other people, the only thing
that I can access is the domain\cdlastname user name. The
domain\firstname.lastname does not show up when I do a search from SQL
Server.
If it works for me, I figure it should work for others. I just can't
figure out what I need to do so I can add others "long name" (CA).
Thanks again,
Matt
Adding a Windows Authentication Login to the server from third-party softwareHi,
I have an application in development into which I want to port some simple user management functionality. I have no problem getting lists of users/database roles/etc. and assigning users to roles as seeing fit, but one last function I cannot perform: replicating the "select windows user" dialog in the domain context of the server so that I have a user to add.
Is this possible with smo? or should I just give up on this angle and just attempt to use ActiveDirectory objects on the client machine and hope that the domain context is the same between the client and the sql server?Hi,
this one is not exposed from SMO, its done through the object picker wrapper:
http://dotnet.org.za/armand/articles/2453.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Adding a Windows Authentication Login to the server from third-party software
I have an application in development into which I want to port some simple user management functionality. I have no problem getting lists of users/database roles/etc. and assigning users to roles as seeing fit, but one last function I cannot perform: replicating the "select windows user" dialog in the domain context of the server so that I have a user to add.
Is this possible with smo? or should I just give up on this angle and just attempt to use ActiveDirectory objects on the client machine and hope that the domain context is the same between the client and the sql server?Hi,
this one is not exposed from SMO, its done through the object picker wrapper:
http://dotnet.org.za/armand/articles/2453.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)