Does securityadmin Server level role can add, alter logins and corresponding users on all databases ?
If not what is the best role other thn SA to have to manager logins and users.
hi,
In sql2k you used to be able to have a same user in 2 databases under the same login. Just moved to 2005, using sql authentication, and have some users who need to access 2 databases using the same login credentials. But sql2005 will not let me create 2 users under the same login across databases. Any ideas?
Thanks,
Mat
Hi,
This thread is a reformulation of a prior thread.
I created a login 'Network service' at server level in Management Studio express.I use windows authentification.Then i defined an user for my database which is associated to login 'Network service', because the application asp.net uses that account (IIS 6.0). This user received db_read and db_write roles.This works.Now i experimented a little bit and i removed from the logins at server level the login 'Network service'.Result: the application still works..Then i removed the Builtinusers login from the login list at server level.Result: i get the error: "login failed for Network service".I recreated then the login 'Network Service' at server level but not the Builtinusers login.Result: it works again.My conclusion is: one of the two logins must be in the list: Network Service or BuiltinusersIs this right?Why do i get that error when both logins are removed and not only when Network Service is removed?Thanks
How to find the list of logins/users who do not have any permission (except default Public) access.
View 4 Replies View RelatedHow to get all logins & users with their password into other server while doing migration?
View 7 Replies View RelatedOur company has 2 Database Roles (DBE and DBA). The DBE creates
database schema, performs SQL Server Administration, and manages server
security. The DBA writes data access, ETL, and manages database
security. In 2005, we're struggling with how to allow the DBA to see
all of the logins on the server in order to add them as users of their
database. What permissions does the DBA need to select from any of the
logins on the server to add them to their database?
Michelle
In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
View 2 Replies View Related<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">Friends,</g>
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">I</g>
i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g> store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.
<g class="gr_ gr_418 gr-alert gr_gramm Grammar multiReplace" data-gr-id="418" id="418">please</g>
best way to migrate logins only related to the specific database to the new server.
Hi All,
I have just migrated couple of test databases
from SQL Server 2000 to 2005 (side by side).
I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?
Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?
Thanks for your time..
Bidyut
Good Day,
I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150). I am connected to my database engine through the "sa" account.
Regards,
Keith
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
I wanted to create two users in SQL Server to be used by my application. The first would have read only access to all the tables in the database, the second would have read and write access.
I tried to use one login for both but when I tried to create the second user in my database I encountered the following error :
Error 15063 : The login already has an account under a different user name.
Do I need to create two separate logins in SQL Server to achieve this? If so, what if I want various users all with different access rights to different tables. Do I need a new login and a new user for each.
hi all ,
any decribtion about users and logins and differncs between them ??
and any example for real use in real life ??
i use logins user name and passwords in the connection string from c# but how users can make difference with that ??
thnks so much for your help
Hello,
How can I login in one databse from Sql Management Studio? Do I need one login for each user?
How does it work?
thanks,
I work for a central IT facility which provides services to all the departments in the organisation. Management are proposing to extend the current database hosting service to a full blown DBA service as well. The role is to be split between the hosting team, who had also been providing high level functions like replication, and a dba team who will take on the database administration. There is to be a strict delineation of responsibilities with the hosting team looking after everything at the instance level and the database team managing the databases. The hosting team will retain the sysadmin role and grant sql permissions to the database team as and when they need them.
View 2 Replies View Related
Hello,
We have 700+ databases on SQL server 2005, and we constantly adding more db.. I know, this is not the best... I have the same group of people who needs to have identical permissions on all existing and newly created databases... How can I acchieve that in efficient way? Ideally I would like to create new database role which has read and write access to all objects in all databases, and add users to this new role. Do those users need to be added to each database? How can I assure that new database will have role and users already added after the creation? Can I add it to the model and all other dbs will have it?
I want to programmatically, using C#, provide BuiltInPower Users permission to a database. That is, I want any user who is logged into the computer where they belong to the Windows Power Users group to be able to log into Sql Server and to a database. I am able to do this nicely in the English version of Windows XP and Sql; however, the same procedure, for example, in the German version fails due to a change in spelling of 'Power Users' in german. In C# one is able to use the enum WindowsBuiltInRole.PowerUser which helps application specific permission issues, but how does one do this when using international XP versions connecting to Sql server. That is, how does one establish BuiltInPower Users permissions for SQL when using international versions of the operating system. Or maybe I am approaching this the wrong way.
View 1 Replies View RelatedOk, let me preface my question with a little info about the application I am working on. I am in the middle of creating a project in VB.Net for my company. We have a data management system for handling sampling data. Now the database design is like so. The application is able to handle multiple "Sites". We create a new database for each site that is create dint eh application. These databases are identical in structure but the data is obviously different.
Now we don't actually delete any records in this application rather we mark items as deleted instead. This allows us to easily undo any changes that have been made to the data. When a change or delete are made, we record this change so that reports can be printed to show what changes were made and by who. This is all well and good, but my thoughts are this.
At the moment I am writing lots of VB code to handle adding these records of change and inserting them into the database... What I would like to be able to do is to just create Triggers on the tables to add them. This is something that I know how to do and i feel like it would be the better way to do it except for one thing... here comes the issue...
I have no way of knowing what user is logged in to my application from within the trigger because the application uses a single login for accessing the database. My thoughts are this... Would I be crazy to think that it would be a good idea to create SQL server logins for each user that is created in the application, giving these users access to only the database that they have been created in? This would allow me to determine who was logged in when the change was made and could then implement recording changes through triggers... I am not a very experienced dba programming is more my speciality. I know how to implement this idea, but I am just wondering if this sort of thing is considered bad practice or if it is something I should consider implementing...
Sorry for the novel there and thanks for any help or insight.
Brian
What is the relationship between the users in a database and the logins on the server.
View 1 Replies View RelatedHi
I can't figure out what the purpose of having seperate users is as I can't actually login to the database using one.
Here is my scenario.
I have a single login called LoginA and I have a database which I want to carve up using schema's. At the database level I need to create a user, associate a login with this user and can set a default schema and specifiy what schemas this USER can access. The login created can access multiple schemas.
So..
I created a database login called loginA.
I created a user for the database called UserA set it's login name to LoginA and
I then created 3 schemas called SchemaA, SchemaB and SchemaC and set their schema owner name to UserA.
I went back to UserA and set their default Schema to SchemaA
How can I login using the new user created as it has no password associated with it. If I login using LoginA then I have no default Schema set becuase the schema is associated with a USER not a LOGIN.
I can understand why you can only have one login account assicated with one user account for each database but I can;t understand why you can specify a user name if you can't use it to login.
Has anyone got any ideas?
Thanks
Hi ,
What is the diff between Logins & Users in SQL Server?
thanks
Babu
Hi folks, i create a procedure in master db that kill users spids. Giving EXECUTE permission on the procedure to users without giving processadmin role doesn't work! Any guidelines?
Howdy!
I have a need to add all the users listed in the sysxlogins table to the db_datawriter role. I wrote a proc that does this. It indicates that each user is successfully added to the role, but they aren't. If I look at the user in EM, they don't have that role checked. I've tried all the obvious stuff like close EM and re-open, etc...it doesn't help.
Here is the proc
CREATE proc sp_MyProc
@DBName varchar(256)
As
Declare UID_Cursor Cursor
For
Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name
Open UID_Cursor
Declare @Name as varchar(256)
Declare @TempString as varchar(8000)
Fetch Next from UID_Cursor into @Name
While (@@Fetch_Status <> -1)
Begin
Set @TempString = 'sp_addrolemember ''db_datareader'', ''' + @Name + ''''
Exec(@TempString)
Fetch Next from UID_Cursor Into @Name
End
Close UID_Cursor
Deallocate UID_Cursor
ere is the make-up of the tables:
[dbo].[PortalUser](
[PortalUserID] [bigint] IDENTITY(1,1) NOT NULL,
...
[dbo].[Role](
[RoleID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_Roles_IsActive] DEFAULT ((1))
...
[dbo].[PortalUserRole](
[PortalUserRoleID] [bigint] IDENTITY(1,1) NOT NULL,
[PortalUserID] [bigint] NOT NULL,
[RoleID] [bigint] NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_PortalUserRole_IsActive] DEFAULT ((1))
I'm asking to get a list of portalUsers that do not have a PortalUserRole records for the rolename I'm checking against. And don't ask me why the person who coded this is searching on rolename not ID. But this is how we're doing it for now.
I have user which is a member of the db_securityadmin database role. It has no other permissions applied to it. When I login with this user, and try to add some other database users to the db_securityadmin role, I receive an error stating that I don't have the permissions to do this:
User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
Is this the normal behaviour, or is there something wrong ? I am using SQL Server 2005. From what I know, this works fine with SQL Server 2000, but it doesn't work as I expected with SQL Server 2005.
I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??
View 1 Replies View RelatedHI guys,
I just restored a database on a new server with a backup(complete backup stored in backup device) from another database on another server using the "with move" option. In fact here was my process:
Alter database ngauge SET SINGLE_USER WITH ROLLBACK IMMEDIATE
restore database ngauge
from disk = 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUPgauge1.BAK'
with move 'NGAUGE' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Data.MDF',
move 'NGAUGE_Log' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Log.LDF'
Alter database ngauge SET READ_WRITE
it worked.
But it did not move the 98 or so users/logins...from the source database
what is going on??? what am I doing wrong??!!
Please help!
'W
Hi all
I'm using sql enterprise manager to access a database which has two users associated to it called 'dbo' and 'user'. I have a login that uses sql authentication also called 'user2' which has been tested and working fine.
From within enterprise manager, when i look at the list of users for my database, 'dbo' is listed as using the 'user2' login to gain access, but the entry for 'user' has no login name listed and is blank, which should be 'user2'. How can I rectify this?
Thanks
I do not understand the sequence in which to add logins/users. A coupleof things to note. I can not use vbscript - it has to be done using thesqldmo objects (or in a sql script).The sequence I use now is:1- create login, set default db2- add login to sql server3- set db owner4- create user5- add user to master db6- add user to tempdb - blows up hereError 'Microsoft SQL-DMO: [SQL-DMO]This object is already in acollection.'I need the user in 3 dbs. Any help greatly appreciated. Thinking aboutit...I bet by adding it to the master db that it is automatically addedto tempdb so I will try to add it to the 2rd db and see if that works.Thanks!-Will
View 2 Replies View RelatedHello all,I am looking for the script, which I believe exists already.I need tobe able to populate the script for security of one database andapply it to another database, even if it is located on another server:1. All logins which not exist have to be created and which existsignored including the NT accounts2. Users same as the old database + the existing ones stay in database3. Passwords for the new logins.4. All permissions/grants on all objects for the users that exists(usually it's the case) and ignore those that don't.I have script which does some of it, but it's not perfect, so everytime there are some errors.Please let me know, if you need me to email script that I have. It'spretty long so I cannot just post it in here.Thank you in advance.
View 3 Replies View RelatedI have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.
How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .
declare @is_read_only nvarchar (200)
Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */
IF @IS_READ_ONLY= 0
BEGIN
Declare @SQL as varchar (200)
[Code] .....
After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.
In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.
In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.
Any advice on how to duplicate the behavior from SQL2000?
Is
there a way to find a list of Windows User accounts that are directly
or indirectly (through Windows Group membership) assigned to a database
role?
I could put work in to CLR programming or using a Linked Server to
Active Directory, but if there is a sys.* view available that can
provide me this
information directly it would be much easier.
I'll be looking into this further myself anyway and posting an answer
if I can find one, but if anyone has suggestions... well, thanks!